With Safari, you learn the way you learn best. Get unlimited access to videos, live online training,
learning paths, books, tutorials, and more.

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training,
learning paths, books, interactive tutorials, and more.