背景
我们在向数据库里插入数据的时候,如果没有出现主键或者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