Retrieving Sequence Values

11.6.1 Problem

After creating a record that includes a new sequence number, you want to find out what that number is.

11.6.2 Solution

In a SQL statement, you can use the LAST_INSERT_ID( ) function. If you're writing a program, your MySQL API may provide a way to get the value directly without using LAST_INSERT_ID( ).

11.6.3 Discussion

Many applications need to determine the AUTO_INCREMENT value of a newly created record. For example, if you get ambitious and write a web-based frontend for entering records into Junior's insect table, you might have the application display each new record nicely formatted in a new page immediately after you hit the Submit button. To do this, you'll need to know the new id value so you can retrieve the proper record. Another common situation in which the AUTO_INCREMENT value is needed occurs when you're using multiple tables: after inserting a record in a master table, typically, you'll need its ID so that you can create records in other related tables that refer to the master record. (Recipe 11.16 shows how to relate multiple tables using sequence numbers.)

When you generate a new AUTO_INCREMENT value, you can get the value from the server by issuing a query that invokes the LAST_INSERT_ID( ) function. In addition, many MySQL APIs provide a client-side mechanism for making the value available without issuing another query. This section discusses both methods and provides a comparison of their differences.

11.6.4 Using LAST_INSERT_ID( ) to Obtain AUTO_INCREMENT Values

The obvious (but incorrect) way to determine a new record's AUTO_INCREMENT value is based on the fact that when MySQL generates the value, it becomes the largest sequence number in the column. Thus, you might try using the MAX( ) function to retrieve it:

SELECT MAX(id) FROM insect;

This is unreliable because it doesn't take into account the multithreaded nature of the MySQL server. The SELECT query does indeed return the maximum id value from the tablebut it may not be the value that you generated. Suppose you insert a record that generates an id value of 9. If another client inserts a record before you issue the SELECT query, MAX(id) will return 10, not 9. Methods for solving this problem include grouping the INSERT and SELECT statements as a transaction or locking the table, but MySQL provides a LAST_INSERT_ID( ) function as a simpler way to obtain the proper value. It returns the most recent AUTO_INCREMENT value that you generated during the time you've been connected to the server. For example, you can insert a record into the insect table, then retrieve its id value like this:

mysql> INSERT INTO insect (name,date,origin)
 -> VALUES('cricket','2001-09-11','basement');
mysql> SELECT LAST_INSERT_ID( );
+------------------+
| last_insert_id( ) |
+------------------+
| 9 |
+------------------+

Or you can use the new value to retrieve the entire record, without even knowing what the id is:

mysql> INSERT INTO insect (name,date,origin)
 -> VALUES('moth','2001-09-14','windowsill');
mysql> SELECT * FROM insect WHERE id = LAST_INSERT_ID( );
+----+------+------------+------------+
| id | name | date | origin |
+----+------+------------+------------+
| 10 | moth | 2001-09-14 | windowsill |
+----+------+------------+------------+

Can Other Clients Change the Value Returned by LAST_INSERT_ID()?

You may ask this question if you're concerned about the possibility of getting the wrong value from LAST_INSERT_ID( ) if other clients happen to generate AUTO_INCREMENT values at about the same time you do. There's nothing to worry about. The value returned by LAST_INSERT_ID( ) is maintained by the server on a connection-specific basis. This property is important because it prevents clients from interfering with each other. When you generate an AUTO_INCREMENT value, LAST_INSERT_ID( ) returns that specific value, even if other clients have generated new records in the same table in the meantime.

11.6.5 Using API-Specific Methods to Obtain AUTO_INCREMENT Values

LAST_INSERT_ID( ) is a SQL function, so you can use it from within any client that understands how to issue SQL statements. On the other hand, you do have to issue a separate query to get its value. If you're writing your own programs, you may have another choice. Many MySQL interfaces include an API-specific extension that returns the AUTO_INCREMENT value without issuing another query. In particular, each of our four APIs have this capability.

11.6.5.1 Perl

Use the mysql_insertid attribute to obtain the AUTO_INCREMENT value generated by a query. This attribute is accessed through either a database handle or a statement handle, depending on how you issue the query. The following example references it through the database handle:

