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.
Search WWH ::




Custom Search