MySQL Cookbook

Retrieving Sequence Values

Problem

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

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

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.

Using LAST_INSERT_ID( ) to Obtain AUTO_INCREMENT ...