Database Reference
In-Depth Information
When you generate a new
AUTO_INCREMENT
value, one way to get the value from the
server is to execute a statement that invokes the
LAST_INSERT_ID()
function. In addi‐
tion, many MySQL APIs provide a client-side mechanism for making the value available
without issuing another statement. This recipe discusses both methods and compares
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 uses
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; if another client inserts a row before you issue the
SELECT
statement,
MAX(id)
returns that client's ID, not yours. It's possible to solve this problem by grouping
the
INSERT
and
SELECT
statements as a transaction or locking the table, but MySQL
provides a simpler way to obtain the proper value: invoke the
LAST_INSERT_ID()
func‐
tion. It returns the most recent
AUTO_INCREMENT
value generated within your session,
regardless of what other clients are doing. For example, to insert a row into the
in
sect
table and retrieve its
id
value, do this:
mysql>
INSERT INTO insect (name,date,origin)
->
VALUES('cricket','2014-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
it is:
mysql>
INSERT INTO insect (name,date,origin)
->
VALUES('moth','2014-09-14','windowsill');
mysql>
SELECT * FROM insect WHERE id = LAST_INSERT_ID();
+----+------+------------+------------+
| id | name | date | origin |
+----+------+------------+------------+
| 10 | moth | 2014-09-14 | windowsill |
+----+------+------------+------------+
The server maintains the value returned by
LAST_INSERT_ID()
on a session-specific
basis. This property is by design, and it's important because it prevents clients from
interfering with each other. When you generate an
AUTO_INCREMENT
value,
LAST_IN
SERT_ID()
returns that specific value, even when other clients generate new rows in the
same table in the meantime.