Portable Sequence Generation with MySQL

Today I needed the ability to create a Oracle-style sequence generator outside of the normal MySQL auto-increment functionality.

I was performing a table format upgrade and one column needed a unique index and we’re not using auto-increment on this table.

This little bit of SQL should work fine:

SELECT @sequence:=IFNULL( @sequence + 1, 1 );

You’d have to use this construct with INSERT INTO ... SELECT constructs.

So for example:


CREATE TABLE TEST_SOURCE
(
ID BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY(ID));

INSERT INTO TEST_SOURCE (ID)
VALUES (NULL), (NULL), (NULL), (NULL), (NULL), (NULL);

… will give us a TEST_SOURCE table with 6 auto-increment values.


mysql> SELECT * FROM TEST_SOURCE;
+----+
| ID |
+----+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
+----+
6 rows in set (0.00 sec)

Now let’s migrate from TEST_SOURCE to TEST:


CREATE TABLE TEST
(
ID BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
VALUE BIGINT,
PRIMARY KEY(ID));

INSERT INTO TEST (ID,VALUE)
SELECT ID, (SELECT @sequence:=IFNULL( @sequence+1, 1 ))
FROM TEST_SOURCE;

… now let’s see our sequences:


mysql> SELECT * FROM TEST;
+----+-------+
| ID | VALUE |
+----+-------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
| 5 | 5 |
| 6 | 6 |
+----+-------+
6 rows in set (0.00 sec)

The second VALUE column was generated from the sequence.

Here’s what’s really cool about this little hack – it works with replication!

How does this work? Each time you run the subquery the @sequence variable is incremented and then returned. The first pass returns NULL but the IFNULL function will return 1 in this case.

In MySQL 5.0 an later it might be better to use a sequence table and a stored procedure but we haven’t upgraded across our cluster yet.


  1. This only works for current instance. How do you maintain durability (i.e. persistence) of the value?

  2. True….. you don’t.

    I just did it for a one time migration…

    It’s sort of a hack but it basically works for one time migrations….

  3. Jerry

    This could be pretty useful. Thanks for the tip. I have done this before using auto_increment in temporary tables, but your solution is cleaner I think.






%d bloggers like this: