MySQL序列解决方案
程序员文章站
2022-05-10 13:04:01
...
-- 定义序列表 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 CONTAINS SQL 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 CONTAINS SQL 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 CONTAINS SQL BEGIN UPDATE sequence SET current_value = value WHERE name = seq_name; RETURN currval(seq_name); END$ DELIMITER ; -- 初始化数据 INSERT INTO sequence VALUES ('SAMPLE', 1, 1); -- 测试 SELECT currval('SAMPLE'); SELECT nextval('SAMPLE'); SELECT nextval('SAMPLE'); SELECT setval('SAMPLE',150); SELECT currval('SAMPLE'); SELECT nextval('SAMPLE'); SELECT nextval('SAMPLE');