LAST_INSERT_ID()

The LAST_INSERT_ID() function returns the AUTO_INCREMENT id of the last row that has been inserted or updated in a table.

Description

The LAST_INSERT_ID() function returns the AUTO_INCREMENT id of the last row that has been inserted or updated in a table.

When the argument is NULL, LAST_INSERT_ID() returns a value representing the first automatically generated value successfully inserted for an AUTO_INCREMENT column as a result of the most recently executed INSERT statement; if you haven’t inserted a column before, then the return value start at 1, and if you’ve inserted a column before, then the return value of the AUTO_INCREMENT column increases by 1.

The value of LAST_INSERT_ID() remains unchanged if no rows are successfully inserted.

LAST_INSERT_ID() only reflects values generated for a user-visible AUTO_INCREMENT column. When a table has no user-defined primary key, MatrixOne appends an internal hidden primary key (column name __mo_fake_pk_col, also used by FULLTEXT index tables) and maintains its value through a separate allocator. The value of this hidden primary key never surfaces in LAST_INSERT_ID(). An INSERT that supplies an explicit value for the user AUTO_INCREMENT column therefore generates only the hidden key, and LAST_INSERT_ID() keeps the value from the last statement that generated a user-visible one. A user-visible AUTO_INCREMENT column that merely happens to be named __mo_fake_pk_col is not hidden and still publishes its value normally.

In MySQL, if you insert multiple rows using a single INSERT statement, LAST_INSERT_ID() returns the value generated for the first inserted row only. For example:

mysql> CREATE TABLE t (id INT AUTO_INCREMENT NOT NULL PRIMARY KEY, name VARCHAR(10) NOT NULL);
mysql> INSERT INTO t VALUES (NULL, 'Bob');
mysql> SELECT * FROM t;
+----+------+
| id | name |
+----+------+
|  1 | Bob  |
+----+------+

mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                1 |
+------------------+

mysql> INSERT INTO t VALUES (NULL, 'Mary'), (NULL, 'Jane'), (NULL, 'Lisa');

mysql> SELECT * FROM t;
+----+------+
| id | name |
+----+------+
|  1 | Bob  |
|  2 | Mary |
|  3 | Jane |
|  4 | Lisa |
+----+------+

mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                2 |
+------------------+

But in MatrixOne, we have a different behavior; if you insert multiple rows using a single INSERT statement, LAST_INSERT_ID() returns the value generated for the last inserted row. Like the example above, when you execute INSERT INTO t VALUES (NULL, 'Mary'), (NULL, 'Jane'), (NULL, 'Lisa');, the LAST_INSERT_ID() will return 4.

Syntax

LAST_INSERT_ID(), LAST_INSERT_ID(expr)

Examples

create table t1(a int auto_increment primary key);
insert into t1 values();
mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|                1 |
+------------------+
1 row in set (0.02 sec)

insert into t1 values(11);
insert into t1 values();
mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|               12 |
+------------------+
1 row in set (0.02 sec)

insert into t1 values(null);
mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|               13 |
+------------------+
1 row in set (0.02 sec)

create table t2(a int auto_increment primary key);
insert into t2 values();
mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|                1 |
+------------------+
1 row in set (0.02 sec)

insert into t2 values(100);
insert into t2 values();
mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|              101 |
+------------------+
1 row in set (0.02 sec)

The example below exercises the hidden primary key rule. li_p1 declares the user-visible AUTO_INCREMENT column name __mo_fake_pk_col, so its generated value surfaces. li_p2 has no user-defined primary key and receives the hidden primary key; inserting an explicit id generates only the hidden key, so LAST_INSERT_ID() stays at 1. After a DELETE optimized to a truncate recreates li_p3, the hidden key still does not surface.

CREATE DATABASE IF NOT EXISTS last_insert_id_doc;
USE last_insert_id_doc;

CREATE TABLE li_p1(pk int primary key, `__mo_fake_pk_col` bigint unsigned auto_increment unique key);
INSERT INTO li_p1(pk) VALUES(10);
SELECT last_insert_id();
SELECT pk, `__mo_fake_pk_col` FROM li_p1 ORDER BY pk;

CREATE TABLE li_p2(id bigint auto_increment, v int, unique key uk(id));
INSERT INTO li_p2(v) VALUES(1);
SELECT last_insert_id();
INSERT INTO li_p2(id,v) VALUES(100,2);
SELECT last_insert_id();
SELECT id, v FROM li_p2 ORDER BY id;

SET delete_opt_to_truncate = 1;
CREATE TABLE li_p3(id bigint auto_increment, v int, unique key(id));
INSERT INTO li_p3(v) VALUES(1);
SELECT last_insert_id();
DELETE FROM li_p3;
INSERT INTO li_p3(id,v) VALUES(100,2);
SELECT last_insert_id();
SELECT id, v FROM li_p3 ORDER BY id;

DROP TABLE IF EXISTS li_p1;
DROP TABLE IF EXISTS li_p2;
DROP TABLE IF EXISTS li_p3;
DROP DATABASE IF EXISTS last_insert_id_doc;