mysql - 使用 auto_increment 时，如何获取新行的 ID？

Question

我的数据库有，

mysql> describe students;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(50) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+

我想插入 2 个学生 - 但我想立即知道他们的 ID

mysql> insert into students values (null, 'tom'), (null, 'larry');

如果插入实际上返回了 ID 的结果集，我会喜欢它......也就是......

mysql> select id from (insert into students values (null, 'tom'), (null, 'larry'));

嗯？

Answer 1

您可以使用LAST_INSERT_ID() 函数获取批处理的第一个AUTO INCREMENT 值，然后使用MAX 获取最大值.

LAST_INSERT_ID() (with no argument) returns a BIGINT (64-bit) value representing the first automatically generated value that was set for an AUTO_INCREMENT column by the most recently executed INSERT statement to affect such a column.

Reference

> INSERT INTO students VALUES (NULL, 'tom'), (NULL, 'larry');

> SELECT LAST_INSERT_ID() FROM students LIMI 1

| LAST_INSERT_ID() |--------------------|                1 |

> SELECT MAX(id) FROM students

| MAX(ID) |-----------|       2 |

> INSERT INTO students VALUES (NULL, 'billy'), (NULL, 'jane'), (NULL, 'fred');

> SELECT LAST_INSERT_ID() FROM students LIMIT 1

| LAST_INSERT_ID() |--------------------|                3 |

> SELECT MAX(id) FROM students

| MAX(ID) |-----------|       5 |