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.
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.
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:
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 query, refer to the @saved_id variable.
cursor1 = conn.cursor ( ) cursor2 = conn.cursor ( ) gen_seq_val (cursor1) # issue query that generates a sequence number gen_seq_val (cursor2) # issue another, using a different cursor seq1 = cursor1.insert_id ( ) seq2 = cursor2.insert_id ( ) print "seq1:", seq1, "seq2:", seq2 # these values will be different cursor1.close ( ) cursor2.close ( )
In Perl, you can achieve the same effect by means of two statement handles; the mysql_insertid attribute for each is unaffected by query activity on the other. In Java, use separate Statement or PreparedStatement objects.
The third technique doesn't work with PHP, because there is no client-side object or structure that maintains AUTO_INCREMENT values on a query-specific basis. The client-side AUTO_INCREMENT value is returned by mysql_insert_id( ), which is tied to the connection, not to a statement. Yes, I know what you're thinking: a workaround would be to open a second connection to the server and issue the first and second queries over the different connections. You're right, that would workbut it's not worth the effort. The overhead of opening another connection is much higher than simply saving the mysql_insert_id( ) value in a PHP variable before issuing another query. Furthermore, opening a second connection isn't as straightforward as it might seem. If you issue a second mysql_connect( ) or mysql_pconnect( ) call with the same connection parameters as the original call, PHP returns the same connection identifier as the one it returned originally! You'd have to connect to the server as a different user to get a truly independent connection identifier. (At the risk of muddying the waters, I should point out that as of PHP 4.2.0, mysql_connect( ) supports the option of explicitly forcing a new connection to be opened. You can use this feature to maintain separate client-side AUTO_INCREMENT values.)
Using the mysql Client Program
Writing MySQL-Based Programs
Record Selection Techniques
Working with Strings
Working with Dates and Times
Sorting Query Results
Modifying Tables with ALTER TABLE
Obtaining and Using Metadata
Importing and Exporting Data
Generating and Using Sequences
Using Multiple Tables
Introduction to MySQL on the Web
Incorporating Query Resultsinto Web Pages
Processing Web Input with MySQL
Using MySQL-Based Web Session Management
Appendix A. Obtaining MySQL Software
Appendix B. JSP and Tomcat Primer
Appendix C. References