下面说说Insert...OnDuplicateKeyUpdate和ReplaceInto有什么区别？什么？我以前没有深入研究过这方面的细节。这几天抽空翻了一下源码，可以看看背后的细节。对了，insert...onduplicatekeyupdate也有个兄弟叫replaceinto，一起带上吧。为了描述方便，本文后面会使用insertduplicate来表示insert...onduplicatekeyupdate。本文内容基于MySQL5.7.35源码。1.准备示例表结构并插入初始化数据SQL如下：CREATETABLE`t_insert`(`id`int(10)unsignedNOTNULLAUTO_INCREMENT,`i1`int(11)NOTNULLDEFAULT'0',`i2`int(11)NOTNULLDEFAULT'0',PRIMARYKEY(`id`),UNIQUEKEY`idx_i1`(`i1`))ENGINE=InnoDBDEFAULTCHARSET=utf8;insertintot_insert(i1,i2)values(101,201),(102,202),(103,203),(104,204),(105,205)2.先说结论，insert执行成功后返回受影响的行数。..关于重复键的更新和替换，这是一个相对较小的话题。先说结论，再分析这两条SQL执行过程中计算受影响行数的逻辑。对执行过程细节不感兴趣的，直接看本节，不需要看第3节执行过程的分析。在源码实现上，batch没有区别插入和单个插入记录。批量插入实际上是循环中的单个插入。因此，结论和执行过程分析两段都是基于插入单条记录进行分析。(1)insert...onduplicatekeyupdateinsertduplicate语句，插入一条记录，受影响的行数可能有3个值：0、1、2，受影响的行数=插入行数+更新行数行。affectedrows=1，说明插入的记录与表中的记录没有主键或唯一索引冲突，可以直接插入成功。RowsAffected=RowsInserted(1)+RowsUpdated(0)=1.如果受影响的行数=0，则表示插入的记录与表中的记录之间存在主键或唯一索引冲突，并且insertduplicate语句的更新字段列表中每个字段的字段值与冲突记录中对应的字段值相同。更新字段列表以t_insert表为例，i1字段上有唯一索引，表中记录如下：示例SQL如下：insertintot_insert(i1,i2)values(105,999)onduplicatekeyupdatei2=205在示例SQL中，updatei2字段值与表中记录i1=105的i2字段值相同。示例SQL既不更新也不向表中插入记录。RowsAffected=RowsInserted(0)+RowsUpdated(0)=0。Affectedrows=2，说明插入的记录与表中的记录存在主键或唯一索引冲突，但表中的字段值插入重复语句的更新字段列表与冲突记录中的字段值不同，插入语句将更新表1记录中的冲突第一行。因为表中可能存在多个主键+唯一索引，在插入记录时，记录中的多个字段可能会与多条不同的记录发生冲突。在这种情况下，insertduplicate只会更新第一个冲突的记录。以t_insert表为例，i1字段上有唯一索引，表中记录如下：示例SQL如下：--i2=999也可以写成i2=values(i2)insertintot_insert(i1,i2)values(105,999)在onduplicatekeyupdatei2=999的示例SQL中，更新字段列表中的i2字段值与的i2字段值(205)不同i1=105表中的记录。SQL执行过程中，i1=105的记录中的i2字段值会更新为999，执行结果为插入成功。插入行数增加1，但插入成功实际上修改了表中已有的记录，修改的行数也增加1。RowsAffected=RowsInserted(1)+RowsUpdated(1)=2.(2)replaceintoreplaceinto语句插入一条记录，影响行数的可能值有两个：1、N（大于1）。受影响的行数=插入的行数+删除的行数。affectedrows=1，说明插入的记录与表中的记录没有主键或唯一索引冲突，可以直接插入成功。RowsAffected=RowsInserted(1)+RowsDeleted(0)=1。Affectedrows=N，说明插入的记录与表中的N-1条记录存在主键或唯一索引冲突。在插入成功之前，N-1条冲突的记录将被删除。RowsAffected=RowsInserted(1)+RowsDeleted(N-1)=N。主键和唯一索引中不允许重复记录。为什么replaceinto语句插入的记录与表中的多条记录冲突？因为在一张表中，可能有多个主键+唯一索引，插入的记录中的不同字段可能与不同的记录冲突。以t_insert表为例，id为主键字段，在i1字段上有唯一索引。t_insert表中的记录如下：示例SQL如下：replaceintot_insert(id,i1,i2)values(4,105,888)示例SQL中，要插入的记录的id=4，与主键冲突；要插入的记录的i1=105，与字段i1上的唯一索引冲突。replaceinto语句执行过程中，id=4，i1=105的两条记录会被删除，id=4，i1=105，i2=888的记录会被插入。即先删除2条记录，再插入1条记录，受影响的行数=插入行数（1）+删除行数（2）=3。插入后表中数据如下:3.执行过程分析(1)insert...onduplicatekeyupdateinsertduplicate语句是MySQL对SQL标准的扩展，它有两种行为：如果插入记录和表中的记录没有主键或唯一索引冲突，它与普通插入语句相同。如果插入的记录与表中的记录存在主键或唯一索引冲突，则不会插入失败，而是将冲突记录的对应字段更新为更新字段列表中的字段值。updatefieldlistinsertduplicate语句影响的行数保存在Statistics类的实例属性copyed和updated中，计算公式为：受影响的行数=copied+updated。copied表示插入的行数，updated表示更新的行数。接下来我们看一下insertduplicate语句的执行过程。insertduplicate执行流程图的第1步，调用insertrecord方法。如果插入成功，则插入操作的主要流程完成，不需要执行2-4步。Rowsaffected=copied(1)+updated(0)=1.Step2，如果插入失败是因为主键或者唯一索引冲突，MySQL会找出是哪个索引冲突导致的，然后构造一个由以下组成的查询条件该索引的所有字段，并从存储引擎中读取冲突的记录。取出的记录称为旧记录。第三步：用insertduplicate语句的更新字段列表中的字段值替换旧记录中对应的字段值，得到一条新记录。第四步，判断新记录的内容是否与旧记录完全相同。如果完全一样，则不需要进行update操作，受影响的行数=copied(0)+updated(0)=0。如果不完全一样，调用updaterecord方法更新的values新记录中的每个字段到表中，受影响的行数=复制（1）+更新（1）=2。需要注意的是，如果记录之间存在主键或唯一索引冲突inserted和表中的多条记录，insertduplicate只会更新第一条冲突的记录。无论哪个索引报告记录冲突，都更新索引中的冲突记录。(2)replaceintoreplaceinto语句也是标准SQL的扩展，它也有两种行为：如果插入的记录与表中的记录没有主键或唯一索引冲突，则等同于正常的插入语句。如果插入的记录与表中的记录有主键或唯一索引冲突，会先删除表中的冲突记录，然后再插入新的记录，这与replace的替换语义非常一致成声明。除了先删除再插入，还有一种方式：使用replaceinto语句values()中各字段的值来更新表中的冲突记录。但是，要使用这种方法，需要满足一些条件，后面会详细介绍。replaceinto语句影响的行数存储在Statistics类的实例属性copied和deleted中，计算公式为：受影响的行数=copied+deleted。copied表示插入的行数，deleted表示删除的行数。接下来我们看一下replaceinto语句的执行过程：replaceinto执行流程图的第1步，调用insertrecord方法。如果插入成功，则插入操作的主要流程完成，不需要执行2-3步。Rowsaffected=copied(1)+deleted(0)=1。这一步和insertduplicate语句一样，因为他们在这一步都执行了同一行代码，两兄弟还没有分开。Step2，如果因为主键或者唯一索引冲突导致插入失败，MySQL会找出是哪个索引冲突导致的，然后构造一个由该索引的所有字段组成的查询条件，从存储引擎中读取冲突的记录，而读取出来的记录称为旧记录。旧记录用于删除步骤3中的冲突记录，并确定插入记录中的哪些字段需要更新到表中。这一步也和insertduplicate语句一样，因为在这一步他们执行的是同一段代码，两兄弟还没有分开。第三步，从这一步开始，replaceinto和insertduplicate的逻辑是不一样的。在这一步中，MySQL会根据一些条件判断是否更新旧记录、删除旧记录、插入新记录来实现replaceinto操作。使用更新旧记录的方法。如果能用这个方法实现replaceinto，说明插入的记录只和表中的一条记录冲突。将要插入的记录的各个字段的值更新到旧记录中，增加删除的计数，替换到主进程中就完成了。因为replaceinto的语义是replace，即删除旧记录插入新记录，所以这里虽然使用了updateoldrecord的方法，但是count还是使用了deleted而不是updated。使用删除旧记录和插入新记录的方法。步骤1到3是一个循环。第3步，直接删除第一条冲突记录，然后回到第1步进行插入操作，依次执行第1~3步，直到所有冲突记录都被删除，才能插入成功。如果多次执行第3步，则每次执行删除的计数都会加1。第4步，增加复制计数，复制值从0变为1。如果第3步是通过更新旧记录实现的，则受影响的行数=复制（1）+删除（1）=2。如果第3步是通过删除旧记录和插入新记录来实现，步骤3可能会执行多次，deleted的值为多少次，受影响的行数=copied(1)+deleted(N)=1+N。其中，N表示第3步的执行次数。执行过程中还有一个逻辑没有提到，就是在第3步中，如何决定是使用更新旧记录还是删除旧记录插入新记录的方法.使用更新旧记录的方法，需要同时满足三个条件：条件1，步骤2中报告记录冲突的索引是表中最后创建的唯一索引（也可能是主键).条件2，表中所有字段不受其他表中字段作为外键的约束。情况3，表上没有定义删除触发器。外键约束和删除触发器很少用到，就不说了。4.总结先说结论部分，先介绍下insert...onduplicatekeyupdate语句执行成功后影响行数的三个可能取值：0、1、2、compare每个值都有详细的解释。然后，在replaceinto语句执行成功后，引入两个可能影响行数的值：1、N（大于1的整数），并对这两个值进行更详细的描述。在执行过程分析部分，详细分析了insert...onduplicatekeyupdate语句和replaceinto语句的执行过程。本文转载自微信公众号“一树一溪”，可通过以下二维码关注。转载本文请联系艺书艺熙公众号。
