Recipe 11.12. Managing Multiple Simultaneous AUTO_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 user-defined variables for later. If you're using statements from within a program, save the sequence values in program variables. Alternatively, you might be able to issue the statements using separate connection or statement objects to keep them from getting mixed up.

Discussion

As described in Section 11.4, the LAST_INSERT_ID⁠(⁠ ⁠ ⁠) server-side sequence value indicator function is set each time a statement generates an AUTO_INCREMENT value, whereas client-side sequence indicators may be reset for every statement. What if you issue a statement that generates an AUTO_INCREMENT value, but you 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 user-defined variable after issuing a statement 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 value in a subsequent statement, refer to the @saved_id variable.

• At the API level, you can save the AUTO_INCREMENT value in an API language variable. This can be done either by saving the value returned from LAST_INSERT_ID⁠(⁠ ⁠ ⁠) or from any API-specific extension that might be available.

• A third technique can be used from within APIs that enables you to maintain separate client-side AUTO_INCREMENT values. For example, statement handles in Perl have a mysql_insertid attribute, and the attribute value for one handle is unaffected by activity on another. In Java, use separate Statement or PreparedStatement objects.