在MySQL中创建实现自增的序列(Sequence)的教程

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: 在MySQL中创建实现自增的序列(Sequence)的教程 这篇文章主要介绍了在MySQL中创建实现自增的序列(Sequence)的教程,分别列举了两个实例并简单讨论了一些限制因素,需要的朋友可以参考下 项目应用中,曾有以下一个场景:接口中要求发送一个int类型的流水号,由于多线程模式,如果用时间戳,可能会有重复的情况(当然概率很小)。

在MySQL中创建实现自增的序列(Sequence)的教程

 

这篇文章主要介绍了在MySQL中创建实现自增的序列(Sequence)的教程,分别列举了两个实例并简单讨论了一些限制因素,需要的朋友可以参考下
 

项目应用中,曾有以下一个场景:
接口中要求发送一个int类型的流水号,由于多线程模式,如果用时间戳,可能会有重复的情况(当然概率很小)。
所以想到了利用一个独立的自增的sequence来解决该问题。
当前数据库为:mysql
由于mysql和oracle不太一样,不支持直接的sequence,所以需要创建一张table来模拟sequence的功能,理由sql语句如下:
第一步:创建--Sequence 管理表

?
1
2
3
4
5
6
7
DROP TABLE IF EXISTS sequence ;
CREATE TABLE sequence (
      name VARCHAR (50) NOT NULL ,
      current_value INT NOT NULL ,
      increment INT NOT NULL DEFAULT 1,
      PRIMARY KEY ( name )
) ENGINE=InnoDB;

 
第二步:创建--取当前值的函数

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
DROP FUNCTION IF EXISTS currval;
DELIMITER $
CREATE FUNCTION currval (seq_name VARCHAR (50))
      RETURNS INTEGER
      LANGUAGE SQL
      DETERMINISTIC
      CONTAINS SQL
      SQL SECURITY DEFINER
      COMMENT ''
BEGIN
      DECLARE value INTEGER ;
      SET value = 0;
      SELECT current_value INTO value
           FROM sequence
           WHERE name = seq_name;
      RETURN value;
END
$
DELIMITER ;

 
第三步:创建--取下一个值的函数

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
DROP FUNCTION IF EXISTS nextval;
DELIMITER $
CREATE FUNCTION nextval (seq_name VARCHAR (50))
      RETURNS INTEGER
      LANGUAGE SQL
      DETERMINISTIC
      CONTAINS SQL
      SQL SECURITY DEFINER
      COMMENT ''
BEGIN
      UPDATE sequence
           SET current_value = current_value + increment
           WHERE name = seq_name;
      RETURN currval(seq_name);
END
$
DELIMITER ;

 
第四步:创建--更新当前值的函数

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
DROP FUNCTION IF EXISTS setval;
DELIMITER $
CREATE FUNCTION setval (seq_name VARCHAR (50), value INTEGER )
      RETURNS INTEGER
      LANGUAGE SQL
      DETERMINISTIC
      CONTAINS SQL
      SQL SECURITY DEFINER
      COMMENT ''
BEGIN
      UPDATE sequence
           SET current_value = value
           WHERE name = seq_name;
      RETURN currval(seq_name);
END
$
DELIMITER ;

 
第五步:测试函数功能
当上述四步完成后,可以用以下数据设置需要创建的sequence名称以及设置初始值和获取当前值和下一个值。
 

  • INSERT INTO sequence VALUES ('TestSeq', 0, 1);----添加一个sequence名称和初始值,以及自增幅度
  • SELECT SETVAL('TestSeq', 10);---设置指定sequence的初始值
  • SELECT CURRVAL('TestSeq');--查询指定sequence的当前值
  • SELECT NEXTVAL('TestSeq');--查询指定sequence的下一个值

 

 
在java代码中,可直接创建sql语句查询下一个值,这样就解决了流水号唯一的问题。
贴出部分代码(已测试通过)

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
public void testGetSequence() {
   Connection conn = JDBCUtils.getConnection(url, userName, password);
   String sql = "SELECT CURRVAL('TestSeq');" ;
   PreparedStatement ptmt = null ;
   ResultSet rs = null ;
   try {
     ptmt = conn.prepareStatement(sql);
     rs = ptmt.executeQuery();
     int count = 0 ;
     while (rs.next()) {
       count = rs.getInt( 1 );
     }
     System.out.println(count);
   } catch (SQLException e) {
     e.printStackTrace();
   } finally {
     JDBCUtils.close(rs, ptmt, conn);
   }
}

 
 
