MySQL insert or update

业务场景

• 我们经常会遇到类似的业务场景，插入一条数据如果他不存在则执行 insert ，当这条记录存在的时候，我们去 update 他的一些属性（或者什么都不做）。

解决方案：

• 使用 ON DUPLICATE KEY UPDATE
  在 主键 或者 唯一约束 重复时，执行更新操作。
• 使用 REPLACE INTO
  在 主键 或者 唯一约束 重复时，先 delete 再 insert。

ON DUPLICATE KEY UPDATE

• 创建表，建立唯一约束，准备一条数据

CREATE TABLE `stu_class_ref` (
  `id` varchar(30) NOT NULL,
  `stu_id` varchar(30) DEFAULT NULL,
  `class_id` varchar(30) DEFAULT NULL,
  `note` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `stu_id` (`stu_id`,`class_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO 
`test`.`stu_class_ref`(`id`, `stu_id`, `class_id`, `note`) 
VALUES ('001', 'zhangsan', 'yuwen', NULL);

准备数据

• 使用 ON DUPLICATE KEY UPDATE

INSERT INTO 
`test`.`stu_class_ref`(`id`, `stu_id`, `class_id`, `note`) 
VALUES (UUID_SHORT(), 'zhangsan', 'yuwen', '我喜欢语文:)')
ON DUPLICATE KEY UPDATE note = '我喜欢语文:)'
> Affected rows: 2
> 时间: 0.042s

• Affected rows: 2，MySQL 检查插入的行是否会产生重复键错误，如果会则执行update

ON DUPLICATE KEY UPDATE

• 如果想要引用 VALUES 中的值，参考如下

INSERT INTO 
`test`.`stu_class_ref`(`id`, `stu_id`, `class_id`, `note`) 
VALUES (UUID_SHORT(), 'zhangsan', 'yuwen', NULL)
ON DUPLICATE KEY UPDATE note = VALUES(class_id)
> Affected rows: 2
> 时间: 0.006s

ON DUPLICATE KEY UPDATE

REPLACE INTO

• MySQL 中 还有一个黑科技语法 REPLACE INTO

REPLACE INTO `stu_class_ref`(`id`, `stu_id`, `class_id`, `note`) 
VALUES (UUID_SHORT(), 'zhangsan', 'yuwen', NULL)
> Affected rows: 2
> 时间: 0.004s

REPLACE INTO

• REPLACE INTO 就比较简单粗暴了，他会先执行delete 操作，然后insert

ON DUPLICATE KEY UPDATE 与 REPLACE INTO

• 再来创建一张表, 创建三个唯一约束, 插入三条数据

CREATE TABLE `interesting` (
  `id` varchar(30) NOT NULL,
  `uni_a` varchar(30) DEFAULT NULL,
  `uni_b` varchar(30) DEFAULT NULL,
  `uni_c` varchar(30) DEFAULT NULL,
  `version` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uni_a` (`uni_a`) USING BTREE,
  UNIQUE KEY `uni_b` (`uni_b`) USING BTREE,
  UNIQUE KEY `uni_c` (`uni_c`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `test`.`interesting`(`id`, `uni_a`, `uni_b`, `uni_c`, `version`) 
VALUES ('1', 'a', 'a', 'a', NULL);
INSERT INTO `test`.`interesting`(`id`, `uni_a`, `uni_b`, `uni_c`, `version`) 
VALUES ('2', 'b', 'b', 'b', NULL);
INSERT INTO `test`.`interesting`(`id`, `uni_a`, `uni_b`, `uni_c`, `version`) 
VALUES ('3', 'c', 'c', 'c', NULL);

准备数据

1. 执行 ON DUPLICATE KEY UPDATE

INSERT INTO `interesting`(`id`, `uni_a`, `uni_b`, `uni_c`, `version`) 
VALUES (UUID_SHORT(), 'a', 'b', 'c', NULL)
ON DUPLICATE KEY UPDATE version = 666
> Affected rows: 2
> 时间: 0.049s

ON DUPLICATE KEY UPDATE

• Affected rows: 2 但是其实三条主键都有冲突了

2. 再看一下 REPLACE INTO

REPLACE INTO `interesting`(`id`, `uni_a`, `uni_b`, `uni_c`, `version`) 
VALUES (UUID_SHORT(), 'a', 'b', 'c', NULL)
> Affected rows: 4
> 时间: 0.026s

REPLACE INTO

• Affected rows: 4 REPLACE INTO 将三条有冲突的全部delete 然后 insert

总结：

• ON DUPLICATE KEY UPDATE 只会对所匹配的第一行进行update,
• REPLACE INTO 会对所有匹配行进行delete, insert
• 所以应避免对有多个唯一索引的表使用