Creating sequences in MySQL

Posted on February 23, 2012 Comments

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.

Hope you find it useful.

Related Posts :

Comments

  1. since the select and update in function getNextSeq is not Atomic, will we get the same sequence value for each thread while there are many concurrent threads?

Pings

  1. Custom auto increment values » Fatal Exception

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre lang="" line="" escaped="" cssfile="">