LAST_INSERT_ID()

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.

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:

1. mysql> CREATE TABLE t (id INT AUTO_INCREMENT NOT NULL PRIMARY KEY, name VARCHAR(10) NOT NULL);
2. mysql> INSERT INTO t VALUES (NULL, 'Bob');
3. mysql> SELECT * FROM t;
4. +----+------+
5. | id | name |
6. +----+------+
7. |  1 | Bob  |
8. +----+------+

10. mysql> SELECT LAST_INSERT_ID();
11. +------------------+
12. | LAST_INSERT_ID() |
13. +------------------+
14. |                1 |
15. +------------------+

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

19. mysql> SELECT * FROM t;
20. +----+------+
21. | id | name |
22. +----+------+
23. |  1 | Bob  |
24. |  2 | Mary |
25. |  3 | Jane |
26. |  4 | Lisa |
27. +----+------+

29. mysql> SELECT LAST_INSERT_ID();
30. +------------------+
31. | LAST_INSERT_ID() |
32. +------------------+
33. |                2 |
34. +------------------+

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

1. LAST_INSERT_ID(), LAST_INSERT_ID(expr)

Examples

1. create table t1(a int auto_increment primary key);
2. insert into t1 values();
3. mysql> select last_insert_id();
4. +------------------+
5. | last_insert_id() |
6. +------------------+
7. |                1 |
8. +------------------+
9. 1 row in set (0.02 sec)

11. insert into t1 values(11);
12. insert into t1 values();
13. mysql> select last_insert_id();
14. +------------------+
15. | last_insert_id() |
16. +------------------+
17. |               12 |
18. +------------------+
19. 1 row in set (0.02 sec)

21. insert into t1 values(null);
22. mysql> select last_insert_id();
23. +------------------+
24. | last_insert_id() |
25. +------------------+
26. |               13 |
27. +------------------+
28. 1 row in set (0.02 sec)

30. create table t2(a int auto_increment primary key);
31. insert into t2 values();
32. mysql> select last_insert_id();
33. +------------------+
34. | last_insert_id() |
35. +------------------+
36. |                1 |
37. +------------------+
38. 1 row in set (0.02 sec)

40. insert into t2 values(100);
41. insert into t2 values();
42. mysql> select last_insert_id();
43. +------------------+
44. | last_insert_id() |
45. +------------------+
46. |              101 |
47. +------------------+
48. 1 row in set (0.02 sec)