MySQL: LAST_INSERT_ID Function

This MySQL tutorial explains how to use the MySQL LAST_INSERT_ID function with syntax and examples.

Description

The MySQL LAST_INSERT_ID function returns the first AUTO_INCREMENT value that was set by the most recent INSERT or UPDATE statement that affected an AUTO_INCREMENT column.

Syntax

The syntax for the LAST_INSERT_ID function in MySQL is:

LAST_INSERT_ID( [expression] )

Parameters or Arguments

expression

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

Note

• If the most recent INSERT or UPDATE statement set more than one AUTO_INCREMENT value, the LAST_INSERT_ID function returns only the first AUTO_INCREMENT value.
• The LAST_INSERT_ID function returns the last AUTO_INCREMENT value on a client-by-client basis, so it will only return the last AUTO_INCREMENT value for your client. The value can not be affected by other clients.
• Executing the LAST_INSERT_ID function does not affect the value that LAST_INSERT_ID returns.

Applies To

The LAST_INSERT_ID function can be used in the following versions of MySQL:

• MySQL 5.7, MySQL 5.6, MySQL 5.5, MySQL 5.1, MySQL 5.0, MySQL 4.1, MySQL 4.0, MySQL 3.23

Example

Let's look at some MySQL LAST_INSERT_ID function examples and explore how to use the LAST_INSERT_ID function in MySQL.

For example, if we had the following suppliers table with an AUTO_INCREMENT field called supplier_id:

CREATE TABLE suppliers 
( supplier_id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
  supplier_name VARCHAR(50) NOT NULL,
  website VARCHAR(50) );

And the suppliers table contained the following records:

And we executed the following INSERT statement:

INSERT INTO suppliers
(supplier_name, website)
VALUES
('Oracle', 'www.oracle.com');

The suppliers table would now look like this:

And if we executed the LAST_INSERT_ID function as follows:

mysql> SELECT LAST_INSERT_ID();
Result: 4

The LAST_INSERT_ID function would return 4 since the last INSERT statement inserted a record into the suppliers table with a supplier_id (ie: AUTO_INCREMENT value) of 4.

Affecting more than one AUTO_INCREMENT value

Let's take a quick look at how the LAST_INSERT_ID function would behave if the most recent INSERT set more than one AUTO_INCREMENT value. In other words, what would happen if we inserted 2 records with our last INSERT statement.

Let's look again at the suppliers table with an AUTO_INCREMENT field called supplier_id:

CREATE TABLE suppliers 
( supplier_id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
  supplier_name VARCHAR(50) NOT NULL );

And the suppliers table contained the following records:

We also have a customers table with the following records:

And we executed the following INSERT statement that uses a SELECT statement to insert more than one record into the suppliers table:

INSERT INTO suppliers
(supplier_name)
SELECT customer_name
FROM customers
ORDER BY customer_id;

After executing this INSERT statement, the suppliers table would now look like this:

As you can see the INSERT statement inserted 2 new records into the suppliers table (supplier_id=5 and supplier_id=6).

Now when we execute the LAST_INSERT_ID function as follows:

mysql> SELECT LAST_INSERT_ID();
Result: 5

The LAST_INSERT_ID function would return 5 because the record with the supplier_id=5 was the first AUTO_INCREMENT value to be set by the most recent INSERT statement.