mysql で sequence

テーブル

create table sequence ( id varchar(20) primary key, seq int);

関数

delimiter ;;

-- 次のシーケンス
create function next_val (aid varchar(20)) returns integer
begin
    insert into sequence values (aid, 1) on duplicate key update seq=seq+1;
    return (select seq from sequence where id = aid);
end;;

-- シーケンス設定
create function set_val (aid varchar(20), aseq int) returns integer
begin
    insert into sequence values (aid, aseq) on duplicate key update seq=aseq;
    return (select seq from sequence where id = aid);
end;;

-- 現在のシーケンス
create function current_val (aid varchar(20)) returns integer
begin
    return (select seq from sequence where id = aid union all select 0 limit 1);
end;;

delimiter ;

実行確認

select next_val('test');
select next_val('test1');
select next_val('test');
select next_val('test1');
select next_val('test');
select next_val('test2');
select set_val('test2', 5);
select set_val('test3', 3);

select * from sequence;

実行確認結果

id	seq
test	3
test1	2
test2	5
test3	3

番外/削除

drop table if exists sequence;
drop function if exists next_val;
drop function if exists current_val ;
drop function if exists set_val ;