replace into和insert into on duplicate key update的区别

在项目中，我们经常会遇到当数据库存在某条记录时，则更新数据，若不存在则插入数据的情况。在mysql中，replace into ...和insert into ... on duplicate key update ... 都可以实现，那么这两种有什么区别呢？

replace into和on duplcate key update都是只有在primary key或者unique key冲突的时候才会执行。如果数据存在，replace into则会将原有数据删除，再进行插入操作，这样就会有一种情况，如果某些字段有默认值，但是replace into语句的字段不完整，则会设置成默认值。而on duplicate key update则是执行update后面的语句。 下面我们来看一个例子来说明。

先创建一张测试表：

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)
);

插入一条测试数据：

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

执行replace into： 

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

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

 

现在我们将数据还原：

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

执行on duplicate key update： 

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

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