前段时间和滴滴的一位同学聊到 insert ... on duplicate key update 插入一条记录成功后,影响行数为 2 意味着什么?
以前没有深挖过这里面的细节,最近几天抽空翻了翻源码,可以来扒一扒这背后的细节了。对了,insert ... on duplicate key update 还有个兄弟叫 replace into,一起带飞吧。
为了方便描述,本文后面会用 insert duplicate 表示 insert ... on duplicate key update。
本文内容基于 MySQL 5.7.35 源码。
1、 准备工作
示例表结构及插入初始化数据 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 小节的执行过程分析了。
在源码实现中,批量插入和单条插入记录没什么区别,批量插入实际上是循环执行单条插入。所以,结论和执行过程分析两小节,都基于插入单条记录进行分析。
(1) insert ... on duplicate key update
insert 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) replace into
replace 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、 执行过程分析
(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 条记录。哪个索引报记录冲突,就更新这个索引中冲突的这条记录。
(2) replace into
replace 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、 总结
先说结论小节,先介绍了 insert ... on duplicate key update 语句执行成功之后,影响行数可能的 3 种取值:0、1、2,以及对每一种取值进行了比较详细的说明。
然后介绍了 replace into 语句执行成功之后,影响行数可能的 2 种取值:1、N(大于 1 的整数),以及对这两种取值进行了比较详细的说明。
执行过程分析小节,详细分析了 insert ... on duplicate key update 语句、replace into 语句的执行过程。
本文转载自微信公众号「一树一溪」,可以通过以下二维码关注。转载本文请联系一树一溪公众号。
免责声明:本平台仅供信息发布交流之途,请谨慎判断信息真伪。如遇虚假诈骗信息,请立即举报
举报