实现mysql的sequence

背景

因为做oracle迁移mysql的工作，mysql并不具有sequence语法，所以需要自己想办法模拟实现一个sequence。

方案1

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;

2. 创建一个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