In MariaDB, LAST_INSERT_ID() is a built-in function that 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 guest (
  guest_id INT NOT NULL AUTO_INCREMENT,
  guest_name VARCHAR(255) NOT NULL,
  PRIMARY KEY (guest_id)
);

The guest_id column uses AUTO_INCREMENT for its value.

Now insert some rows:

INSERT INTO guest (guest_name) VALUES ('Homer');
INSERT INTO guest (guest_name) VALUES ('Bart');
INSERT INTO guest (guest_name) VALUES ('Marge');

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 guest;

Result:

+----------+------------+
| guest_id | guest_name |
+----------+------------+
|        1 | Homer      |
|        2 | Bart       |
|        3 | Marge      |
+----------+------------+

The guest_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(9);

Result:

+-------------------+
| LAST_INSERT_ID(9) |
+-------------------+
|                 9 |
+-------------------+

Now call it again, but with no argument:

SELECT LAST_INSERT_ID();

Result:

+------------------+
| LAST_INSERT_ID() |
+------------------+
|                9 |
+------------------+

It still returns a value of 9.

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

INSERT INTO guest (guest_name) VALUES ('Lisa');

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 guest;

Result:

+----------+------------+
| guest_id | guest_name |
+----------+------------+
|        1 | Homer      |
|        2 | Bart       |
|        3 | Marge      |
|        4 | Lisa       |
+----------+------------+

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