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;