mysq创建序列

1.创建自增序列表

#自定义 自增序列表 sequence

CREATE TABLE `sequence` (
	`name` varchar (150) comment '序列名称',
	`current_value` int (11) comment '当前值/初始值',
	`increment` int (11)  comment '步增值'
); 

2.插入一条自定义记录

my_sequence 从1开始每调用一次步增1

INSERT INTOsequence (name, current_value, increment)
VALUES (‘my_sequence’,‘1’,‘1’);

3.获取下一个函数值

drop function if exists nextval;
create function nextval(seq_name VARCHAR(50)) RETURNS INT
begin \\
  declare vvv integer;
  set vvv = 0;
  select current_value into vvv from sequence where name = seq_name for update;
  update sequence set current_value = current_value + increment where name = seq_name;
  return currval(seq_name);
end \\
delimiter ;

4.新增

drop function if exists setcval;
delimiter \\
create function setcval(seq_name VARCHAR(50),seq_curr int) RETURNS INT
begin
update sequence set current_value = seq_curr where name = seq_name;
return currval(seq_name);
end \\
delimiter ;

5.查看

drop function if exists currval;
delimiter \\
create function currval(seq_name VARCHAR(50))
returns integer
begin
  declare vvv integer;
  set vvv = 0;
  select current_value into vvv from sequence where name = seq_name;
  return vvv;
end \\
delimiter ;

6.使用nextVal(‘my_sequence’)

INSERT INTO TABLE (ID,NAME)
            VALUE (nextVal('my_sequence'),"xiaowang");