$dbh->do ("INSERT INTO insect (name,date,origin)
 VALUES('moth','2001-09-14','windowsill')");
my $seq = $dbh->{mysql_insertid};

If you're using prepare( ) and execute( ), access mysql_insertid as a statement handle attribute:

my $sth = $dbh->prepare ("INSERT INTO insect (name,date,origin)
 VALUES('moth','2001-09-14','windowsill')");
$sth->execute ( );
my $seq = $sth->{mysql_insertid};

If you find that the value of the mysql_insertid attribute is always zero, you probably have an old version of DBD::mysql that doesn't support it. Try using the insertid attribute instead. (In this case, insertid is available only as a database handle attribute.)

11.6.5.2 PHP

After issuing a query that generates an AUTO_INCREMENT value, retrieve the value by calling mysql_insert_id( ):

mysql_query ("INSERT INTO insect (name,date,origin)
 VALUES('moth','2001-09-14','windowsill')", $conn_id);
$seq = mysql_insert_id ($conn_id);

The argument should be a connection identifier. If no argument is given, mysql_insert_id( ) uses the most recently opened connection.

11.6.5.3 Python

The MySQLdb driver for DB-API provides an insert_id( ) cursor method for getting sequence values. Use it with the cursor object through which you execute a query that generates an AUTO_INCREMENT value:

cursor = conn.cursor ( )
cursor.execute ("""
 INSERT INTO insect (name,date,origin)
 VALUES('moth','2001-09-14','windowsill')
 """)
seq = cursor.insert_id ( )

11.6.5.4 Java

The MySQL Connector/J JDBC driver provides a getLastInsertID( ) method for obtaining AUTO_INCREMENT values. It can be used with either Statement or PreparedStatement objects. This example uses a Statement:

Statement s = conn.createStatement ( );
s.executeUpdate (
 "INSERT INTO insect (name,date,origin)"
 + " VALUES('moth','2001-09-14','windowsill')");
long seq = ((com.mysql.jdbc.Statement) s).getLastInsertID ( );
s.close ( );

Note that because getLastInsertID( ) is driver-specific, you access it by casting the Statement object to the com.mysql.jdbc.Statement type. If you're using a PreparedStatement object, cast it to the com.mysql.jdbc.PreparedStatement type instead:

PreparedStatement s = conn.prepareStatement (
 "INSERT INTO insect (name,date,origin)"
 + " VALUES('moth','2001-09-14','windowsill')");
s.executeUpdate ( );
long seq = ((com.mysql.jdbc.PreparedStatement) s).getLastInsertID ( );
s.close ( );

11.6.6 Server-Side and Client-Side SequenceValue Retrieval Compared

As mentioned earlier, the value of LAST_INSERT_ID( ) is maintained on a connection-specific basis on the server side of the MySQL connection. By contrast, the API-specific methods for accessing AUTO_INCREMENT values directly are implemented on the client side. Server-side and client-side sequence value retrieval methods have some similarities, but also some differences.

All methods, both server-side and client-side, have in common the property that you must access the AUTO_INCREMENT value using the same MySQL connection that was used to generate the value in the first place. If you generate an AUTO_INCREMENT value, then disconnect from the server and reconnect before attempting to access the value, you'll get zero. On the other hand, the persistence of AUTO_INCREMENT values can be much longer on the server side of the connection:

• After you issue a query that generates an AUTO_INCREMENT value, the value remains available through LAST_INSERT_ID( ) even if you issue other statements, as long as none of those statements generate an AUTO_INCREMENT value.
• The sequence value available on the client side typically is set for every query, not just those that generate AUTO_INCREMENT values. If you issue an INSERT statement that generates a new value, then issue some other query before accessing the client-side sequence value, it probably will have been set to zero. The precise behavior varies among APIs, but if you use the following general guideline, you should be safe: if a query generates a sequence value that you won't be using immediately, save the value in a variable that you can refer to later. Otherwise, you may find that the sequence value has been wiped out when you do try to access it.