背景
因为做oracle迁移mysql的工作,mysql并不具有sequence语法,所以需要自己想办法模拟实现一个sequence。
方案1
- 建一张表e_sys_sequence用来记录序列名称和值
CREATE TABLE `e_sys_sequence` (
`sequence_name` varchar(64) NOT NULL COMMENT '序列名称' ,
`value` bigint NOT NULL DEFAULT 0 COMMENT '当前值' ,
PRIMARY KEY (`sequence_name`)
)ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '通用序列表' ROW_FORMAT = Dynamic;
- 创建一个mysql函数,对序列做增加操作
DELIMITER $$
CREATE FUNCTION `e_sys_sequence.nextval`(name varchar(50))
RETURNS BIGINT
BEGIN
UPDATE e_sys_sequence t, (SELECT @current_val:=`value` FROM e_sys_sequence t2 WHERE t2.sequence_name=sequence_name) t3 SET t.value = t.value + 1 WHERE t.sequence_name =sequence_name AND @current_val=t