MySQL Tutorial/Information Functions/LAST INSERT ID

LAST_INSERT_ID(expr)

   <source lang="sql">

mysql> mysql> CREATE TABLE sequence (id INT NOT NULL); Query OK, 0 rows affected (0.05 sec) mysql> mysql> INSERT INTO sequence VALUES (0); Query OK, 1 row affected (0.00 sec) mysql> mysql> UPDATE sequence SET id=LAST_INSERT_ID(id+1); Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> mysql> SELECT LAST_INSERT_ID(); +------------------+ | LAST_INSERT_ID() | +------------------+ | 1 | +------------------+ 1 row in set (0.00 sec) mysql> mysql> drop table sequence; Query OK, 0 rows affected (0.00 sec) mysql></source>

LAST_INSERT_ID() returns the first automatically generated value set for an AUTO_INCREMENT column

If you insert multiple rows using a single INSERT statement, LAST_INSERT_ID() returns the value generated for the first inserted row only.

   <source lang="sql">

mysql> mysql> CREATE TABLE t (

   ->  id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
   ->  name VARCHAR(10) NOT NULL
   -> );

Query OK, 0 rows affected (0.03 sec) mysql> mysql> INSERT INTO t VALUES (NULL, "Bob"); Query OK, 1 row affected (0.00 sec) mysql> mysql> SELECT * FROM t; +----+------+ | id | name | +----+------+ | 1 | Bob | +----+------+ 1 row in set (0.00 sec) mysql> SELECT LAST_INSERT_ID(); +------------------+ | LAST_INSERT_ID() | +------------------+ | 1 | +------------------+ 1 row in set (0.00 sec) mysql> mysql> INSERT INTO t VALUES (NULL, "Mary"),

   ->                      (NULL, "Jane"),
   ->                      (NULL, "Lisa");

Query OK, 3 rows affected (0.02 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> mysql> SELECT * FROM t; +----+------+ | id | name | +----+------+ | 1 | Bob | | 2 | Mary | | 3 | Jane | | 4 | Lisa | +----+------+ 4 rows in set (0.00 sec) mysql> mysql> SELECT LAST_INSERT_ID(); +------------------+ | LAST_INSERT_ID() | +------------------+ | 2 | +------------------+ 1 row in set (0.00 sec) mysql> mysql> drop table t; Query OK, 0 rows affected (0.00 sec)</source>