MariaDB: LAST_INSERT_ID Function

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

Description

The MariaDB 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 MariaDB 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 MariaDB:

• MariaDB 10

Example

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

For example, if we had the following sites table with an AUTO_INCREMENT field called site_id:

CREATE TABLE sites 
( site_id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
  site_name VARCHAR(50) NOT NULL,
  server_name VARCHAR(50) );

And the sites table contained the following records:

And we executed the following INSERT statement:

INSERT INTO sites
(site_name, server_name)
VALUES
('CheckYourMath.com', 'YourServer');

The sites table would now look like this:

And if we executed the LAST_INSERT_ID function as follows:

SELECT LAST_INSERT_ID();
Result: 2

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

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 sites table with an AUTO_INCREMENT field called site_id:

CREATE TABLE sites 
( site_id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
  site_name VARCHAR(50) NOT NULL,
  server_name VARCHAR(50) );

And the sites table contained the following records:

And we executed the following INSERT statement that inserts more than one record into the sites table:

INSERT INTO sites
(site_name, server_name)
VALUES
('CheckYourMath.com', 'YourServer'),
('BigActivities.com', 'NewServer');

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

As you can see the INSERT statement inserted 2 new records into the sites table (site_id=2 and site_id=3).

Now when we execute the LAST_INSERT_ID function as follows:

SELECT LAST_INSERT_ID();
Result: 5

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