Implementing sequences using a stored function and triggers!

In MySQL 5.0.10 we now have the functionality to create an automatic sequence generator using a trigger that calls a stored function. You could even have a global sequence if you wish, that is, a sequence that is used by multiple tables.

/* For this example, we'll put the sequences table in the test database. */
USE test;

/* Create a sequence table */
CREATE TABLE IF NOT EXISTS sequences
(name CHAR(20) PRIMARY KEY,
val INT UNSIGNED);

DROP FUNCTION IF EXISTS nextval;

DELIMITER //

/* The actual sequence function. Call nextval('seqname'), and it returns the next value. */
/* If the named sequence does not yet exist, it is created with initial value 1. */
CREATE FUNCTION nextval (seqname CHAR(20))
RETURNS INT UNSIGNED
BEGIN
INSERT INTO sequences VALUES (seqname,LAST_INSERT_ID(1))
ON DUPLICATE KEY UPDATE val=LAST_INSERT_ID(val+1);
RETURN LAST_INSERT_ID();
END
//

DELIMITER ;

/* Let's now use a sequence in a test table... */
CREATE TABLE IF NOT EXISTS data
(id int UNSIGNED NOT NULL PRIMARY KEY DEFAULT 0,
info VARCHAR(50));

DROP TRIGGER nextval;
/* The trigger only generates a new id if 0 is inserted. */
/* The default value of id is also 0 (see the create table statement) so that makes it implicit. */
CREATE TRIGGER nextval BEFORE INSERT ON data
FOR EACH ROW SET new.id=IF(new.id=0,nextval('data'),new.id);

TRUNCATE TABLE data;

INSERT INTO data (info) VALUES ('bla');
INSERT INTO data (info) VALUES ('foo'),('bar');
SELECT * FROM data;

+----+------+
| id | info |
+----+------+
|  1 | bla  |
|  2 | foo  |
|  3 | bar  |
+----+------+

Pretty neat, eh?
The sequences table and the nextval() function can, as you can see, handle multiple sequences. Your trigger just identifies the one it wants. In our example, we simply have a sequence name that is the same as our table name.

10 thoughts on “Implementing sequences using a stored function and triggers!”

  1. Excuse-me, but what’s the usefulness of this ?

    This slow & awkward technique has been known for decades and is the only way in Oracle to have what MySql always had, ie auto_increment primary keys.

    So what’s this useful for ? for auto_incrementing a non-pri-key column ?

    Regards

    VGR

    ———————
    http://www.europeanexperts.org

    #1 expert at PHP & MySql on http://www.experts-exchange.com before I left in Oct 2003 ;-)
    I left my place for “VB” Mike Hillyer to take it ;-)

  2. For me, I was just playing with some new trigger and stored proc functionality. In that sense, this was a nice demo ;-)

    I agree that for most purposes, AUTO_INCREMENT does fine. But… sometimes people want other things. Sequences are more programmable than auto-inc, so if you want to do for instance funny increments, that is also possible. You can also can have a single sequence apply to multiple tables, and people sometimes want that.

    In the case of migration, apps sometimes rely on certain weird behaviour. When migrating, you could use something like the above to reproduce the same result and keep the app happy.

    But really, primarily, it was just a little exercise.

  3. Your method produced on my MySQL 5.0.10 instance these results:

    mysql> SELECT * FROM data;
    +—-+——+
    | id | info |
    +—-+——+
    | 0 | bla |
    | 2 | foo |
    | 3 | bar |
    +—-+——+

    The first ‘0’ is wrong. It appears because the first call of nextval() returns 0, because
    the first INSERT doesn’t produce duplicate key error, and LAST_INSEERT_ID isn’t called. So
    LAST_INSERT_ID() returns 0, and RETURN gives you zero as well.

    To produce a correct results I suggest to use this function:

    CREATE FUNCTION nextval (seqname CHAR(20))
    RETURNS INT UNSIGNED
    BEGIN
    INSERT INTO sequences VALUES (seqname,1)
    ON DUPLICATE KEY UPDATE val=LAST_INSERT_ID(val+1);
    RETURN IF(LAST_INSERT_ID()=0,1,LAST_INSERT_ID());
    END

  4. Hey, right you are. LAST_INSERT_ID() isn’t set by the INSERT because there’s no AUTO_INCREMENT column. Silly me!

    This should also work:

    INSERT INTO sequences VALUES (seqname,LAST_INSERT_ID(1))
    ON DUPLICATE KEY UPDATE val=LAST_INSERT_ID(val+1);

  5. I see a second use for this. I have four different entities, each of which must use a unique primary key. The key must be unique across the different entities. Each entity is described, physically, in its own table. However, since there are four different tables, I can’t use an auto_increment column in each table, b/c the key won’t be unique across the tables (for those who are interested, these entities are derived from a base entity, so they cannot efficiently all be stored in one table). How to generate keys that are unique across all four derived tables? A sequence.

  6. That kinda bites general design principles.
    The normal way to handle such a situation is:
    Only one table has an auto-inc PK, the others have a foreign key to that first table.
    You first insert into the first table, get the assigned id – using mysql_insert_d() or SELECT LAST_INSERT_ID() – and then insert into the other tables.
    Easy.

  7. I m very new to MqSql, The above thing is very nice, it took just 5 to 10 minute for me to do this successfully.
    My sincere thanks for this..
    Keep going..

  8. Actually this is _not_ what Oracle is doing. Oracle is using sequences. This is done because they scale to high volume INSERTs.

    The technique above and the auto_increment _both_ have a single counter that forces serialisation of all inserts. If your INSERTs are measured in Mb/s this becomes a problem.

    Oracle sequences are designed to allow parallel inserts.

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>