LAST_INSERT_ID()可信吗

导读

通常我们会在执行一次INSERT后，调用LAST_INSERT_ID()获取最新的自增ID，但这么做其实并不太可靠。

函数 LAST_INSERT_ID() 没有额外参数或表达式时，则返回一个无符号BIGINT，默认地，它返回最后一次对自增ID列INSERT后的值。

注意，对非自增ID列INSERT结束后，调用 LAST_INSERT_ID() 是没有作用的，例如：

[[email protected]]> create table tt (
`id` int(11) NOT NULL primary key,
`c1` int(10) unsigned NOT NULL
)engine=innodb;

[[email protected]]> insert into tt values(0,0);
Query OK, 1 row affected (0.01 sec)

[[email protected]]> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|                0 |
+------------------+

在应用程序中，不少开发者会习惯调用 LAST_INSERT_ID() 函数获取最后插入的自增值，但实际上这么做并不可靠，我们来看几个例子：

例1，插入失败时

[[email protected]]> CREATE TABLE `t` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c1` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

# 第一次插入，没问题
[[email protected]]> insert into t select 0,rand()*1024;
[[email protected]]> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|                1 |
+------------------+

# 第二次插入，也没问题
[[email protected]]> insert into t select 0,rand()*1024;
[[email protected]]> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|                2 |
+------------------+

# 第三次插入，故意制造一个重复主键，这次就不对了
[[email protected]]> insert into t values(0,rand()*1024), (3, rand()*1024);
ERROR 1062 (23000): Duplicate entry '3' for key 'PRIMARY'
[[email protected]]> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|                3 |
+------------------+

# 表中实际只有两条记录
[[email protected]]> select * from t;
+----+-----+
| id | c1  |
+----+-----+
|  1 | 784 |
|  2 | 574 |
+----+-----+
2 rows in set (0.00 sec)

例子2，同时多次插入时

多个insert时，返回第二个insert值，例如：

# 现在表里有3条记录
[[email protected]]> select * from t;
+----+-----+
| id | c1  |
+----+-----+
|  1 | 784 |
|  2 | 574 |
|  5 | 681 |
+----+-----+
3 rows in set (0.00 sec)

# 一次性再插入3条记录
[[email protected]]> insert into t values 
(0,rand()*1024), (0, rand()*1024), (0,rand()*1024);

# 获取到的 last_insert_id() 值却是6，显然“不太符合预期”
[[email protected]]> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|                6 |
+------------------+

[[email protected]]> 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() 带有参数时

# 清空重来
[[email protected]]> truncate table t;

# 插入1条新记录
[[email protected]]> insert into t select 0,rand()*1024;

# 查看 last_insert_id(), 符合预期
[[email protected]]> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|                1 |
+------------------+

[[email protected]]> select * from t;
+----+-----+
| id | c1  |
+----+-----+
|  1 | 730 |
+----+-----+

# 调用 last_insert_id() 时增加表达式
[[email protected]]> select last_insert_id(id+2) from t;
+----------------------+
| last_insert_id(id+2) |
+----------------------+
|                    3 |
+----------------------+

# 再看 last_insert_id() 的值，好像“又不符合预期”了
[[email protected]]> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|                3 |
+------------------+

# 插入1条新纪录
[[email protected]]> insert into t select 0,rand()*1024;

# 再看 last_insert_id() 的值，好像“又回到正轨”了
[[email protected]]> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|                2 |
+------------------+

[[email protected]]> select * from t;
+----+-----+
| id | c1  |
+----+-----+
|  1 | 730 |
|  2 | 600 |
+----+-----+
2 rows in set (0.00 sec)

通过几个例子，我们能看到调用 last_insert_id() 函数想获取表中自增列最大值其实并不可靠，如果需要构建一个sequence表，最好还是每次都调用 max() 函数获取最大值才行。

附带MySQL版本信息：

[[email protected]]> \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