MySQL Cookbook, 2nd Edition

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. (Using AUTO_INCREMENT Values to Relate Tables 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 ...