replace into 和 insert into ON DUPLICATE KEY 用法

 

-- 查询创建表语句
show create table person;
-- 创建person表，主键为int自增
CREATE TABLE `person` (
  `id` int(6) NOT NULL AUTO_INCREMENT,
  `name` varchar(32) DEFAULT NULL,
  `phone` varchar(32) DEFAULT NULL,
  `address` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
-- 新增测试数据
insert into person(name,phone,address) values ('张三','18812345678','浙江省杭州市');
insert into person(name,phone,address) values ('李四','18712345678','湖南省长沙市');
insert into person(name,phone,address) values ('王五','18612345678','江苏省南京市');
-- 查询数据
select * from person;

-- 执行replace into语句
replace into person(id,name,phone) values (1,'张三','18812345678') ;

-- 查询结果
select * from person;

-- 执行replace into语句
replace into person(id,name,phone,address) values (4,'赵六','18512345678','河北省石家庄市');

-- 查询结果
select * from person;

根据如上执行过程可以看出：
第一次的replace into语句，为更新id=1的数据，结果影响了2行，并且更新后address字段值内容为空。
第二次的replace into语句，为新增id=4的数据，结果影响了1行。
通过查询资料发现，若更新原有数据，replace into会先删除掉原有数据，此时结果影响1行，随后又新增该更新后的语句，结果又影响1行，由于values中未带有address字段值内容，所以更新后数据address字段值为空。

-- 执行insert into ON DUPLICATE KEY 语句
insert into person values (1,'张三','18888888888','浙江省杭州市') ON DUPLICATE KEY UPDATE address = '黑龙江省哈尔滨市'

-- 查询结果
select * from person;

-- 执行insert into ON DUPLICATE KEY 语句
insert into person values (5,'孙七','18412345678','内蒙古呼和浩特') ON DUPLICATE KEY UPDATE address = '黑龙江省哈尔滨市';

-- 查询结果
select * from person;

根据如上执行过程可以看出：
第一次的insert into ON DUPLICATE KEY UPDATE语句，为更新id=1的数据，结果影响了2行，并且更新后phone没有改变，address改变，说明执行更新的为update后面的字段。
第二次的insert into ON DUPLICATE KEY UPDATE语句，为新增id=5的数据，结果影响了1行，并且字段address为前者的“内蒙古呼和浩特”，并非update后面的字段值。
同replace into，先执行delete，后执行insert into，区别为，insert into ON DUPLICATE KEY UPDATE语句相当于执行的是if exist do update else do insert，因此，如果replace into填充的字段不全，则会导致未被更新的字段都会修改为默认值，insert into ON DUPLICATE KEY UPDATE语句只是更新部分字段，对于未被更新的字段不会变化。
所以：在相关应用场景中，如数据同步服务，尽量使用insert into ON DUPLICATE KEY UPDATE语句，保证不会造成数据字段丢失。

 

分类: 数据库