mysql5.7.22 自建序列功能

DROP TABLE IF EXISTS owt_sequence;

CREATE TABLE `owt_sequence` (
`seq_name`  varchar(200) NOT NULL ,
`current_val`  int NOT NULL  DEFAULT 0 ,
`increment_val`  int NOT NULL DEFAULT 1 ,
   PRIMARY KEY (`seq_name`)
)
;

DROP FUNCTION IF EXISTS owt_currval;
delimiter $$
CREATE function owt_currval(v_seq_name varchar(20)) returns INT
BEGIN
     declare seq_no INT;
     set seq_no = 0;
     insert INTO owt_sequence(seq_name) value(v_seq_name) on DUPLICATE key update seq_name = v_seq_name;
     select current_val into seq_no from owt_sequence where seq_name = v_seq_name LIMIT 1;   
     return seq_no;
END
$$
delimiter ;

DROP FUNCTION IF EXISTS owt_nextval;
delimiter $$
CREATE function owt_nextval(v_seq_name varchar(20)) returns INT
BEGIN
     update owt_sequence set current_val = current_val + increment_val where seq_name = v_seq_name;
     return owt_currval(v_seq_name);
END
$$
delimiter ;

使用方法   select owt_nextval('aaa');

转载于:https://www.cnblogs.com/fb010001/p/9105113.html