LAST_INSERT_ID()

Grammar Description

If the table contains the self-increment field AUTO_INCREMENT, after inserting a record into the table, you can call LAST_INSERT_ID() to obtain the self-increment field value of the most recently inserted record.

If no parameters are inserted, LAST_INSERT_ID() returns a BIGINT UNSIGNED (64-bit) value that represents the first automatically generated value successfully inserted into the AUTO_INCREMENT column as the result of the most recently executed INSERT statement. The return value depends on the value of the previous AUTO_INCREMENT column. If you did not insert a column before, the return value starts at 1. If you inserted a column before, the return value of the AUTO_INCREMENT column is increased by 1.

If no parameter is inserted successfully, the value of LAST_INSERT_ID() remains unchanged.

In MySQL, if multiple rows are inserted using a single INSERT statement, LAST_INSERT_ID() returns only the value generated for the first inserted row. 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 different behaviors; if multiple rows are inserted using a single INSERT statement, LAST_INSERT_ID() returns the value generated for the last inserted row. As in the example above, LAST_INSERT_ID() returns 4 when you execute INSERT INTO t VALUES (NULL, 'Mary'), (NULL, 'Jane'), (NULL, 'Lisa');.

Grammar Structure

LAST_INSERT_ID(), LAST_INSERT_ID(expr)

Example

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)