In MySQL, the 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.

It can also be called with an argument, in which case, it returns the value of the expression and the next call to LAST_INSERT_ID() will return the same value.

Syntax

The function can be called in the following ways:

LAST_INSERT_ID()
LAST_INSERT_ID(expr)

Where expr is returned, and the next call to LAST_INSERT_ID() will return the same value.

Example

As an example, let’s create a table with an AUTO_INCREMENT column:

CREATE TABLE idiots (
  idiot_id INT NOT NULL AUTO_INCREMENT,
  idiot_name VARCHAR(255) NOT NULL,
  PRIMARY KEY (idiot_id)
);

The idiot_id column uses AUTO_INCREMENT for its value.

Now insert some rows:

INSERT INTO idiots (idiot_name) VALUES ('Dumb');
INSERT INTO idiots (idiot_name) VALUES ('Dumber');
INSERT INTO idiots (idiot_name) VALUES ('Dumbest');

Now let’s run LAST_INSERT_ID():

SELECT LAST_INSERT_ID();

Result:

+------------------+
| LAST_INSERT_ID() |
+------------------+
|                3 |
+------------------+

It returns 3.

Now, let’s return all rows in the table to verify that the last row has an AUTO_INCREMENT value of 3 inserted:

SELECT * FROM idiots;

Result:

+----------+------------+
| idiot_id | idiot_name |
+----------+------------+
|        1 | Dumb       |
|        2 | Dumber     |
|        3 | Dumbest    |
+----------+------------+

As expected, the idiot_id column goes up to 3.

Including an Argument

As mentioned, if called with an argument, the function returns the value of the expression and the next call to LAST_INSERT_ID() will return the same value.

SELECT LAST_INSERT_ID( 22 );

Result:

+----------------------+
| LAST_INSERT_ID( 22 ) |
+----------------------+
|                   22 |
+----------------------+

Now call it again, but with no argument:

SELECT LAST_INSERT_ID();

Result:

+------------------+
| LAST_INSERT_ID() |
+------------------+
|               22 |
+------------------+

It still returns a value of 22.

However, if we continue to insert values into our idiots table, the AUTO_INCREMENT will continue where it left off on that table:

INSERT INTO idiots (idiot_name) VALUES ('Homer');

Now let’s run LAST_INSERT_ID():

SELECT LAST_INSERT_ID();

Result:

+------------------+
| LAST_INSERT_ID() |
+------------------+
|                4 |
+------------------+

It returns 4.

And here’s what the table looks like now:

SELECT * FROM idiots;

Result:

+----------+------------+
| idiot_id | idiot_name |
+----------+------------+
|        1 | Dumb       |
|        2 | Dumber     |
|        3 | Dumbest    |
|        4 | Homer      |
+----------+------------+

For more information on this function, see the MySQL documentation.