MySQL 之自己实现sequence

Oracle的Sequence是一个很好的主键生成策略，但是MySQL并不具有sequence语法，下面在MySQL中来模拟实现一个sequence。

1、建一张表e_sys_sequence用来记录序列名称和值

DROP TABLE IF EXISTS e_sys_sequence;
CREATE TABLE
e_sys_sequence
(
    name VARCHAR(64) NOT NULL,
    current_value BIGINT NOT NULL,
    increment INT NOT NULL DEFAULT 1,
    PRIMARY KEY (name)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='通用序列表';

2、创建取当前值的函数e_sys_sequence.currval

DROP FUNCTION IF EXISTS `e_sys_sequence.currval`; 
DELIMITER $ 
CREATE FUNCTION `e_sys_sequence.currval` (seq_name VARCHAR(50)) RETURNS INTEGER
BEGIN
 DECLARE value INTEGER; 
 SET value = 0; 
 SELECT current_value INTO value FROM e_sys_sequence WHERE name = seq_name; 
RETURN value; 
END
$ 
DELIMITER ;

3、创建取下一个值的函数e_sys_sequence.nextval

DROP FUNCTION IF EXISTS `e_sys_sequence.nextval`;  
DELIMITER $$  
CREATE FUNCTION `e_sys_sequence.nextval`(seq_name char (64)) returns BIGINT
BEGIN
 UPDATE e_sys_sequence SET current_value=last_insert_id(current_value+increment) WHERE name=seq_name;
 RETURN last_insert_id();
END $$
DELIMITER;

4、创建更新当前值的函数 e_sys_sequence.setval

DROP FUNCTION IF EXISTS `e_sys_sequence.setval`;  
DELIMITER $ 
CREATE FUNCTION `e_sys_sequence.setval`(seq_name VARCHAR(50), value INTEGER) RETURNS INTEGER
BEGIN
 UPDATE e_sys_sequence SET current_value = value WHERE name = seq_name; 
 RETURN `e_sys_sequence.currval`(seq_name); 
END
$ 
DELIMITER ;

测试：

--添加一个sequence名称和初始值，以及自增幅度  添加一个名为TestSeq的自增序列
INSERT INTO e_sys_sequence VALUES ('TestSeq', 0, 1);
--查询指定sequence的下一个值，这里是获取TestSeq下一个值
SELECT `e_sys_sequence.nextval`('TestSeq');
--查询指定sequence的当前值，这里是获取TestSeq当前值
SELECT `e_sys_sequence.currval`('TestSeq');
---更新指定sequence的初始值，这里更新TestSeq的值为10
SELECT `e_sys_sequence.setval`('TestSeq', 10);