duplicatekey是什么意思,duplicatekey的意思翻译、用法(update影响行数)

这篇文章给大家聊聊关于duplicatekey是什么意思,duplicatekey的意思翻译、用法，以及update影响行数对应的知识点，希望对各位有所帮助，不要忘了收藏本站哦。

前段时间和滴滴的一位同学聊到insert...onduplicatekeyupdate插入一条记录成功后，影响行数为2意味着什么？

以前没有深挖过这里面的细节，最近几天抽空翻了翻源码，可以来扒一扒这背后的细节了。对了，insert...onduplicatekeyupdate还有个兄弟叫replaceinto，一起带飞吧。

为了方便描述，本文后面会用insertduplicate表示insert...onduplicatekeyupdate。

示例表结构及插入初始化数据SQL如下：

CREATETABLE`t_insert`(\n`id`int(10)unsignedNOTNULLAUTO_INCREMENT,\n`i1`int(11)NOTNULLDEFAULT'0',\n`i2`int(11)NOTNULLDEFAULT'0',\nPRIMARYKEY(`id`),\nUNIQUEKEY`idx_i1`(`i1`)\n)ENGINE=InnoDBDEFAULTCHARSET=utf8;\n\ninsertintot_insert(i1,i2)values\n(101,201),\n(102,202),\n(103,203),\n(104,204),\n(105,205)\n2.先说结论

insert...onduplicatekeyupdate和replaceinto执行成功之后返回的影响行数，是个比较小的主题，我们先说结论，然后再分析这两种SQL执行过程中计算影响行数的逻辑。

对执行过程细节不感兴趣的朋友，直接看本小节就好，可以不需要看第3小节的执行过程分析了。

在源码实现中，批量插入和单条插入记录没什么区别，批量插入实际上是循环执行单条插入。所以，结论和执行过程分析两小节，都基于插入单条记录进行分析。

insertduplicate语句，插入一条记录，影响行数可能有3种取值：0、1、2，影响行数=插入行数+更新行数。

影响行数=1，表示插入记录和表中记录不存在主键或唯一索引冲突，插入操作可以直接成功。影响行数=插入行数(1)+更新行数(0)=1。

影响行数=0，表示插入记录和表中记录存在主键或唯一索引冲突，并且insertduplicate语句update字段列表中每个字段的字段值和冲突记录中对应的字段值一样。

以t_insert表为例，i1字段上有唯一索引，表中记录如下：

insertintot_insert(i1,i2)\nvalues(105,999)\nonduplicatekeyupdatei2=205\n

示例SQL中，updatei2字段值和表中i1=105的记录的i2字段值一样。示例SQL既不会更新表中记录，也不会往表中插入记录。影响行数=插入行数(0)+更新行数(0)=0。

影响行数=2，表示插入记录和表中记录存在主键或唯一索引冲突，但是insertduplicate语句update字段列表中的字段值和冲突记录中的字段值不一样，插入语句会更新表中冲突的第1条记录。

因为表中主键+唯一索引可能存在多个，插入一条记录，该记录中的多个字段可能和多条不同记录存在冲突，这种情况下，insertduplicate只会更新冲突的第1条记录。

以t_insert表为例，i1字段上有唯一索引，表中记录如下：

--i2=999也可以写成i2=values(i2)\ninsertintot_insert(i1,i2)\nvalues(105,999)\nonduplicatekeyupdatei2=999\n

示例SQL中，update字段列表中的i2字段值和表中i1=105的记录的i2字段值（205）不一样。

SQL执行过程中，会把i1=105的记录中的i2字段值更新为999，执行结果为插入成功。插入行数加1，但这个插入成功实际上是修改了表中已有记录，修改行数也要加1。影响行数=插入行数(1)+更新行数(1)=2。

replaceinto语句，插入一条记录，影响行数可能的取值有两种：1、N（大于1）。影响行数=插入行数+删除行数。

影响行数=1，表示插入记录和表中记录不存在主键或唯一索引冲突，插入操作可以直接成功。影响行数=插入行数(1)+删除行数(0)=1。

影响行数=N，表示插入记录和表中的N-1条记录存在主键或唯一索引冲突，插入成功之前，会删除这N-1条冲突记录。影响行数=插入行数(1)+删除行数(N-1)=N。

主键和唯一索引中都不允许存在重复记录，为什么replaceinto语句插入一条记录会和表中多条记录存在冲突？

因为一个表中，主键+唯一索引可能有多个，插入记录中不同字段可能会和不同的记录产生冲突。

以t_insert表为例，id为主键字段，i1字段上有唯一索引。t_insert表中记录如下：

