阅读 222

关于 MySQL insert 和自增 ID 的奇怪事件

你好,我是yes。

上周五,我的读者向我提了个问题,这个问题既熟悉又生僻,还是挺有意思的,所以我在这里分享一下。

首先有建立一张表

CREATE TABLE `t` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;
复制代码

表没什么花头,主键是 ID,然后是自增的。

此时执行一条插入语句:

insert into t (id,c,d) values (1,1,1),(null,2,2),(7,7,7),(null,4,4);
复制代码

语句中既有指定的 ID,又有让 MySQL 自己计算的自增 ID。

然后此时,再执行一条sql

insert into t (id,c,d) values (null,9,9);
复制代码

你觉得这条插入的 ID 应该是几?

你肯定以为是 9 ,实际上这条新纪录的 ID 是 10。

乍一看,不对啊,第一条插入语句的最后一个记录是 (null,4,4),它的 ID 是 8 ,那为什么紧接着的后面一个插入自增 ID 直接变到了 10 而不是 9 ?

为什么中间空了一个 ID?

讲实话,我也不知道,于是我去官网寻寻觅觅,果然皇天不负有心人,嘻嘻。

这种一条语句里面,即包含指定 ID 又有自增 ID 的叫做Mixed-mode inserts

然后关于自增 ID 的模式,又分为三种,我在之前的文章分析过:

所以默认值是 1。

然后默认值是 1 再加上这次的插入是Mixed-mode inserts,就发生了点火花。

简单的来了个机翻:

例外情况是“混合模式插入”,其中用户为多行“简单插入”中的某些(但不是全部)行提供 AUTO_INCREMENT 列的显式值。 对于此类插入,InnoDB 分配的自动增量值多于要插入的行数。 但是,所有自动分配的值都是连续生成的(因此高于)由最近执行的前一条语句生成的自动增量值。 “多余”的数字丢失

好了,破案了,官网上已经写的很清楚了,这样的插入在innodb_autoinc_lock_mode = 1的情况下,InnoDB 就是会分配多余插入行数的 ID 数,而多出来的 ID 就被丢弃了。

所以后面的插入看起来就和前面断掉了,搞得像一部分 ID 莫名其妙被吃了一样。

那为什么会 InnoDB 要这样实现呢?

如果想具体知道这个问题,那肯定得看源码了,那成本就太大了。

我盲猜猜,你一条插入里面即自己设定了 ID,又让 InnoDB 计算自增 ID,在代码实现上估计有点难度,想要做到完美的自增 ID 序列估计成本有点大。

简单来说就是得不偿失,自增 ID 这么多,少一两个不要紧,所以就简单实现,一次多准备点,这样插入就不会出错啦~

好了,官网链接:dev.mysql.com/doc/refman/…

这次短平快到此结束。


我是yes,从一点点到亿点点,我们下篇见。

文章分类
后端
文章标签