MySQL Cookbook, 2nd Edition

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 Retrieving Sequence Values, 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 INTOtbl_name (id,...) VALUES(NULL,...);
  SET @saved_id = LAST_INSERT_ID();

  Then you can issue other statements without regard to their effect ...