LAST_INSERT_ID()可信吗

来自:老叶茶馆(微信号:iMySQL_WX),作者:叶金荣

导读

通常我们会在执行一次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

推荐↓↓↓
数据库开发
上一篇:MySQL 只能做小项目?说几句公道话! 下一篇:MySQL意外查不到数据,真相令人惊掉下巴