-- 定义序列表
drop table if exists sequence;
create table sequence (
name varchar(50) not null,
current_value int not null,
increment int not null default 1,
primary key (name)
) engine=innodb;
-- 获取当前序列号
drop function if exists currval;
delimiter $
create function currval (seq_name varchar(50))
returns integer
contains sql
begin
declare value integer;
set value = 0;
select current_value into value
from sequence
where name = seq_name;
return value;
end$
delimiter ;
-- 获取下一个序列号
drop function if exists nextval;
delimiter $
create function nextval (seq_name varchar(50))
returns integer
contains sql
begin
update sequence set current_value = current_value + increment
where name = seq_name;
return currval(seq_name);
end$
delimiter ;
-- 重设序列号
drop function if exists setval;
delimiter $
create function setval (seq_name varchar(50), value integer)
returns integer
contains sql
begin
update sequence set current_value = value
where name = seq_name;
return currval(seq_name);
end$
delimiter ;
-- 初始化数据
insert into sequence values ('sample', 1, 1);
-- 测试
select currval('sample');
select nextval('sample');
select nextval('sample');
select setval('sample',150);
select currval('sample');
select nextval('sample');
select nextval('sample');