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);