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

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,高可用系列 2核4GB
云数据库 RDS PostgreSQL,高可用系列 2核4GB
简介: 在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)
相关实践学习
每个IT人都想学的“Web应用上云经典架构”实战
本实验从Web应用上云这个最基本的、最普遍的需求出发,帮助IT从业者们通过“阿里云Web应用上云解决方案”,了解一个企业级Web应用上云的常见架构,了解如何构建一个高可用、可扩展的企业级应用架构。
MySQL数据库入门学习
本课程通过最流行的开源数据库MySQL带你了解数据库的世界。   相关的阿里云产品:云数据库RDS MySQL 版 阿里云关系型数据库RDS(Relational Database Service)是一种稳定可靠、可弹性伸缩的在线数据库服务,提供容灾、备份、恢复、迁移等方面的全套解决方案,彻底解决数据库运维的烦恼。 了解产品详情: https://www.aliyun.com/product/rds/mysql 
目录
相关文章
|
12天前
|
安全 关系型数据库 MySQL
CentOS 7 yum 安装 MySQL教程
在CentOS 7上安装MySQL 8,其实流程很清晰。首先通过官方Yum仓库来安装服务,然后启动并设为开机自启。最重要的环节是首次安全设置:需要先从日志里找到临时密码来登录,再修改成你自己的密码,并为远程连接创建用户和授权。最后,也别忘了在服务器防火墙上放行3306端口,这样远程才能连上。
209 16
|
5月前
|
存储 关系型数据库 MySQL
【免费动手教程上线】阿里云RDS MySQL推出大容量高性能存储:高性能本地盘(最高16TB存储空间)、高性能云盘(最高64TB存储空间)
阿里云RDS MySQL提供高性能本地盘与高性能云盘等存储方案,满足用户大容量、低延迟需求。高性能本地盘单盘最大16TB,IO延时微秒级;高性能云盘兼容ESSD特性,支持IO性能突发、BPE及16K原子写等能力。此外,阿里云还提供免费动手体验教程,帮助用户直观感受云数据库 RDS 存储性能表现。
|
11月前
|
NoSQL 关系型数据库 MySQL
2024Mysql And Redis基础与进阶操作系列(4-2)作者——LJS[含MySQL非空、唯一性、PRIMARY KEY、自增列/自增约束举例说明等详解步骤及常见报错问题对应的解决方法]
24MySQL非空、唯一性、PRIMARY KEY、自增列/自增约束举例说明等详解步骤及常见报错问题对应的解决方法(4-2) 学不会你来砍我!!!
|
7月前
|
人工智能 关系型数据库 MySQL
解决MySQL自增id用尽的问题
本文介绍了解决文章点击记录表(`article_click_record`)数据量激增问题的方案。由于用户量大,每天新增约400万条记录,导致表id接近溢出(2,100,000,000),且占用空间超320G。解决方案包括:1) 新建`article_click_record_new`表,将id类型改为BIGINT以避免溢出;2) 过渡阶段同时写入新旧表,待旧表id溢出后切换至新表;3) 定时清理过期数据或转移旧表内容。实现方式涉及修改相关接口和服务逻辑,确保业务平稳过渡。
164 5
|
关系型数据库 MySQL Java
centos7安装mysql教程及Navicat平替软件
【8月更文挑战第17天】本教程详述CentOS 7上安装MySQL的过程。首先确保移除任何预装的MySQL组件,然后通过wget获取并安装MySQL的YUM源。可以选择安装特定版本如5.7或8.0。安装MySQL服务器后,启动服务并查找初始密码。登录MySQL后应立即更改密码,并可根据需要设置远程访问权限。此外,还推荐使用免费开源的DBeaver作为数据库管理工具,提供了安装步骤以方便管理和操作MySQL数据库。
397 3
|
关系型数据库 MySQL
MySQL自增ID用完会怎样?
MySQL自增ID用完会怎样?
|
10月前
|
NoSQL Java 关系型数据库
Liunx部署java项目Tomcat、Redis、Mysql教程
本文详细介绍了如何在 Linux 服务器上安装和配置 Tomcat、MySQL 和 Redis,并部署 Java 项目。通过这些步骤,您可以搭建一个高效稳定的 Java 应用运行环境。希望本文能为您在实际操作中提供有价值的参考。
600 26
|
11月前
|
关系型数据库 MySQL Java
Servlet+MySQL增删改查 原文出自[易百教程] 转载请保留原文链接: https://www.yiibai.com/geek/1391
对于任何项目开发,创建,读取,更新和删除(CRUD)记录操作是应用程序的一个最重要部分。
228 20
|
10月前
|
存储 关系型数据库 MySQL
MySQL主键谁与争锋:MySQL为何钟爱自增主键ID+UUID?
本文深入探讨了在MySQL中使用自增类型主键的优势与局限性。自增主键通过保证数据的有序性和减少索引维护成本,提升了查询和插入性能,简化了数据库管理和维护,并提高了数据一致性。然而,在某些业务场景下,如跨表唯一性需求或分布式系统中,自增主键可能无法满足要求,且存在主键值易预测的安全风险。因此,选择主键类型时需综合考虑业务需求和应用场景。
322 2

热门文章

最新文章

推荐镜像

更多