前段時(shí)間和滴滴的一位同學(xué)聊到 insert ... on duplicate key update 插入一條記錄成功后，影響行數(shù)為 2 意味著什么?

以前沒有深挖過這里面的細(xì)節(jié)，最近幾天抽空翻了翻源碼，可以來(lái)扒一扒這背后的細(xì)節(jié)了。對(duì)了，insert ... on duplicate key update 還有個(gè)兄弟叫 replace into，一起帶飛吧。

為了方便描述，本文后面會(huì)用 insert duplicate 表示 insert ... on duplicate key update。

本文內(nèi)容基于 MySQL 5.7.35 源碼。

1、 準(zhǔn)備工作

示例表結(jié)構(gòu)及插入初始化數(shù)據(jù) SQL 如下：

CREATE TABLE `t_insert` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `i1` int(11) NOT NULL DEFAULT '0',
  `i2` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_i1` (`i1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into t_insert(i1, i2) values
(101, 201),
(102, 202),
(103, 203),
(104, 204),
(105, 205)

2、先說結(jié)論

insert ... on duplicate key update 和 replace into 執(zhí)行成功之后返回的影響行數(shù)，是個(gè)比較小的主題，我們先說結(jié)論，然后再分析這兩種 SQL 執(zhí)行過程中計(jì)算影響行數(shù)的邏輯。

對(duì)執(zhí)行過程細(xì)節(jié)不感興趣的朋友，直接看本小節(jié)就好，可以不需要看第 3 小節(jié)的執(zhí)行過程分析了。

在源碼實(shí)現(xiàn)中，批量插入和單條插入記錄沒什么區(qū)別，批量插入實(shí)際上是循環(huán)執(zhí)行單條插入。所以，結(jié)論和執(zhí)行過程分析兩小節(jié)，都基于插入單條記錄進(jìn)行分析。

（1） insert ... on duplicate key update

insert duplicate 語(yǔ)句，插入一條記錄，影響行數(shù)可能有 3 種取值：0、1、2，影響行數(shù) = 插入行數(shù) + 更新行數(shù)。

影響行數(shù) = 1，表示插入記錄和表中記錄不存在主鍵或唯一索引沖突，插入操作可以直接成功。影響行數(shù) = 插入行數(shù)(1) + 更新行數(shù)(0) = 1。

影響行數(shù) = 0，表示插入記錄和表中記錄存在主鍵或唯一索引沖突，并且 insert duplicate 語(yǔ)句 update 字段列表中每個(gè)字段的字段值和沖突記錄中對(duì)應(yīng)的字段值一樣。

update 字段列表

以 t_insert 表為例，i1 字段上有唯一索引，表中記錄如下：

示例 SQL 如下：

insert into t_insert(i1, i2)
values (105, 999)
on duplicate key update i2 = 205

示例 SQL 中，update i2 字段值和表中 i1 = 105 的記錄的 i2 字段值一樣。示例 SQL 既不會(huì)更新表中記錄，也不會(huì)往表中插入記錄。影響行數(shù) = 插入行數(shù)(0) + 更新行數(shù)(0) = 0。

影響行數(shù) = 2，表示插入記錄和表中記錄存在主鍵或唯一索引沖突，但是 insert duplicate 語(yǔ)句 update 字段列表中的字段值和沖突記錄中的字段值不一樣，插入語(yǔ)句會(huì)更新表中沖突的第 1 條記錄。

因?yàn)楸碇兄麈I + 唯一索引可能存在多個(gè)，插入一條記錄，該記錄中的多個(gè)字段可能和多條不同記錄存在沖突，這種情況下，insert duplicate 只會(huì)更新沖突的第 1 條記錄。

以 t_insert 表為例，i1 字段上有唯一索引，表中記錄如下：

示例 SQL 如下：

-- i2 = 999 也可以寫成 i2 = values(i2)
insert into t_insert(i1, i2)
values (105, 999)
on duplicate key update i2 = 999

示例 SQL 中，update 字段列表中的 i2 字段值和表中 i1 = 105 的記錄的 i2 字段值(205)不一樣。

SQL 執(zhí)行過程中，會(huì)把 i1 = 105 的記錄中的 i2 字段值更新為 999，執(zhí)行結(jié)果為插入成功。插入行數(shù)加 1，但這個(gè)插入成功實(shí)際上是修改了表中已有記錄，修改行數(shù)也要加 1。影響行數(shù) = 插入行數(shù)(1) + 更新行數(shù)(1) = 2。

（2） replace into

replace into 語(yǔ)句，插入一條記錄，影響行數(shù)可能的取值有兩種：1、N(大于 1)。影響行數(shù) = 插入行數(shù) + 刪除行數(shù)。

影響行數(shù) = 1，表示插入記錄和表中記錄不存在主鍵或唯一索引沖突，插入操作可以直接成功。影響行數(shù) = 插入行數(shù)(1) + 刪除行數(shù)(0) = 1。

