Managing Multiple SimultaneousAUTO_INCREMENT Values

11.15.1 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.

11.15.2 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.

11.15.3 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:

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.)

Категории