MySQL LAST_INSERT_ID() Function

The MySQL LAST_INSERT_ID() function returns the value generated by the last insert for the auto-increment column.

LAST_INSERT_ID() Syntax

Here is the syntax of the MySQL LAST_INSERT_ID() function:

LAST_INSERT_ID(expr)

Parameters

expr

Optional. An expression that should return an integer.

Return value

With no arguments, the LAST_INSERT_ID() function returns the value generated by the last insertion of the auto-increment column.

If multiple rows are inserted using a single INSERT statement , only the value generated for the first inserted row is returned.

If there is an argument, the LAST_INSERT_ID() function takes the value specified by the argument, and remembers this value as the next value returned by the LAST_INSERT_ID() function.

LAST_INSERT_ID() Examples

The following example shows how to use the LAST_INSERT_ID() function.

First, let’s create a table test_last_insert_id:

CREATE TABLE test_last_insert_id(
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    s VARCHAR(30) NOT NULL
);

Let’s insert a row for testing:

INSERT INTO test_last_insert_id (s) VALUES ('a');

Let’s query the data in the table:

SELECT * FROM test_last_insert_id;

+----+---+
| id | s |
+----+---+
|  1 | a |
+----+---+

Let’s get the value of the last inserted auto-increment column through the LAST_INSERT_ID() function:

SELECT LAST_INSERT_ID();

+------------------+
| LAST_INSERT_ID() |
+------------------+
|                1 |
+------------------+

Let’s insert 2 rows for testing:

INSERT INTO test_last_insert_id (s) VALUES ('b'), ('c');

Let’s query the data in the table:

SELECT * FROM test_last_insert_id;

+----+---+
| id | s |
+----+---+
|  1 | a |
|  2 | b |
|  3 | c |
+----+---+

Let’s get the value of the last inserted auto-increment column through the LAST_INSERT_ID() function:

SELECT LAST_INSERT_ID();

+------------------+
| LAST_INSERT_ID() |
+------------------+
|                2 |
+------------------+

Here, you might ask why the result is 2 instead of 3? This is because, if multiple rows are inserted by a single INSERT statement , only the value generated for the first inserted row is returned.