影響行數(shù) = N，表示插入記錄和表中的 N - 1 條記錄存在主鍵或唯一索引沖突，插入成功之前，會(huì)刪除這 N - 1 條沖突記錄。影響行數(shù) = 插入行數(shù)(1) + 刪除行數(shù)(N - 1) = N。

主鍵和唯一索引中都不允許存在重復(fù)記錄，為什么 replace into 語(yǔ)句插入一條記錄會(huì)和表中多條記錄存在沖突?

因?yàn)橐粋€(gè)表中，主鍵 + 唯一索引可能有多個(gè)，插入記錄中不同字段可能會(huì)和不同的記錄產(chǎn)生沖突。

以 t_insert 表為例，id 為主鍵字段，i1 字段上有唯一索引。t_insert 表中記錄如下：

示例 SQL 如下：

replace into t_insert(id, i1, i2)
values (4, 105, 888)

示例 SQL 中，待插入記錄的 id = 4，和主鍵沖突;待插入記錄的 i1 = 105，和 i1 字段上的唯一索引沖突。

replace into 語(yǔ)句執(zhí)行過程中，會(huì)刪除 id = 4 和 i1 = 105 的兩條記錄，插入 id = 4、i1 = 105、i2 = 888 這條記錄。

也就是先刪除 2 條記錄，再插入 1 條記錄，影響行數(shù) = 插入行數(shù)(1) + 刪除行數(shù)(2) = 3。

插入之后表中數(shù)據(jù)如下：

3、 執(zhí)行過程分析

（1） insert ... on duplicate key update

insert duplicate 語(yǔ)句是 MySQL 對(duì) SQL 標(biāo)準(zhǔn)的擴(kuò)展，它有 2 種行為：

• 如果插入記錄和表中記錄不存在主鍵或唯一索引沖突，它和普通插入語(yǔ)句一樣。
• 如果插入記錄和表中記錄存在主鍵或唯一索引沖突，它不會(huì)插入失敗，而是會(huì)用 update 字段列表中的字段值更新沖突記錄對(duì)應(yīng)的字段。

update 字段列表

insert duplicate 語(yǔ)句的影響行數(shù)，保存在 Statistics 類的實(shí)例屬性 copied 和 updated 中，計(jì)算公式：影響行數(shù) = copied + updated。

copied 表示插入行數(shù)，updated 表示更新行數(shù)。

接下來(lái)，我們來(lái)看看 insert duplicate 語(yǔ)句的執(zhí)行過程。

insert duplicate 執(zhí)行流程圖

第 1 步，調(diào)用插入記錄方法，如果插入成功，插入操作主流程就完成了，不需要執(zhí)行第 2 ~ 4 步。影響行數(shù) = copied(1) + updated(0) = 1。

第 2 步，如果因?yàn)橹麈I或唯一索引沖突導(dǎo)致插入失敗，MySQL 會(huì)找到是因?yàn)槟囊粋€(gè)索引沖突造成的，然后構(gòu)造由這個(gè)索引的所有字段組成的查詢條件，去存儲(chǔ)引擎讀取沖突的記錄，讀取出來(lái)的這條記錄叫作舊記錄。

第 3 步，用 insert duplicate 語(yǔ)句 update 字段列表中的字段值替換舊記錄中對(duì)應(yīng)字段的值后得到新記錄。

第 4 步，判斷新記錄和舊記錄的內(nèi)容是否完全一樣。

如果完全一樣，就不需要進(jìn)行更新操作，影響行數(shù) = copied(0) + updated(0) = 0。

如果不完全一樣，調(diào)用更新記錄方法，把新記錄各字段的值更新到表中，影響行數(shù) = copied(1) + updated(1) = 2。

有一點(diǎn)需要注意，如果待插入記錄和表中多條記錄存在主鍵或唯一索引沖突，insert duplicate 只會(huì)更新沖突的第 1 條記錄。哪個(gè)索引報(bào)記錄沖突，就更新這個(gè)索引中沖突的這條記錄。

（2） replace into

replace into 語(yǔ)句也是對(duì)標(biāo)準(zhǔn) SQL 的擴(kuò)展，它也有 2 種行為：

• 如果插入記錄和表中記錄不存在主鍵或唯一索引沖突，它和普通插入語(yǔ)句一樣。
• 如果插入記錄和表中記錄存在主鍵或唯一索引沖突，它會(huì)先刪除表中的沖突記錄，然后插入新記錄，這很符合 replace into 語(yǔ)句替換的語(yǔ)義。

除了先刪除再插入，還有另一種方式：用 replace into 語(yǔ)句 values() 中各字段的值更新表中的沖突記錄。不過，要使用這種方式，需要滿足一些條件，后面會(huì)詳細(xì)說。

replace into 語(yǔ)句的影響行數(shù)，保存在 Statistics 類的實(shí)例屬性 copied 和 deleted 中，計(jì)算公式：影響行數(shù) = copied + deleted。

