《??MySQL自增ID，居然大部分人都搞錯(cuò)了??？?》中的作業(yè)題，有少量答對(duì)的人，但原理講得不透，今天簡(jiǎn)單說(shuō)下作業(yè)題中的答案，以及相關(guān)知識(shí)點(diǎn)。

作業(yè)題是這樣的：

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");

執(zhí)行結(jié)束后id分別是：

(1, 'shenjian')
(111, '111')    // 允許指定值
(112, 'abc')    // 忽略NULL，從最大值開(kāi)始增
(222, '222')    // 允許指定值
(223, 'xyz')    // 忽略NULL，從最大值開(kāi)始增

如果繼續(xù)執(zhí)行以下語(yǔ)句：

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

請(qǐng)問(wèn)：

• 會(huì)不會(huì)執(zhí)行報(bào)錯(cuò)？
• 如果報(bào)錯(cuò)，為什么呢？
• 如果不報(bào)錯(cuò)，得到的數(shù)據(jù)是什么呢？

知其然，知其所以然。先系統(tǒng)性講講相關(guān)知識(shí)點(diǎn)，InnoDB自增鍵，插入，如何插入，以及插入之后發(fā)生了什么？

畫(huà)外音：本文均以MySQL5.6，InnoDB引擎為例。

?什么是插入？

MySQL有一系列的語(yǔ)句，可以往數(shù)據(jù)庫(kù)新增數(shù)據(jù)，稱(chēng)作泛插入語(yǔ)句(insert-like statement)。不同的插入方式，對(duì)自增鍵的影響是不一樣的。

畫(huà)外音：自增鍵，是指auto_increment，大家應(yīng)該都用得很多。

有幾種插入方式？?

更為細(xì)化后，總的來(lái)說(shuō)，插入方式分為三類(lèi)：

• 簡(jiǎn)單插入(simple insert)
• 批量插入(bulk insert)
• 混合插入(mixed-mode insert)

什么是簡(jiǎn)單插入(simple insert)？

?普通的insert/replace語(yǔ)句，不管是單條插入還是多條插入，都是簡(jiǎn)單插入。

畫(huà)外音：

• 不包含遞歸的子查詢(xún)；
• 不包含insert … on duplicate key update… ；

例如，對(duì)于作業(yè)題中的數(shù)據(jù)表：

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

都是簡(jiǎn)單插入。

簡(jiǎn)單插入的特點(diǎn)是，能夠提前知道被插入的行數(shù)。因此，這類(lèi)插入，在處理自增鍵時(shí)，是最容易的。

畫(huà)外音：很容易保證自增鍵連續(xù)性。

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

與簡(jiǎn)單插入相對(duì)，在插入時(shí)，不知道被插入的行數(shù)，是批量插入。

例如，對(duì)于作業(yè)題中的數(shù)據(jù)表：

insert into t1(name) select name from t2;

除此之外，像：

• replace … select …
• load data

都是批量插入。

由于不能夠提前知道多少行插入，在處理自增列時(shí)，每插入一行，才會(huì)賦值新的自增值。

畫(huà)外音，官網(wǎng)原文是：

InnoDB assigns new values for the AUTO_INCREMENT column one at a time as each
row is processed.

這里的潛臺(tái)詞是，在批量插入事務(wù)并發(fā)時(shí)，“可能”出現(xiàn)同一個(gè)事務(wù)的自增鍵不連續(xù)。

什么是混合插入(mixed-mode insert)？

例如，對(duì)于作業(yè)題中的SQL：

insert into t1(id, name) values 

(111,"111"),(NULL, "abc"),(222,"222"),(NULL,"xyz");

有些行插入時(shí)指定了自增鍵，無(wú)需數(shù)據(jù)庫(kù)生成；有些行插入時(shí)未指定自增鍵(NULL)，需要數(shù)據(jù)庫(kù)生成。

畫(huà)外音：具體走哪個(gè)分支，實(shí)際執(zhí)行時(shí)才知道。

以及，作業(yè)題中的如下SQL：

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

有些行插入實(shí)際上是修改，有些行插入實(shí)際上就是插入。?

畫(huà)外音：具體走哪個(gè)分支，也是實(shí)際執(zhí)行時(shí)才知道。

insert … on duplicate key update … 這種情況是最最復(fù)雜的，它可能導(dǎo)致，系統(tǒng)生成的自增值，在更新階段用不上。

畫(huà)外音，官網(wǎng)原文是：

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.

說(shuō)了這么多，對(duì)于作業(yè)題，id分別是：?

(1, 'shenjian')
(111, '111')    // 允許指定值
(112, 'abc')    // 忽略NULL，從最大值開(kāi)始增
(222, '222')    // 允許指定值
(223, 'xyz')    // 忽略NULL，從最大值開(kāi)始增

如果繼續(xù)執(zhí)行以下語(yǔ)句，答案會(huì)是什么呢？

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

答案如下：

對(duì)于：?

insert ("shenjian"),("aaa"),("bbb")

系統(tǒng)以為是批量插入，就生成了224，225，226三個(gè)連續(xù)自增鍵。

接下來(lái)：?

on duplicate key update ...

結(jié)果有一行插入沖突，實(shí)際是更新，導(dǎo)致224這個(gè)自增值并沒(méi)有用上，于是就出現(xiàn)了奇怪的“自增值不連續(xù)”的怪異現(xiàn)象。?