Creating sequences in MySQL
Those who have used PostgresSQL usually gets lazy when write the SQL code to auto-numeric fields, but we miss that feature when we are in a MySQL database.
As we know, MySQL have auto-increment fields, that are very useful too, but we miss that feature when we are in a PostgreSQL database.
I will try to explain how to simulate sequences like PostgreSQL in MySQL. But in what situations we would need sequences in MySQL? some ideas:
- When we need more than one auto-numeric field in the same table.
- When we need a global counter to use in more than one table, procedures, etc.
- I can’t think more situations, but I’m sure at some point we can found one.
OK let’s start:
We need to create a table to store the current sequence value (if this table is created inside mysql database is not bad idea)
create table _sequence ( seq_name varchar(50) not null primary key, seq_val int unsigned not null );
Now we need a function to get the next sequence value, including these cases:
- Create a sequence name if the sequence name don’t exists (is not necessary get complicated).
- Increase the sequence number each function call.
There is the function getNextSeq it receive sequence name as parameter:
delimiter // drop function if exists getNextSeq// create function getNextSeq(sSeqName varchar(50)) returns int unsigned begin declare nLast_val int; set nLast_val = (select seq_val from _sequence where seq_name = sSeqName); if nLast_val is null then set nLast_val = 1; insert into _sequence (seq_name,seq_val) values (sSeqName,nLast_Val); else set nLast_val = nLast_val + 1; update _sequence set seq_val = nLast_val where seq_name = sSeqName; end if; return nLast_val; end// delimiter ;
The next step is to create (to maintain purposes) a stored procedure to modify the current sequence value:
drop procedure if exists sp_setSeqVal// create procedure sp_setSeqVal(sSeqName varchar(50), nVal int unsigned) begin if (select count(*) from _sequence where seq_name = sSeqName) = 0 then insert into _sequence (seq_name,seq_val) values (sSeqName,nVal); else update _sequence set seq_val = nVal where seq_name = sSeqName; end if; end// delimiter ;
That’s it!, just one more thing: testing. Creating a table with two auto-numeric fields using simulated sequences:
create table multiple_autonums ( id int not null primary key auto_increment, seq_1 int, -- sequence 1 seq_2 int, -- sequence 2 unique(seq_1), unique(seq_2) );
We must create a before-insert trigger on table:
delimiter // drop trigger if exists multiple_autonums_ai// create trigger multiple_autonums_bi before insert on multiple_autonums for each row begin set new.seq_1 = getNextSeq("seq_1"); set new.seq_2 = getNextSeq("seq_2"); end// delimiter ;
Run some tests:
-- inserting new values. insert into multiple_autonums (id) values (null),(null),(null); -- getting the inserted values select * from multiple_autonums; +----+-------+-------+ | id | seq_1 | seq_2 | +----+-------+-------+ | 1 | 1 | 1 | | 2 | 2 | 2 | | 3 | 3 | 3 | +----+-------+-------+ 3 rows in set (0.00 sec) -- Changing sequence current values call sp_setSeqVal("seq_1",47); call sp_setSeqVal("seq_2",9786); -- Inserting new values insert into multiple_autonums (id) values (null),(null),(null); -- Seeing what happen. select * from multiple_autonums; +----+-------+-------+ | id | seq_1 | seq_2 | +----+-------+-------+ | 1 | 1 | 1 | | 2 | 2 | 2 | | 3 | 3 | 3 | | 4 | 48 | 9787 | | 5 | 49 | 9788 | | 6 | 50 | 9789 | +----+-------+-------+
What do you think?
This simulated sequence don’t consider some stuffs but it works and can be helpful. Even you can modify the function to make more complex generation of next value.
Pingback: Custom auto increment values » Fatal Exception