replaceintot_insert(id,i1,i2)\nvalues(4,105,888)\n

示例SQL中，待插入记录的id=4，和主键冲突；待插入记录的i1=105，和i1字段上的唯一索引冲突。

replaceinto语句执行过程中，会删除id=4和i1=105的两条记录，插入id=4、i1=105、i2=888这条记录。

也就是先删除2条记录，再插入1条记录，影响行数=插入行数(1)+删除行数(2)=3。

insertduplicate语句是MySQL对SQL标准的扩展，它有2种行为：

insertduplicate语句的影响行数，保存在Statistics类的实例属性copied和updated中，计算公式：影响行数=copied+updated。

copied表示插入行数，updated表示更新行数。

接下来，我们来看看insertduplicate语句的执行过程。

第1步，调用插入记录方法，如果插入成功，插入操作主流程就完成了，不需要执行第2~4步。影响行数=copied(1)+updated(0)=1。

第2步，如果因为主键或唯一索引冲突导致插入失败，MySQL会找到是因为哪一个索引冲突造成的，然后构造由这个索引的所有字段组成的查询条件，去存储引擎读取冲突的记录，读取出来的这条记录叫作旧记录。

第3步，用insertduplicate语句update字段列表中的字段值替换旧记录中对应字段的值后得到新记录。

第4步，判断新记录和旧记录的内容是否完全一样。

如果完全一样，就不需要进行更新操作，影响行数=copied(0)+updated(0)=0。

如果不完全一样，调用更新记录方法，把新记录各字段的值更新到表中，影响行数=copied(1)+updated(1)=2。

有一点需要注意，如果待插入记录和表中多条记录存在主键或唯一索引冲突，insertduplicate只会更新冲突的第1条记录。哪个索引报记录冲突，就更新这个索引中冲突的这条记录。

replaceinto语句也是对标准SQL的扩展，它也有2种行为：

replaceinto语句的影响行数，保存在Statistics类的实例属性copied和deleted中，计算公式：影响行数=copied+deleted。

copied表示插入行数，deleted表示删除行数。

接下来，我们来看一下replaceinto语句的执行过程：

第1步，调用插入记录方法，如果插入成功，插入操作主流程就完成了，不需要执行第2~3步。影响行数=copied(1)+deleted(0)=1。

这一步和insertduplicate语句是一样的，因为它们俩在这一步执行的是同一行代码，兄弟俩还没有分家。

第2步，如果因为主键或唯一索引冲突导致插入失败，MySQL会找到是因为哪一个索引冲突造成的，然后构造由这个索引的所有字段组成的查询条件，从存储引擎读取冲突的记录，读取出来的这条记录叫作旧记录。

旧记录用于第3步中删除冲突记录，以及判断需要把插入记录中的哪些字段更新到表中。

这一步和insertduplicate语句也是一样的，因为在这一步它们执行的是同一段代码，兄弟俩还没有分家。

第3步，从这一步开始，replaceinto和insertduplicate的逻辑就不一样了。

在这一步，MySQL会根据一些条件判断是用更新旧记录，还是删除旧记录，插入新记录的方式来实现replaceinto操作。

使用更新旧记录方式，如果能够使用这种方式实现replaceinto，说明插入记录只和表中的一条记录冲突，把待插入记录各字段的值更新到旧记录中，增加deleted计数，replaceinto主流程就完成了。

因为replaceinto的语义是替换，也就是删除旧记录，插入新记录，所以，虽然这里用的是更新旧记录的方式，但计数还是用了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。

执行流程中还有一个逻辑没有说，就是第3步中，怎么决定使用更新旧记录方式还是删除旧记录，插入新记录方式。

使用更新旧记录方式，需要同时满足3个条件：

条件1，第2步中报记录冲突的那个索引是表中最后创建的唯一索引（也可能是主键）。

条件2，表中的所有字段，都没有被其它表的字段作为外键约束。

条件3，表上没有定义过删除触发器。

外键约束和删除触发器都很少使用，不展开讲了。

2.先说结论小节，先介绍了insert...onduplicatekeyupdate语句执行成功之后，影响行数可能的3种取值：0、1、2，以及对每一种取值进行了比较详细的说明。

然后介绍了replaceinto语句执行成功之后，影响行数可能的2种取值：1、N（大于1的整数），以及对这两种取值进行了比较详细的说明。

3.执行过程分析小节，详细分析了insert...onduplicatekeyupdate语句、replaceinto语句的执行过程。

以上就是本文的全部内容了，如果本文对你有所帮助，还请帮忙转发、点赞，谢谢^_^

OK，本文到此结束，希望对大家有所帮助。