参考:https://blog.csdn.net/weixin_42383575/article/details/103023379
新建序列
### 新建序列表 drop table if exists sequence; create table sequence( seq_name VARCHAR(50) NOT NULL COMMENT '序列名称', current_val INT NOT NULL COMMENT '当前值', increment_val INT NOT NULL DEFAULT 1 COMMENT '跨度', PRIMARY KEY(seq_name) ); ### 新增一个序列 INSERT INTO sequence VALUES('seq_test_1', 0, 1); INSERT INTO sequence VALUES('seq_test_2', 0, 2); ### 创建函数用于获取当前序列值 CREATE FUNCTION currval(v_seq_name VARCHAR(50)) RETURNS INTEGER BEGIN DECLARE val INTEGER; set val = 0; SELECT current_val into val from sequence where seq_name=v_seq_name; return val; END; ### 查询当前的值 SELECT currval('seq_test_1'); ### 创建函数用户获取下一个值 CREATE FUNCTION nextval(v_seq_name VARCHAR(50)) RETURNS INTEGER BEGIN UPDATE sequence SET current_val = current_val + increment_val where seq_name=v_seq_name; return currval(v_seq_name); END; ### 查询下一个值 SELECT nextval('seq_test_1');
创建表测试
### 新建测试表 DROP TABLE IF EXISTS `sequence_test`; CREATE TABLE `sequence_test` ( `name` varchar(255) NOT NULL, `value` double(255,2) DEFAULT NULL, `num1` int(11) DEFAULT NULL, `num2` int(11) DEFAULT NULL, PRIMARY KEY (`name`) ); ### 新建触发器 插入记录前给自增字段赋值 DROP TRIGGER IF EXISTS `TRI_TEST_SEQ`; CREATE TRIGGER `TRI_TEST_SEQ` BEFORE INSERT ON `sequence_test` FOR EACH ROW BEGIN SET NEW.num1 = nextval('seq_test_1'); SET NEW.num2 = nextval('seq_test_2'); END; ### 测试效果 INSERT INTO sequence_test(name, value) VALUES('1', 111); INSERT INTO sequence_test(name, value) VALUES('2', 111); INSERT INTO sequence_test(name, value) VALUES('3', 111); INSERT INTO sequence_test(name, value) VALUES('4', 111); ### 结果展示 SELECT * FROM sequence_test;