MySQL Cookbook

Managing Multiple SimultaneousAUTO_INCREMENT Values

Problem

You’re working with two or more tables that contain AUTO_INCREMENT columns, and you’re having a hard time keeping track of the sequence values generated for each table.

Solution

Save the values in SQL variables for later. If you’re using queries from within a program, save the sequence values in program variables; or you may be able to issue the queries using separate connection or statement objects to keep them from getting mixed up.

Discussion

As described in Recipe 11.6, the LAST_INSERT_ID( ) server-side sequence value indicator function is set each time a query generates an AUTO_INCREMENT value, whereas client-side sequence indicators may be reset for every query. What if you issue a statement that generates an AUTO_INCREMENT value, but don’t want to refer to that value until after issuing a second statement that also generates an AUTO_INCREMENT value? In this case, the original value no longer will be accessible, either through LAST_INSERT_ID( ) or as a client-side value. To retain access to it, you should save the value first before issuing the second statement. There are several ways to do this:

• At the SQL level, you can save the value in a SQL variable after issuing a query that generates an AUTO_INCREMENT value:

  INSERT INTO tbl_name (id,...) VALUES(NULL,...);
  SET @saved_id = LAST_INSERT_ID( );

  Then you can issue other statements without regard to their effect on LAST_INSERT_ID( ). To use the original AUTO_INCREMENT ...