Returns the last value inserted into an AUTO_INCREMENT column.

Syntax

LAST_INSERT_ID()

Return type

Integer

Remarks

• LAST_INSERT_ID() only returns automatically generated AUTO_INCREMENT values. The value of LAST_INSERT_ID() remains unchanged if you set the AUTO_INCREMENT column of a row to a explicit or non-automatically generated value.
• LAST_INSERT_ID is maintained on a per-connection basis.
• After a multi-insert statement, the value returned by LAST_INSERT_ID() is the first value inserted in that group of records.
• The value of LAST_INSERT_ID() remains unchanged if no record is successfully added in an INSERT statement.
• While using INSERT IGNORE if the row is ignored, the value of LAST_INSERT_ID remains unchanged.
• If the value of LAST_INSERT_ID is changed inside a stored procedure, the changed value is seen by statements executed after the procedure call.
• If the value of LAST_INSERT_ID is changed inside a stored function, the value is restored when the function ends.
• LAST_INSERT_ID works with UPDATE/INSERT statements, but it may not work as expected with SELECT statements because of the distributed nature of MemSQL.

Examples

In the following example, LAST_INSERT_ID() returns 2 when executed after a multi-insert statement, which is the first inserted value in the statement.

CREATE TABLE persons (
 id BIGINT PRIMARY KEY AUTO_INCREMENT,
 firstname VARCHAR(64),
 lastname VARCHAR(64));

INSERT INTO persons VALUES
 (NULL, 'Eponymous', 'Bach');

SELECT LAST_INSERT_ID();
****
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                1 |
+------------------+

INSERT INTO persons VALUES
 (NULL, 'Ping', 'Baudot'),
 (NULL, 'Count', 'Modulo'),
 (NULL, 'Hugh', 'Rustic');

SELECT LAST_INSERT_ID();
****
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                2 |
+------------------+
1 row in set (0.00 sec)

Following each previous INSERT statement, the value of LAST_INSERT_ID() was changed since a NULL value was inserted in the AUTO_INCREMENT column.

INSERT INTO persons VALUES (5, 'Grant', 'Acos');

SELECT LAST_INSERT_ID();
****
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                2 |
+------------------+

Was this Article Helpful?

Thanks For Your Feedback!