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:

• At the SQL level, you can save the value in a SQL variable after issuing a query 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 query, 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 allow you to maintain separate client-side AUTO_INCREMENT values. For example, in Python, when you use a cursor object to execute a query, the AUTO_INCREMENT value generated by the query is available by calling the cursor's insert_id( ) method. If you issue other queries using the same cursor, that value will be lost. However, if you use a different cursor object to execute additional queries, the original cursor's insert_id value will be unaffected:

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