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 …