I had to make 10 sequences in our Koha installation which uses MySQL. To my horror MySQL doesn't have CREATE SEQUENCE (insert MySQL vs PostgreSQL rant here). Even worse, MySQL manual suggest usage of LAST_INSERT_ID which would mean that I had to create 10 tables just to track my sequences.
There is better solution (based on StackOverflow question: Emulating a transaction-safe SEQUENCE in MySQL) outlined below:
create table ffzg_zs_seq (
name varchar(2) unique not null,
current integer unsigned not null
);
insert into ffzg_zs_seq values ('PA',100000);
insert into ffzg_zs_seq values ('PB',100000);
insert into ffzg_zs_seq values ('PC',100000);
insert into ffzg_zs_seq values ('PD',100000);
insert into ffzg_zs_seq values ('PE',100000);
insert into ffzg_zs_seq values ('DD',100000);
insert into ffzg_zs_seq values ('MR',100000);
insert into ffzg_zs_seq values ('DR',100000);
insert into ffzg_zs_seq values ('FO',100000);
insert into ffzg_zs_seq values ('SE',100000);
delimiter |
create function ffzg_zs_nextval( seq_name varchar(2) )
returns integer unsigned
begin
update ffzg_zs_seq set current = ( @next_val := current + 1 ) where name = seq_name ;
return @next_val;
end|
delimiter ;
This will create single table to hold all sequences and set all values to 100000 (have in mind that first usable number is 100001). Since I already have data in database I needed following snippet of SQL to set sequences to existing values:
update ffzg_zs_seq
set current=(
select
max(substring_index(itemcallnumber,' ',-1))
from items
where substring_index(itemcallnumber,' ',1) = ffzg_zs_seq.name
);
As you might guesses by now, I'm packing sequence name and number into single field items.itemcallnumber, so I had to use substring_index to extract existing values.
Usage is simple, allmost as good as built-in sequences:
mysql> select ffzg_zs_nextval('DD');
+-----------------------+
| ffzg_zs_nextval('DD') |
+-----------------------+
| 100178 |
+-----------------------+
1 row in set (0.00 sec)