ps:在应用中,还有一种用java代码去实现模拟自增sequence的方式,具体思路是创建一张存放sequence的table,然后通过java调用sql语句去查询和修改这个table中指定sequence名称的值,这种方式请加上synchronized。具体代码这里就不上传了,因为实现了,未去测试过。

在 oracle 中, sequence 提供多表多字段可共用一个不重复值。 Mysql 中存在自增列,基本可以满足 PK 的要求。但自增列存在限制:

a. 只能用于表中的一个字段,一张不能同时存在两个以上的自增列 ;

b. 自增列必须被定义为 key ( PK 或 FK ) ;

c. 自增列不能被多个表共用 ;

d. 当 insert 语句不包括自增字段或将其值设置为 NULL 时,该值会自动填上。

在不要求字段顺序递增的情况下,可以在 Mysql 中实现序列,再来看下面一个例子:

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
DROP TABLE IF EXISTS sequence ;
  
-- 建sequence表,指定seq列为无符号大整型,可支持无符号值:0(default)到18446744073709551615(0到2^64–1)。
CREATE TABLE sequence (
    name       VARCHAR (50) NOT NULL ,
      current_value   BIGINT UNSIGNED NOT NULL DEFAULT 0,
      increment     INT NOT NULL DEFAULT 1,
      PRIMARY KEY ( name -- 不允许重复seq的存在。
) ENGINE=InnoDB;
  
  
DELIMITER /
  
DROP FUNCTION IF EXISTS currval /
  
CREATE FUNCTION currval(seq_name VARCHAR (50))
RETURNS BIGINT
BEGIN
      DECLARE value BIGINT ;
      SELECT current_value INTO value
      FROM sequence
      WHERE upper ( name ) = upper (seq_name); -- 大小写不区分.
      RETURN value;
END ;
/
  
DELIMITER ;
  
  
DELIMITER /
  
DROP FUNCTION IF EXISTS nextval /
  
CREATE FUNCTION nextval (seq_name VARCHAR (50))
RETURNS BIGINT
BEGIN
      DECLARE value BIGINT ;
      UPDATE sequence
      SET current_value = current_value + increment
      WHERE upper ( name ) = upper (seq_name);
      RETURN currval(seq_name);
END ;
/
  
DELIMITER ;
  
DELIMITER /
  
DROP FUNCTION IF EXISTS setval /
  
CREATE FUNCTION setval (seq_name VARCHAR (50), value BIGINT )
RETURNS BIGINT
BEGIN
      UPDATE sequence
      SET current_value = value
      WHERE upper ( name ) = upper (seq_name);
      RETURN currval(seq_name);
END ;
/
  
DELIMITER ;

 在 SQL 中使用序列:
创建序列,往sequence表插入值即可:

?
1
mysql> insert into sequence set name = 'myseq' ;

查看当前已建序列:

?
1
mysql> select * from sequence ;
?
1
2
3
4
5
6
+-------+---------------+-----------+
| name | current_value | increment |
+-------+---------------+-----------+
| myseq |       0 |     1 |
+-------+---------------+-----------+
1 row in set (0.00 sec)

获得序列的下一个值,第一次使用,因此值为1:

?
1
mysql> select nextval( 'myseq' );
?
1
2
3
4
5
6
+------------------+
| nextval('myseq') |
+------------------+
|        1 |
+------------------+
1 row in set (0.00 sec)
相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
2月前
|
NoSQL 关系型数据库 MySQL
2024Mysql And Redis基础与进阶操作系列(4-2)作者——LJS[含MySQL非空、唯一性、PRIMARY KEY、自增列/自增约束举例说明等详解步骤及常见报错问题对应的解决方法]
24MySQL非空、唯一性、PRIMARY KEY、自增列/自增约束举例说明等详解步骤及常见报错问题对应的解决方法(4-2) 学不会你来砍我!!!
|
1月前
|
NoSQL Java 关系型数据库
Liunx部署java项目Tomcat、Redis、Mysql教程
本文详细介绍了如何在 Linux 服务器上安装和配置 Tomcat、MySQL 和 Redis,并部署 Java 项目。通过这些步骤,您可以搭建一个高效稳定的 Java 应用运行环境。希望本文能为您在实际操作中提供有价值的参考。
136 26
|
5月前
|
关系型数据库 MySQL Java
centos7安装mysql教程及Navicat平替软件
【8月更文挑战第17天】本教程详述CentOS 7上安装MySQL的过程。首先确保移除任何预装的MySQL组件,然后通过wget获取并安装MySQL的YUM源。可以选择安装特定版本如5.7或8.0。安装MySQL服务器后,启动服务并查找初始密码。登录MySQL后应立即更改密码,并可根据需要设置远程访问权限。此外,还推荐使用免费开源的DBeaver作为数据库管理工具,提供了安装步骤以方便管理和操作MySQL数据库。
186 3
|
18天前
|
存储 关系型数据库 MySQL
MySQL主键谁与争锋:MySQL为何钟爱自增主键ID+UUID?
本文深入探讨了在MySQL中使用自增类型主键的优势与局限性。自增主键通过保证数据的有序性和减少索引维护成本,提升了查询和插入性能,简化了数据库管理和维护,并提高了数据一致性。然而,在某些业务场景下,如跨表唯一性需求或分布式系统中,自增主键可能无法满足要求,且存在主键值易预测的安全风险。因此,选择主键类型时需综合考虑业务需求和应用场景。
24 2
|
2月前
|
关系型数据库 MySQL Java
Servlet+MySQL增删改查 原文出自[易百教程] 转载请保留原文链接: https://www.yiibai.com/geek/1391
对于任何项目开发,创建,读取,更新和删除(CRUD)记录操作是应用程序的一个最重要部分。
74 20
|
5月前
|
关系型数据库 MySQL
MySQL自增ID用完会怎样?
MySQL自增ID用完会怎样?
|
2月前
|
监控 关系型数据库 MySQL
MySQL自增ID耗尽应对策略:技术解决方案全解析
在数据库管理中,MySQL的自增ID(AUTO_INCREMENT)属性为表中的每一行提供了一个唯一的标识符。然而,当自增ID达到其最大值时,如何处理这一情况成为了数据库管理员和开发者必须面对的问题。本文将探讨MySQL自增ID耗尽的原因、影响以及有效的应对策略。
169 3
|
2月前
|
存储 关系型数据库 MySQL
MySQL 8.0特性-自增变量的持久化
【11月更文挑战第8天】在 MySQL 8.0 之前,自增变量(`AUTO_INCREMENT`)的行为在服务器重启后可能会发生变化,导致意外结果。MySQL 8.0 引入了自增变量的持久化特性,将其信息存储在数据字典中,确保重启后的一致性。这提高了开发和管理的稳定性,减少了主键冲突和数据不一致的风险。默认情况下,MySQL 8.0 启用了这一特性,但在升级时需注意行为变化。
|
2月前
|
存储 监控 关系型数据库
MySQL自增ID耗尽解决方案:应对策略与实践技巧
在MySQL数据库中,自增ID(AUTO_INCREMENT)是一种特殊的属性,用于自动为新插入的行生成唯一的标识符。然而,当自增ID达到其最大值时,会发生什么?又该如何解决?本文将探讨MySQL自增ID耗尽的问题,并提供一些实用的解决方案。
64 1
|
3月前
|
tengine 关系型数据库 MySQL
Tengine、Nginx安装MySQL数据库命令教程
本指南详细介绍了在Linux系统上安装与配置MySQL数据库的步骤。首先通过下载并安装MySQL社区版本,接着启动MySQL服务,使用`systemctl start mysqld.service`命令。若启动失败,可尝试使用`sudo /etc/init.d/mysqld start`。利用`systemctl status mysqld.service`检查MySQL的服务状态,确保其处于运行中。通过日志文件获取初始密码,使用该密码登录数据库,并按要求更改初始密码以增强安全性。随后创建一个名为`tengine`的数据库,最后验证数据库创建是否成功以及完成整个设置流程。