mysql关于数据迁移同步的两种sql

Replace into 和 insert into on duplicate update

简介

relace into，意思就是替换插入。如果即将插入的数据已存在，就删除原数据，插入新数据。

insert into on duplicate update，意思就是先进行插入，如果即将插入的数据已存在，就更新一些字段的值；如果不存在，就执行插入。

• 这里即将插入的数据需要包含唯一列。主键列或者是唯一索引列。
• 更新一些字段的值：一些字段是在 on duplicate update后设置的字段。

---

实例

1. 创建数据表

CREATE TABLE `student` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` varchar(30) DEFAULT NULL COMMENT '姓名',
  `age` int DEFAULT NULL COMMENT '年龄',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2. 插入一些数据

insert into student (name, age) VALUES ('张三', 18),('李四', 20),('王五', 22);

3. 结果

测试

• replace 方式

-- 插入的新数据姓名为张四，年龄为20 观察是否会报错
replace into student (id, name, age) value (1, '张四', 20);

可以看出sql没有报主键不能重复的错误，执行成功，并且姓名和年龄都被修改为新数据。

• insert into on duplicate update方式 测试前先将数据还原

--- 同样 插入的新数据姓名为张四，年龄为20 观察是否会报错
insert into student (id, name, age) value (1, '张四', 20) on DUPLICATE key update name = values(name)

可以看出没有报错并且只有姓名修改为张四，年龄没有修改。这是因为在 on DUPLICATE key update name = values(name)在key重复的情况下，只更新了姓名字段，并没有更新age字段。

values(name)是获取新数据行name列的值。

总结

relace into 是先判断是否存在，如果存在就删除原有数据行，插入新数据行。 on duplicate key 是先执行插入，如果存在，就更新update后的字段。