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_INCREMENTvalue: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 originalAUTO_INCREMENT ...