以前没有深挖过这里面的细节，最近几天抽空翻了翻源码，可以来扒一扒这背后的细节了。对了，insert ... on duplicate key update 还有个兄弟叫 replace into，一起带飞吧。
为了方便描述，本文后面会用 insert duplicate 表示 insert ... on duplicate key update。
本文内容基于 MySQL 5.7.35 源码。
示例表构造及插入初始化数据 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. 先说结论
insert ... on duplicate key update 和 replace into 实行成功之后返回的影响行数，是个比较小的主题，我们先说结论，然后再剖析这两种 SQL 实行过程中计算影响行数的逻辑。
对实行过程细节不感兴趣的朋友，直接看本小节就好，可以不须要看第 3 小节的实行过程剖析了。
在源码实现中，批量插入和单条插入记录没什么差异，批量插入实际上是循环实行单条插入。以是，结论和实行过程剖析两小节，都基于插入单条记录进行剖析。
2.1 insert ... on duplicate key updateinsert duplicate 语句，插入一条记录，影响行数可能有 3 种取值：0、1、2，影响行数 = 插入行数 + 更新行数。
影响行数 = 1，表示插入记录和表中记录不存在主键或唯一索引冲突，插入操作可以直接成功。影响行数 = 插入行数(1) + 更新行数(0) = 1。
影响行数 = 0，表示插入记录和表中记录存在主键或唯一索引冲突，并且 insert duplicate 语句 update 字段列表中每个字段的字段值和冲突记录中对应的字段值一样。
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 既不会更新表中记录，也不会往表中插入记录。影响行数 = 插入行数(0) + 更新行数(0) = 0。
影响行数 = 2，表示插入记录和表中记录存在主键或唯一索引冲突，但是 insert duplicate 语句 update 字段列表中的字段值和冲突记录中的字段值不一样，插入语句会更新表中冲突的第 1 条记录。
由于表中主键 + 唯一索引可能存在多个，插入一条记录，该记录中的多个字段可能和多条不同记录存在冲突，这种情形下，insert duplicate 只会更新冲突的第 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 实行过程中，会把 i1 = 105 的记录中的 i2 字段值更新为 999，实行结果为插入成功。插入行数加 1，但这个插入成功实际上是修正了表中已有记录，修正行数也要加 1。影响行数 = 插入行数(1) + 更新行数(1) = 2。
2.2 replace intoreplace into 语句，插入一条记录，影响行数可能的取值有两种：1、N（大于 1）。影响行数 = 插入行数 + 删除行数。
影响行数 = 1，表示插入记录和表中记录不存在主键或唯一索引冲突，插入操作可以直接成功。影响行数 = 插入行数(1) + 删除行数(0) = 1。
影响行数 = N，表示插入记录和表中的 N - 1 条记录存在主键或唯一索引冲突，插入成功之前，会删除这 N - 1 条冲突记录。影响行数 = 插入行数(1) + 删除行数(N - 1) = N。
主键和唯一索引中都不许可存在重复记录，为什么 replace into 语句插入一条记录会和表中多条记录存在冲突？
由于一个表中，主键 + 唯一索引可能有多个，插入记录中不同字段可能会和不同的记录产生冲突。
以 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 语句实行过程中，会删除 id = 4 和 i1 = 105 的两条记录，插入 id = 4、i1 = 105、i2 = 888 这条记录。
也便是先删除 2 条记录，再插入 1 条记录，影响行数 = 插入行数(1) + 删除行数(2) = 3。
插入之后表中数据如下：
3. 实行过程剖析3.1 insert ... on duplicate key update
insert duplicate 语句是 MySQL 对 SQL 标准的扩展，它有 2 种行为：
如果插入记录和表中记录不存在主键或唯一索引冲突，它和普通插入语句一样。如果插入记录和表中记录存在主键或唯一索引冲突，它不会插入失落败，而是会用 update 字段列表中的字段值更新冲突记录对应的字段。update 字段列表
insert duplicate 语句的影响行数，保存在 Statistics 类的实例属性 copied 和 updated 中，打算公式：影响行数 = copied + updated。
copied 表示插入行数，updated 表示更新行数。
接下来，我们来看看 insert duplicate 语句的实行过程。
insert duplicate 实行流程图
第 1 步，调用插入记录方法，如果插入成功，插入操作主流程就完成了，不须要实行第 2 ~ 4 步。影响行数 = copied(1) + updated(0) = 1。
第 2 步，如果由于主键或唯一索引冲突导致插入失落败，MySQL 会找到是由于哪一个索引冲突造成的，然后布局由这个索引的所有字段组成的查询条件，去存储引擎读取冲突的记录，读取出来的这条记录叫作旧记录。
第 3 步，用 insert duplicate 语句 update 字段列表中的字段值更换旧记录中对应字段的值后得到新记录。
第 4 步，判断新记录和旧记录的内容是否完备一样。
如果完备一样，就不须要进行更新操作，影响行数 = copied(0) + updated(0) = 0。
如果不完备一样，调用更新记录方法，把新记录各字段的值更新到表中，影响行数 = copied(1) + updated(1) = 2。
有一点须要把稳，如果待插入记录和表中多条记录存在主键或唯一索引冲突，insert duplicate 只会更新冲突的第 1 条记录。哪个索引报记录冲突，就更新这个索引中冲突的这条记录。
3.2 replace intoreplace into 语句也是对标准 SQL 的扩展，它也有 2 种行为：
如果插入记录和表中记录不存在主键或唯一索引冲突，它和普通插入语句一样。如果插入记录和表中记录存在主键或唯一索引冲突，它会先删除表中的冲突记录，然后插入新记录，这很符合 replace into 语句更换的语义。除了先删除再插入，还有另一种办法：用 replace into 语句 values() 中各字段的值更新表中的冲突记录。不过，要利用这种办法，须要知足一些条件，后面会详细说。replace into 语句的影响行数，保存在 Statistics 类的实例属性 copied 和 deleted 中，打算公式：影响行数 = copied + deleted。
copied 表示插入行数，deleted 表示删除行数。
接下来，我们来看一下 replace into 语句的实行过程：
replace into 实行流程图
第 1 步，调用插入记录方法，如果插入成功，插入操作主流程就完成了，不须要实行第 2 ~ 3 步。影响行数 = copied(1) + deleted(0) = 1。
这一步和 insert duplicate 语句是一样的，由于它们俩在这一步实行的是同一行代码，兄弟俩还没有分家。
第 2 步，如果由于主键或唯一索引冲突导致插入失落败，MySQL 会找到是由于哪一个索引冲突造成的，然后布局由这个索引的所有字段组成的查询条件，从存储引擎读取冲突的记录，读取出来的这条记录叫作旧记录。
旧记录用于第 3 步中删除冲突记录，以及判断须要把插入记录中的哪些字段更新到表中。
这一步和 insert duplicate 语句也是一样的，由于在这一步它们实行的是同一段代码，兄弟俩还没有分家。
第 3 步，从这一步开始，replace into 和 insert duplicate 的逻辑就不一样了。
在这一步，MySQL 会根据一些条件判断是用更新旧记录，还是删除旧记录，插入新记录的办法来实现 replace into 操作。
利用更新旧记录办法，如果能够利用这种办法实现 replace into，解释插入记录只和表中的一条记录冲突，把待插入记录各字段的值更新到旧记录中，增加 deleted 计数，replace into 主流程就完成了。
由于 replace into 的语义是更换，也便是删除旧记录，插入新记录，以是，虽然这里用的是更新旧记录的办法，但计数还是用了 deleted 而不是 updated。
利用删除旧记录，插入新记录办法，第 1 ~ 3 步是一个循环，在第 3 步会直接把冲突的第一条记录删除，然后再回到第 1 步实行插入操作，循环实行第 1~ 3 步，直到删除了所有冲突记录之后，插入才能够成功。
如果多次实行第 3 步，每次实行时，deleted 计数都会加 1。
第 4 步，增加 copied 计数，copied 值由 0 变为 1。
如果第 3 步利用更新旧记录办法实现，影响行数 = copied(1) + deleted(1) = 2。
如果第 3 步利用删除旧记录，插入新记录办法实现，第 3 步有可能会多次实行，实行几次，deleted 值便是几，影响行数= copied(1) + deleted(N) = 1 + N。
个中，N 表示第 3 步的实行次数。
实行流程中还有一个逻辑没有说，便是第 3 步中，怎么决定利用更新旧记录办法还是删除旧记录，插入新记录办法。
利用更新旧记录办法，须要同时知足 3 个条件：
条件 1，第 2 步中报记录冲突的那个索引是表中末了创建的唯一索引（也可能是主键）。
条件 2，表中的所有字段，都没有被其它表的字段作为外键约束。
条件 3，表上没有定义过删除触发器。
外键约束和删除触发器都很少利用，不展开讲了。
4. 总结2. 先说结论小节，先先容了 insert ... on duplicate key update 语句实行成功之后，影响行数可能的 3 种取值：0、1、2，以及对每一种取值进行了比较详细的解释。
然后先容了 replace into 语句实行成功之后，影响行数可能的 2 种取值：1、N（大于 1 的整数），以及对这两种取值进行了比较详细的解释。
3. 实行过程剖析小节，详细剖析了 insert ... on duplicate key update 语句、replace into 语句的实行过程。
以上便是本文的全部内容了，如果本文对你有所帮助，还请帮忙 转发、点赞，感激 ^_^
