LAST_INSERT_ID()

USAGE

LAST_INSERT_ID([integer])

DETAILS

LAST_INSERT_ID() is an information function that Returns the value of the most recently auto-generated AUTO_INCREMENT column.

An AUTO_INCREMENT value is auto-generated when a row is inserted with an unspecified or NULL ID value.

If multiple rows are inserted, the return value is the ID of the first inserted row.

The return value is unique to each session, and defaults to 0 at the start of a new session.

Calling LAST_INSERT_ID() with an integer argument sets the saved value to the provided integer and returns that value.

• This is useful if you specified the ID value for an inserted row and want to run a statement that queries the LAST_INSERT_ID() value.

• Setting the saved value only changes the return value of this function. It does not change the last-inserted row's ID value or affect any future auto-generated values.

The return value can be consulted multiple times and is not changed by running other SQL statements (not even a ROLLBACK changes it).

The returned value in a running statement is always the value the function had at the start of the statement, so it is fine to use the function to insert a new row in a related table.

EXAMPLES

CREATE TABLE id_example (
  id SERIAL,
  description varchar(32)
);

The default at the start of a session is 0:

SELECT LAST_INSERT_ID();

+------------------+
| LAST_INSERT_ID() |
+------------------+
|                0 |
+------------------+

The value can be changed to any integer:

SELECT LAST_INSERT_ID(1234);

+----------------------+
| LAST_INSERT_ID(1234) |
+----------------------+
|                 1234 |
+----------------------+

INSERT INTO id_example VALUES (41, 'zero');

The return value is unchanged when the ID was not auto-generated:

SELECT LAST_INSERT_ID();

+------------------+
| LAST_INSERT_ID() |
+------------------+
|             1234 |
+------------------+

INSERT INTO id_example (description) VALUES
  ('one'), ('two'), ('three');

SELECT LAST_INSERT_ID();

+------------------+
| LAST_INSERT_ID() |
+------------------+
|               42 |
+------------------+

SELECT * FROM id_example;

+----+-------------+
| id | description |
+----+-------------+
| 41 | zero        |
| 42 | one         |
| 43 | two         |
| 44 | three       |
+----+-------------+

CHANGE HISTORY