I am of the attitude, the day you stop learning something is the day you die. I’m not prepared to induce MySQL into both sides of that equation, however some days it never ceases to amaze me what little thing I didn’t know about MySQL.
Today I saw in reviewing SQL statements for an application SELECT LAST_INSERT_ID(). No big deal, that is expected, however I then saw UPDATE … SET id=LAST_INSERT_ID(id+1) WHERE …
Having never seen this syntax I was forced to review it’s usage. See MySQL Documentation
If expr is given as an argument to LAST_INSERT_ID(), the value of the argument is returned by the function and is remembered as the next value to be returned by LAST_INSERT_ID(). This can be used to simulate sequences:
1.Create a table to hold the sequence counter and initialize it:
mysql> CREATE TABLE sequence (id INT NOT NULL);
mysql> INSERT INTO sequence VALUES (0);
2. Use the table to generate sequence numbers like this:
mysql> UPDATE sequence SET id=LAST_INSERT_ID(id+1);
mysql> SELECT LAST_INSERT_ID();
The UPDATE statement increments the sequence counter and causes the next call to LAST_INSERT_ID() to return the updated value. The SELECT statement retrieves that value. The mysql_insert_id() C API function can also be used to get the value. See Section 17.2.3.35, “mysql_insert_id()”.
Seems like a layman’s idea of Sequence support, but you are still restricted with the problems of a manual implementation. Transactions support, and if you use MyISAM, table level locking.
Sounds like an opportunity for avoiding auto_increment.
If this can be thrown in a SP that will handle the inserts in the table, can this be used to replace the auto_increment for a master-master replication configuration? I’m not really a fan of the auto_increment offset.
Thanks.