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.
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.
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 | +----+------+------------+------------+
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.
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.)
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)
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.
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 ()
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 ();
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.
3.142.200.109