SQL Tutorial SQL Advanced SQL Database SQL References

MySQL LAST_INSERT_ID() Function



With no arguments, the MySQL LAST_INSERT_ID() function returns the first automatically generated value successfully inserted for an AUTO_INCREMENT column as a result of the most recently executed INSERT statement. The value of LAST_INSERT_ID() remains unchanged if no rows are successfully inserted.

With an argument, this function will return the value of the expression and the next call to LAST_INSERT_ID() will return the same value.

Syntax

LAST_INSERT_ID(expression)

Parameters

expression Optional. If it is specified, the value of the expression is returned and remembered as the next value to be returned by the LAST_INSERT_ID() function.

Return Value

Returns the AUTO_INCREMENT ID of the last row that has been inserted or updated in a table.

Example:

Consider the example below, where a table called Employee is created with an AUTO_INCREMENT field called EmpID. After creating the table, the LAST_INSERT_ID() function is used to get the AUTO_INCREMENT ID in various scenarios.

mysql> CREATE TABLE Employee (
  EmpID INT AUTO_INCREMENT PRIMARY KEY, 
  Name VARCHAR(50));

mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                0 |
+------------------+

mysql> INSERT INTO Employee(Name) Values ('John');

mysql> INSERT INTO Employee(Name) Values ('Marry');

mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                2 |
+------------------+

mysql> INSERT INTO Employee(Name) Values ('Kim') , ('Jo');

mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                3 |
+------------------+

mysql> SELECT * FROM Employee;
+-------+-------+
| EmpID | Name  |
+-------+-------+
| 1     | John  |
| 2     | Marry |
| 3     | Kim   |
| 4     | Jo    |
+-------+-------+

mysql> SELECT LAST_INSERT_ID(10);
+--------------------+
| LAST_INSERT_ID(10) |
+--------------------+
|                 10 |
+--------------------+

mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|               10 |
+------------------+

mysql> INSERT INTO Employee(Name) Values ('Ramesh');

mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                5 |
+------------------+

mysql> SELECT * FROM Employee;
+-------+--------+
| EmpID | Name   |
+-------+--------+
| 1     | John   |
| 2     | Marry  |
| 3     | Kim    |
| 4     | Jo     |
| 5     | Ramesh |
+-------+--------+

❮ MySQL Functions