Mysql序列自增

1. 新建序列表

drop table if exists sequence;
create table sequence(
seq_name varchar(50) not null,
current_val int not null,
increment_val int not null,
primary key (seq_name));

2. 新增序列

insert into sequence value(‘seq_num1’,‘1’,‘1’);

3. 创建函数,获取序列当前值

create function currval(v_seq_name VARCHAR(50))
returns integer
begin
declare value integer;
set value = 0;
select current_val into value from sequence where seq_name = v_seq_name;
return value;
end;

4. 创建函数，获取下一个序列值

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;

5.应用时查询语句

select currval(‘seq_test1_num1’); —查询序列当前值

select nextval(‘seq_test1_num1’); —查询序列下一个值