一、前言

最近在做一个需求，需要在我们自己的数据库中保存全量的第三方数据，最终的方案是使用定时任务请求并更新表中的数据。对于拉取到的数据，如果表中没有则直接新增一条数据，如果表中已有数据则更新相应的字段。数据库使用的是MySQL 5.7版本。这让我知道了MySQL中一个独特的语法 insert into ... on duplicate key update ...。

该语法通过在insert 语句后加上 on duplicate key update 子句，能够起到如下的效果：

如果插入的数据在表中存在唯一键或者主键冲突，则会更新冲突的行；
如果插入的数据在表中不存在唯一键或者主键冲突，则会新增一条数据。

二、用法详解

2.1 数据表结构

CREATE TABLE user(
  id BIGINT(20) NOT NULL DEFAULT '0' COMMENT '用户id',
  name VARCHAR(20) NOT NULL DEFAULT '' COMMENT '用户名',
  gender TINYINT(2) NOT NULL DEFAULT '0' COMMENT '用户性别， 1-男， 2-女',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_name` (`name`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户信息'

INSERT INTO user(id, name, gender) VALUES(10, "xiaowang", 1),(11, "xiaoli",2),(12, "xiaozhao",1)

数据信息

2.2 不同情况的插入情况

2.2.1 没有冲突新增数据

INSERT INTO user(id, name, gender) VALUE(13, 'xiaozi', 1) ON DUPLICATE KEY UPDATE id = 13;

输出信息：表示成功插入一行数据

2.2.2 有冲突没有修改数据

重复执行上述语句

INSERT INTO user(id, name, gender) VALUE(13, 'xiaozi', 1) ON DUPLICATE KEY UPDATE id = 13;

因为 id = 13 和表中数据一样，因此不会更新任何数据。

输出信息：没有改变任何数据

2.2.3 有冲突修改了数据

情况一：修改成功

重复执行上述语句将 id 修改成5

INSERT INTO user(id, name, gender) VALUE(13, 'xiaozi', 1) ON DUPLICATE KEY UPDATE id = 5;

因为 id 为5 在表中不存在，则会将冲突的 id 13修改成5

输出信息：插入一行，更新一行，影响了2行

此时表中的数据为

情况二：修改失败

重复执行上述语句将id改成已有的主键id，比如11

INSERT INTO user(id, name, gender) VALUE(13, 'xiaozi', 1) ON DUPLICATE KEY UPDATE id = 11;

因为主键11的行已经存在表中，此时会执行失败；

输出信息为：

情况三：更新过程中涉及主键和唯一键冲突，两个键

重复执行上述语句将 ‘xiaozi‘ 改成 ‘xiaowang’

INSERT INTO user(id, name, gender) VALUE(13, 'xiaowang', 1) ON DUPLICATE KEY UPDATE id = 5

输出信息：影响到两行数据

表中的数据

可以看到，只更新了数据的主键，唯一键并没有改变。即发生冲突时，只会更新首次出现的键。

总结

MySQL 的独特语法 insert into ... on duplicate key update ... ; 在发生主键或唯一键冲突时，可以使用update 后的条件更新数据表中的行，前提是update后的条件不能与表中已有的数据冲突。

MySQL insert into ... on duplicate key update