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语句,保证不会造成数据字段丢失。
posted @ 2019-06-10 16:30  只会玩卡尔  阅读(...)  评论(...编辑  收藏