自增ID插入時，究竟發生了什么？
《??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這個自增值并沒有用上，于是就出現了奇怪的“自增值不連續”的怪異現象。?
