MySQL replace into和on duplicate key update区别

背景

我们在向数据库里插入数据的时候，如果没有出现主键或者unique索引冲突，直接执行插入操作，而遇到冲突情况时需要将原有主键或者唯一索引所在的记录更新。

以下有三种做法供我们选择：

1、 先select，如果记录存在update该条数据，否则直接插入(笨方法)
2、 使用replace into，这是mysql自身的语法，使用方式

replace into tablename (f1, f2, f3) values(vf1, vf2, vf3),(vvf1, vvf2, vvf3)

3、 使用on duplicate key update，也是mysql自带的语法，使用方式

INSERT INTO table (a,b,c) VALUES (1,2,3)
ON DUPLICATE KEY UPDATE c=c+1;

实验

先创建一张测试表：

CREATE TABLE test(
id INT NOT NULL AUTO_INCREMENT,
col1 VARCHAR(32) NOT NULL,
col2 VARCHAR(32) DEFAULT NULL,
PRIMARY KEY(id),
UNIQUE KEY(col1)
);

desc test;

插入一条数据测试：

insert into test values (null, 'unique', '123456');

查看表数据：

SELECT * FROM test WHERE col1='unique';

执行replace into：

replace into test(col1) values ('unique');

查看表数据：

SELECT * FROM test WHERE col1='unique';

现象：可以看到id的值增加了1，并且col2的值变为了NUll，说明在出现冲突的情况下，使用replace into只是先delete掉原来的数据，再执行insert操作，如果新的insert语句不完成，会将其余字段设置为默认值。

将数据还原：

update test set col2 = '123456' where col1 = 'unique';

查看表数据：

SELECT * FROM test WHERE col1='unique';

执行on duplicate key update：

insert into test values(null, 'unique', '654321') on duplicate key update col1 = 'update_unique';

查看表数据：

SELECT * FROM test WHERE col1='update_unique'

可以看到id的值没有变化，col2的值也没有变化，只有col1的值发生了变化，说明在出现冲突的情况下，on duplicate key update只是执行了update后面的语句。

结论

1、当插入数据主键或者unique索引没有冲突：
replace into和on duplicate key update的做法都是直接插入数据

2、出现主键或者unique索引冲突时：
replace into ：先删除此行数据，然后插入新的数据

on duplicate key update：只是执行update后面的语句属性的更新，其余属性不变。

参考MySQL官方文档
https://dev.mysql.com/doc/refman/5.7/en/replace.html
https://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html