MariaDB LAST_INSERT_ID() Function

In MariaDB, LAST_INSERT_ID() is a built-in function that returns the value generated by the auto AUTO_INCREMENT column for the last insertion.

MariaDB LAST_INSERT_ID() Syntax

Here is the syntax of the MariaDB LAST_INSERT_ID() function:

LAST_INSERT_ID()
LAST_INSERT_ID(expr)

Parameters

expr

Optional. An expression that should return an integer.

Return value

Without parameters, the LAST_INSERT_ID() function returns the value generated by the AUTO_INCREMENT column for the last insertion.

With an parameter, the LAST_INSERT_ID() function returns the value of that argument and remembers this value as the next returning value.

If multiple rows are inserted using a single INSERT statement, LAST_INSERT_ID() will return the value generated for the first inserted row.

LAST_INSERT_ID() Examples

The following example shows how to use the LAST_INSERT_ID() function.

First, let’s create a table named test_last_insert_id:

CREATE TABLE test_last_insert_id(
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    s VARCHAR(30) NOT NULL
);

Let’s insert 1 row for testing:

INSERT INTO test_last_insert_id (s) VALUES ('a');

Let’s query the rows from the table:

SELECT * FROM test_last_insert_id;

Output:

+----+---+
| id | s |
+----+---+
|  1 | a |
+----+---+

Let’s use the LAST_INSERT_ID() function to get the auto-increment value of the last insertion:

SELECT LAST_INSERT_ID();

Output:

+------------------+
| LAST_INSERT_ID() |
+------------------+
|                1 |
+------------------+

Let’s insert 2 rows for testing again:

INSERT INTO test_last_insert_id (s) VALUES ('b'), ('c');

Let’s query the data in the table:

SELECT * FROM test_last_insert_id;

Output:

+----+---+
| id | s |
+----+---+
|  1 | a |
|  2 | b |
|  3 | c |
+----+---+

Let’s use the LAST_INSERT_ID() function to get the the auto-increment value of the last insertion:

SELECT LAST_INSERT_ID();

Output:

+------------------+
| LAST_INSERT_ID() |
+------------------+
|                2 |
+------------------+

Here, you might ask why the result is 2 instead of 3? This is because, if multiple rows are inserted using a single INSERT statement, only the value generated for the first inserted row is returned.

Now, let’s give an argument to the LAST_INSERT_ID() function:

SELECT LAST_INSERT_ID(10);

Output:

+--------------------+
| LAST_INSERT_ID(10) |
+--------------------+
|                 10 |
+--------------------+

Let’s call the LAST_INSERT_ID() function:

SELECT LAST_INSERT_ID();

Output:

+------------------+
| LAST_INSERT_ID() |
+------------------+
|               10 |
+------------------+

At this point, the LAST_INSERT_ID() function returns the parameters from the last call.

Let’s insert 1 row again for testing:

INSERT INTO test_last_insert_id (s) VALUES ('d');

Let’s call the LAST_INSERT_ID() function:

SELECT LAST_INSERT_ID();

Output:

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

At this point, the LAST_INSERT_ID() function returns the last ID of the AUTO_INCREMENT column.

Conclusion

In MariaDB, LAST_INSERT_ID() is a built-in function that returns the value generated by the AUTO_INCREMENT column for the last insertion.