# **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:

```text
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**

```text
LAST_INSERT_ID(), LAST_INSERT_ID(expr)
```

## **Example**

```text
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)
```