LAST_INSERT_ID()可信吗
导读
通常我们会在执行一次INSERT后，调用LAST_INSERT_ID()获取最新的自增ID，但这么做其实并不太可靠。
函数 LAST_INSERT_ID() 没有额外参数或表达式时，则返回一个无符号BIGINT，默认地，它返回最后一次对自增ID列INSERT后的值。
注意，对非自增ID列INSERT结束后，调用 LAST_INSERT_ID() 是没有作用的，例如：
[root@yejr.me]> create table tt (
`id` int(11) NOT NULL primary key,
`c1` int(10) unsigned NOT NULL
)engine=innodb;
[root@yejr.me]> insert into tt values(0,0);
Query OK, 1 row affected (0.01 sec)
[root@yejr.me]> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
| 0 |
+------------------+
在应用程序中，不少开发者会习惯调用 LAST_INSERT_ID() 函数获取最后插入的自增值，但实际上这么做并不可靠，我们来看几个例子：
例1，插入失败时
[root@yejr.me]> CREATE TABLE `t` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`c1` int(10) unsigned NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
# 第一次插入，没问题
[root@yejr.me]> insert into t select 0,rand()*1024;
[root@yejr.me]> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
| 1 |
+------------------+
# 第二次插入，也没问题
[root@yejr.me]> insert into t select 0,rand()*1024;
[root@yejr.me]> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
| 2 |
+------------------+
# 第三次插入，故意制造一个重复主键，这次就不对了
[root@yejr.me]> insert into t values(0,rand()*1024), (3, rand()*1024);
ERROR 1062 (23000): Duplicate entry '3' for key 'PRIMARY'
[root@yejr.me]> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
| 3 |
+------------------+
# 表中实际只有两条记录
[root@yejr.me]> select * from t;
+----+-----+
| id | c1 |
+----+-----+
| 1 | 784 |
| 2 | 574 |
+----+-----+
2 rows in set (0.00 sec)
例子2，同时多次插入时
多个insert时，返回第二个insert值，例如：
# 现在表里有3条记录
[root@yejr.me]> select * from t;
+----+-----+
| id | c1 |
+----+-----+
| 1 | 784 |
| 2 | 574 |
| 5 | 681 |
+----+-----+
3 rows in set (0.00 sec)
# 一次性再插入3条记录
[root@yejr.me]> insert into t values
(0,rand()*1024), (0, rand()*1024), (0,rand()*1024);
# 获取到的 last_insert_id() 值却是6，显然“不太符合预期”
[root@yejr.me]> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
| 6 |
+------------------+
[root@yejr.me]> select * from t;
+----+-----+
| id | c1 |
+----+-----+
| 1 | 784 |
| 2 | 574 |
| 5 | 681 |
| 6 | 841 |
| 7 | 112 |
| 8 | 87 |
+----+-----+
6 rows in set (0.00 sec)
例3，当 LAST_INSERT_ID() 带有参数时
# 清空重来
[root@yejr.me]> truncate table t;
# 插入1条新记录
[root@yejr.me]> insert into t select 0,rand()*1024;
# 查看 last_insert_id(), 符合预期
[root@yejr.me]> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
| 1 |
+------------------+
[root@yejr.me]> select * from t;
+----+-----+
| id | c1 |
+----+-----+
| 1 | 730 |
+----+-----+
# 调用 last_insert_id() 时增加表达式
[root@yejr.me]> select last_insert_id(id+2) from t;
+----------------------+
| last_insert_id(id+2) |
+----------------------+
| 3 |
+----------------------+
# 再看 last_insert_id() 的值，好像“又不符合预期”了
[root@yejr.me]> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
| 3 |
+------------------+
# 插入1条新纪录
[root@yejr.me]> insert into t select 0,rand()*1024;
# 再看 last_insert_id() 的值，好像“又回到正轨”了
[root@yejr.me]> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
| 2 |
+------------------+
[root@yejr.me]> select * from t;
+----+-----+
| id | c1 |
+----+-----+
| 1 | 730 |
| 2 | 600 |
+----+-----+
2 rows in set (0.00 sec)
通过几个例子，我们能看到调用 last_insert_id() 函数想获取表中自增列最大值其实并不可靠，如果需要构建一个sequence表，最好还是每次都调用 max() 函数获取最大值才行。
附带MySQL版本信息：
[root@yejr.me]> \s
...
Server version: 8.0.15 MySQL Community Server - GPL
...
参考资料
MySQL手册 https://dev.mysql.com/doc/refman/8.0/en/information-functions.html#function_last-insert-id