Recipe 11.4. Retrieving Sequence Values

Problem

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

Solution

Issue a SELECT LAST_INSERT_ID⁠(⁠ ⁠ ⁠) statement. If you're writing a program, your MySQL API may provide a way to get the value directly without issuing a statement.

Discussion

Many applications need to determine the AUTO_INCREMENT value of a newly created row. For example, if you get ambitious and write a web-based frontend for entering rows into Junior's insect table, you might have the application display each new row nicely formatted in a new page immediately after you hit the Submit button. To do this, you need to know the new id value so that you can retrieve the proper row. Another common situation in which the AUTO_INCREMENT value is needed occurs when you're using multiple tables: after inserting a row in a master table, typically, you'll need its ID so that you can create rows in other related tables that refer to the master row. (Section 11.13 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 statement 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 statement. This recipe discusses both methods and provides a comparison of their characteristics.

Using LAST_INSERT_ID⁠(⁠ ⁠ ⁠) to obtain AUTO_INCREMENT values

The obvious (but incorrect) way to determine a new row'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 statement does indeed return the maximum id value from the table, but it may not be the value that you generated. Suppose that you insert a row that generates an id value of 9. If another client inserts a row before you issue the SELECT statement, MAX(id) returns 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, regardless of what other clients are doing. For example, you can insert a row into the insect table and then retrieve its id value like this:

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

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

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

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

You might ask this question if you're concerned about the possibility of getting the wrong value from LAST_INSERT_ID⁠(⁠ ⁠ ⁠) when 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 when other clients generate new rows in the same table in the meantime. This behavior is by design.

Using API-specific methods to obtain AUTO_INCREMENT values

LAST_INSERT_ID⁠(⁠ ⁠ ⁠) is an 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 statement 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 statement. Most of our APIs have this capability.

Perl

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

$dbh->do ("INSERT INTO insect (name,date,origin)            VALUES('moth','2006-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','2006-09-14','windowsill')"); $sth->execute (); my $seq = $sth->{mysql_insertid}; 

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

Ruby

The Ruby DBI driver for MySQL exposes the client-side AUTO_INCREMENT value using the database handle func method that returns driver-specific values:

dbh.do("INSERT INTO insect (name,date,origin)         VALUES('moth','2006-09-14','windowsill')") seq = dbh.func(:insert_id) 

PHP

The native PHP interface for MySQL includes a function that returns the most recent AUTO_INCREMENT value, but the PEAR DB interface does not. On the other hand, PEAR DB does have its own sequence generation mechanism that you can use instead. See the PEAR documentation for details.

Python

The MySQLdb driver for DB-API provides an insert_id⁠(⁠ ⁠ ⁠) connection object method for getting the sequence value after you execute a statement that generates an AUTO_INCREMENT value:

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

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','2006-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','2006-09-14','windowsill')"); s.executeUpdate (); long seq = ((com.mysql.jdbc.PreparedStatement) s).getLastInsertID (); s.close (); 

Server-side and client-side sequence value 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, require 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, and then disconnect from the server and reconnect before attempting to access the value, you'll get zero. Within a given connection, the persistence of AUTO_INCREMENT values can be much longer on the server side of the connection:

• After you issue a statement 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 statement, not just those that generate AUTO_INCREMENT values. If you issue an INSERT statement that generates a new value and then issue some other statement 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: when a statement 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.