Kevin Burton’s NEW FeedBlog

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));

… 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));

… 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.