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)