自增ID插入时，究竟发生了什么？

2022-06-21 09:08 浏览：295

《MySQL自增ID，居然大部分人都搞错了？》中的作业题，有少量答对的人，但原理讲得不透，今天简单说下作业题中的答案，以及相关知识点。

作业题是这样的：

drop table t1;
create table t1(
    id int not null auto_increment,
    name varchar(10) unique,
    count int default 0,
    primary key(id),
    index(name)
)engine=innodb;

insert into t1(id, name) values(1, "shenjian");

insert into t1(id, name) values 
(111, "111"),(NULL, "abc"),(222, "222"),(NULL,"xyz");

执行结束后id分别是：

(1, 'shenjian')
(111, '111')    // 允许指定值
(112, 'abc')    // 忽略NULL，从最大值开始增
(222, '222')    // 允许指定值
(223, 'xyz')    // 忽略NULL，从最大值开始增

如果继续执行以下语句：

insert into t1(name)values("shenjian"),("aaa"),("bbb")
on duplicate key update count=100;

请问：

会不会执行报错？
如果报错，为什么呢？
如果不报错，得到的数据是什么呢？

知其然，知其所以然。先系统性讲讲相关知识点，InnoDB自增键，插入，如何插入，以及插入之后发生了什么？

画外音：本文均以MySQL5.6，InnoDB引擎为例。

什么是插入？

MySQL有一系列的语句，可以往数据库新增数据，称作泛插入语句(insert-like statement)。不同的插入方式，对自增键的影响是不一样的。

画外音：自增键，是指auto_increment，大家应该都用得很多。

有几种插入方式？

更为细化后，总的来说，插入方式分为三类：

简单插入(simple insert)
批量插入(bulk insert)
混合插入(mixed-mode insert)

什么是简单插入(simple insert)？

普通的insert/replace语句，不管是单条插入还是多条插入，都是简单插入。

画外音：

不包含递归的子查询；
不包含insert … on duplicate key update… ；

例如，对于作业题中的数据表：

insert into t1(id, name) values(1,"shenjian");
insert into t1(name) values("zhangsan"),("lisi"),("ww");

都是简单插入。

简单插入的特点是，能够提前知道被插入的行数。因此，这类插入，在处理自增键时，是最容易的。

画外音：很容易保证自增键连续性。

什么是批量插入(bulk insert)？

与简单插入相对，在插入时，不知道被插入的行数，是批量插入。

例如，对于作业题中的数据表：

insert into t1(name) select name from t2;

除此之外，像：

replace … select …
load data

都是批量插入。

由于不能够提前知道多少行插入，在处理自增列时，每插入一行，才会赋值新的自增值。

画外音，官网原文是：

InnoDB assigns new values for the AUTO_INCREMENT column one at a time as each
row is processed.
这里的潜台词是，在批量插入事务并发时，“可能”出现同一个事务的自增键不连续。
什么是混合插入(mixed-mode insert)？
例如，对于作业题中的SQL：
insert into t1(id, name) values 

(111,"111"),(NULL, "abc"),(222,"222"),(NULL,"xyz");
有些行插入时指定了自增键，无需数据库生成；有些行插入时未指定自增键(NULL)，需要数据库生成。
画外音：具体走哪个分支，实际执行时才知道。
以及，作业题中的如下SQL：
insert into t1(name) values("shenjian"),("aaa"),("bbb")
on duplicate key update count=100;
有些行插入实际上是修改，有些行插入实际上就是插入。
画外音：具体走哪个分支，也是实际执行时才知道。
insert … on duplicate key update … 这种情况是最最复杂的，它可能导致，系统生成的自增值，在更新阶段用不上。
画外音，官网原文是：
an INSERT followed by a UPDATE, where the allocated value for the
AUTO_INCREMENT column may or may not be used during the update phase.
说了这么多，对于作业题，id分别是：
(1, 'shenjian')
(111, '111')    // 允许指定值
(112, 'abc')    // 忽略NULL，从最大值开始增
(222, '222')    // 允许指定值
(223, 'xyz')    // 忽略NULL，从最大值开始增
如果继续执行以下语句，答案会是什么呢？
insert into t1(name)values("shenjian"),("aaa"),("bbb")
on duplicate key update count=100;
答案如下：
对于：
insert ("shenjian"),("aaa"),("bbb")
系统以为是批量插入，就生成了224，225，226三个连续自增键。
接下来：
on duplicate key update ...
结果有一行插入冲突，实际是更新，导致224这个自增值并没有用上，于是就出现了奇怪的“自增值不连续”的怪异现象。