copied 表示插入行數(shù)，deleted 表示刪除行數(shù)。

接下來(lái)，我們來(lái)看一下 replace into 語(yǔ)句的執(zhí)行過程：

replace into 執(zhí)行流程圖

第 1 步，調(diào)用插入記錄方法，如果插入成功，插入操作主流程就完成了，不需要執(zhí)行第 2 ~ 3 步。影響行數(shù) = copied(1) + deleted(0) = 1。

這一步和 insert duplicate 語(yǔ)句是一樣的，因?yàn)樗鼈儌z在這一步執(zhí)行的是同一行代碼，兄弟倆還沒有分家。

第 2 步，如果因?yàn)橹麈I或唯一索引沖突導(dǎo)致插入失敗，MySQL 會(huì)找到是因?yàn)槟囊粋€(gè)索引沖突造成的，然后構(gòu)造由這個(gè)索引的所有字段組成的查詢條件，從存儲(chǔ)引擎讀取沖突的記錄，讀取出來(lái)的這條記錄叫作舊記錄。

舊記錄用于第 3 步中刪除沖突記錄，以及判斷需要把插入記錄中的哪些字段更新到表中。

這一步和 insert duplicate 語(yǔ)句也是一樣的，因?yàn)樵谶@一步它們執(zhí)行的是同一段代碼，兄弟倆還沒有分家。

第 3 步，從這一步開始，replace into 和 insert duplicate 的邏輯就不一樣了。

在這一步，MySQL 會(huì)根據(jù)一些條件判斷是用更新舊記錄，還是刪除舊記錄，插入新記錄的方式來(lái)實(shí)現(xiàn) replace into 操作。

使用更新舊記錄方式，如果能夠使用這種方式實(shí)現(xiàn) replace into，說明插入記錄只和表中的一條記錄沖突，把待插入記錄各字段的值更新到舊記錄中，增加 deleted 計(jì)數(shù)，replace into 主流程就完成了。

因?yàn)?replace into 的語(yǔ)義是替換，也就是刪除舊記錄，插入新記錄，所以，雖然這里用的是更新舊記錄的方式，但計(jì)數(shù)還是用了 deleted 而不是 updated。

使用刪除舊記錄，插入新記錄方式，第 1 ~ 3 步是一個(gè)循環(huán)，在第 3 步會(huì)直接把沖突的第一條記錄刪除，然后再回到第 1 步執(zhí)行插入操作，循環(huán)執(zhí)行第 1~ 3 步，直到刪除了所有沖突記錄之后，插入才能夠成功。

如果多次執(zhí)行第 3 步，每次執(zhí)行時(shí)，deleted 計(jì)數(shù)都會(huì)加 1。

第 4 步，增加 copied 計(jì)數(shù)，copied 值由 0 變?yōu)?1。

如果第 3 步使用更新舊記錄方式實(shí)現(xiàn)，影響行數(shù) = copied(1) + deleted(1) = 2。

如果第 3 步使用刪除舊記錄，插入新記錄方式實(shí)現(xiàn)，第 3 步有可能會(huì)多次執(zhí)行，執(zhí)行幾次，deleted 值就是幾，影響行數(shù) = copied(1) + deleted(N) = 1 + N。

其中，N 表示第 3 步的執(zhí)行次數(shù)。

執(zhí)行流程中還有一個(gè)邏輯沒有說，就是第 3 步中，怎么決定使用更新舊記錄方式還是刪除舊記錄，插入新記錄方式。

使用更新舊記錄方式，需要同時(shí)滿足 3 個(gè)條件：

條件 1，第 2 步中報(bào)記錄沖突的那個(gè)索引是表中最后創(chuàng)建的唯一索引(也可能是主鍵)。

條件 2，表中的所有字段，都沒有被其它表的字段作為外鍵約束。

條件 3，表上沒有定義過刪除觸發(fā)器。

外鍵約束和刪除觸發(fā)器都很少使用，不展開講了。

4、 總結(jié)

先說結(jié)論小節(jié)，先介紹了 insert ... on duplicate key update 語(yǔ)句執(zhí)行成功之后，影響行數(shù)可能的 3 種取值：0、1、2，以及對(duì)每一種取值進(jìn)行了比較詳細(xì)的說明。

然后介紹了 replace into 語(yǔ)句執(zhí)行成功之后，影響行數(shù)可能的 2 種取值：1、N(大于 1 的整數(shù))，以及對(duì)這兩種取值進(jìn)行了比較詳細(xì)的說明。

執(zhí)行過程分析小節(jié)，詳細(xì)分析了 insert ... on duplicate key update 語(yǔ)句、replace into 語(yǔ)句的執(zhí)行過程。

本文轉(zhuǎn)載自微信公眾號(hào)「一樹一溪」，可以通過以下二維碼關(guān)注。轉(zhuǎn)載本文請(qǐng)聯(lián)系一樹一溪公眾號(hào)。