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

作者:xm-koma 时间:2024-01-25 02:42:13 

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


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;

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


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 ;

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


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 ;

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


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语句查询下一个值,这样就解决了流水号唯一的问题。
贴出部分代码(已测试通过)


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 中实现序列,再来看下面一个例子:


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表插入值即可:


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

查看当前已建序列:


mysql> select * from sequence;

+-------+---------------+-----------+
| name | current_value | increment |
+-------+---------------+-----------+
| myseq |       0 |     1 |
+-------+---------------+-----------+
1 row in set (0.00 sec)

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


mysql> select nextval('myseq');

+------------------+
| nextval('myseq') |
+------------------+
|        1 |
+------------------+
1 row in set (0.00 sec)

标签:MySQL,自增,序列
0
投稿

猜你喜欢

  • python绘制趋势图的示例

    2022-02-28 07:19:18
  • PDO::getAvailableDrivers讲解

    2023-06-08 22:24:20
  • vue嵌套组件传参实例分享

    2024-06-07 15:23:11
  • python列表的切片与复制示例分析

    2023-07-15 02:12:34
  • Python解决走迷宫问题算法示例

    2023-04-18 02:14:45
  • 彻底弄懂CSS盒子模式之五(定位强化练习)

    2007-05-11 16:51:00
  • oracle dba 应该熟悉的命令

    2009-07-02 12:08:00
  • 我的ImageMagick使用心得

    2008-10-21 11:05:00
  • Python多线程中阻塞(join)与锁(Lock)使用误区解析

    2022-03-22 08:00:31
  • Python tkinter 列表框Listbox属性详情

    2023-09-16 14:06:26
  • java 使用poi 导入Excel数据到数据库的步骤

    2024-01-19 11:21:34
  • 构建Python包的五个简单准则简介

    2023-05-06 11:38:10
  • JavaScript缓动库

    2009-05-25 12:50:00
  • python实现图片批量压缩程序

    2021-11-27 03:50:11
  • python使用Matplotlib改变坐标轴的默认位置

    2021-08-06 00:27:42
  • 基于layPage插件实现两种分页方式浅析

    2024-05-28 15:40:53
  • Python找出最小的K个数实例代码

    2022-09-13 12:21:10
  • 15个设计得最糟糕最变态的CAPTCHA验证码

    2008-09-01 17:17:00
  • python模块smtplib实现纯文本邮件发送功能

    2022-08-14 21:01:32
  • Python 随机生成中文验证码的实例代码

    2022-12-15 23:17:34
  • asp之家 网络编程 m.aspxhome.com