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
language sql
deterministic
contains sql
sql security definer
comment ''
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
language sql
deterministic
contains sql
sql security definer
comment ''
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
language sql
deterministic
contains sql
sql security definer
comment ''
begin
update sequence
set current_value = value
where name = seq_name;
return currval(seq_name);
end
$
delimiter ;
/*
-- 测试
insert into sequence values ('testseq', 0, 1);
select setval('testseq', 10);
select currval('testseq');
select nextval('testseq');
*/
作者 json的博客 bitscn.com