[聚合文章] replace into 与insert into on duplicate key update的区别

大多数熟悉mysql的用户来说，碰到表数据替换的需求一般会想到下面两种SQL来处理: 1.replace into

2.insert into …on duplicate key update

以上都不属于标准SQL，也就是mysql中的"方言"。

那么用那种方式比较好呢？

首先我们来分析下这两种语句的差异：

1.replace into 语句

CREATE TABLE dianwoba (

select * from dianwoba;

mysql> replace into dianwoba(d,w) values(5,6);

Query OK, 2 rows affected (0.04 sec)

mysql> select * from dianwoba;

我们看到，对于replaceinto语句，当出现键值冲突时，如果没有指定某个字段的值，则这个字段就会修改成默认值如：(5,null,6)->(5,6,null)

接下来我们来看看Insert into .. on duplicate key update 语句。依然是对d=5的这行操作。

mysql> insert into dianwoba (d,w) values(5,'7') on duplicate key update b=values(b);

Query OK, 2 rows affected (0.04 sec)

mysql> select * from dianwoba;

我们看到，采用insert into …on duplicate key update 语句，对于不指定修改的列，则不修改如：(5,6,null)->(5,6,7)

通过上面的分析，我们知道了这两个语句之间的区别。

接下来，我们会演示这两个语句的另外的一个”陷阱“。我们将这个dianwoba在w列上增加一个唯一性索引，然后继续进行相关测试:

mysql> alter table dianwoba add unique key (w);

Query OK, 0 rows affected (0.14 sec)

Records: 0 Duplicates: 0 Warnings: 0

然后我们执行下面的replace into语句。

mysql> replace into dianwoba(w) values(6);

Query OK, 2 rows affected (0.03 sec)

mysql> select * from dianwoba;

(5,6,7)->(6,6,null)

这个完全符合replace into 语句发现冲突时，把原来的行删除，然后插入新行的逻辑。

咱们再来做一次replace into 操作，语句如下：

mysql> replace into dianwoba(d,w) values(6,5);

Query OK, 3 rows affected (0.07 sec)

请问一下，再操作完之后，会是什么结果？

mysql> select * from dianwoba;

我们来仔细分析一下 replace into dianwoba(d,w) values(6,5); 的执行过程：

第一步： (6,6,null)->(6,5,null)

第二步： (4,5,null)-> 删除

因此，对于有主键，且有唯一性索引的情况下，执行replaceinto语句，最终在数据库中产生的影响，需要遵循先操作主键（表），然后再操作唯一性索引的顺序。假如有多个唯一性索引，则一个replace语句，可能导致多条记录被删除。这个陷阱，对于研发人员来说，是务必知晓的细节，否则可能酿成大错。

同样场景我们再来分析一下insert into .on duplicate key update 语句的影响。

mysql> insert into dianwoba(d,w) values(6,4) on duplicate key update w=values(w);

ERROR 1062 (23000): Duplicate entry '4' for key 'w'

出现的结果是操作失败。没有像replace insert 那样，来删除一行来成全自己的事务。

mysql> insert into dianwoba(d,w,b) values(6,4,'4') on duplicate key update b=values(b);

Query OK, 2 rows affected (0.05 sec)

(6,5,null)->(6,5,4)

原因：对于在mysql中执行上面的允许冲突的语句，首先在主键上进行insert,如果发现冲突，则执行语句中update后面的更新操作。 然后就变更就完成。 在此列中，没有再去管b是什么值是不是有唯一索引。