MySQL--REPLACE INTO和INSERT INTO ON DUPLICATE KEY UPDATE使用选择

测试环境

MySQL版本： 5.7.26-29-log Percona Server (GPL)

测试表结构：

DROP TABLE IF EXISTS `tb101`;
CREATE TABLE `tb101` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c1` int(11) DEFAULT NULL,
  `c2` int(11) DEFAULT NULL,
  `dt` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_c1` (`c1`),
  UNIQUE KEY `idx_c2` (`c2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

初始化测试数据：

INSERT INTO tb101(id,c1,c2,dt)
VALUES(1,1,1,'2021-01-01 00:00:00'),
(2,2,2,'2021-01-02 00:00:00'),
(3,3,3,'2021-01-03 00:00:00'),
(4,4,4,'2021-01-04 00:00:00');

模拟测试--REPLACE INTO

测试脚本：

REPLACE INTO tb101(c1,c2)VALUES(1,11);
Query OK, 2 rows affected (0.00 sec)

REPLACE INTO tb101(c1,c2)VALUES(2,3);
Query OK, 3 rows affected (0.00 sec)

SELECT * FROM tb101;
+----+------+------+---------------------+
| id | c1   | c2   | dt                  |
+----+------+------+---------------------+
|  4 |    4 |    4 | 2021-01-04 00:00:00 |
|  5 |    1 |   11 | 2022-01-20 15:39:07 |
|  6 |    2 |    3 | 2022-01-20 15:39:13 |
+----+------+------+---------------------+
3 rows in set (0.00 sec)

模拟测试--INSERT INTO ON DUPLICATE

测试脚本：

INSERT INTO tb101(c1,c2)VALUES(1,11)
ON DUPLICATE KEY UPDATE c1=VALUES(c1),c2=VALUES(c2);
Query OK, 2 rows affected (0.00 sec)

INSERT INTO tb101(c1,c2)VALUES(2,3)
ON DUPLICATE KEY UPDATE c1=VALUES(c1),c2=VALUES(c2);
ERROR 1062 (23000): Duplicate entry '3' for key 'idx_c2'

SELECT * FROM tb101;
+----+------+------+---------------------+
| id | c1   | c2   | dt                  |
+----+------+------+---------------------+
|  1 |    1 |   11 | 2021-01-01 00:00:00 |
|  2 |    2 |    2 | 2021-01-02 00:00:00 |
|  3 |    3 |    3 | 2021-01-03 00:00:00 |
|  4 |    4 |    4 | 2021-01-04 00:00:00 |
+----+------+------+---------------------+
4 rows in set (0.00 sec)

测试结论

REPLACE INTO等价于DELETE+INSERT，当存在主键或唯一健冲突时，会先按照主键或唯一键进行删除，再按照REPLACE INTO语句中VALUE进行插入，对于REPLACE INTO语句中未指定的列，会使用默认值或自增值。如果新插入记录和多条已有记录冲突，会先删除所有存在冲突记录，然后插入新记录，果对于表为自增表且REPLACE INTO语句未指定自增主键值，则会为插入记录分配新的自增ID，导致记录主键发生变化。

INSERT INTO ON DUPLICATE KEY UPDATE等价于INSERT OR UPDATE，会先尝试做INSERT操作，当遇到主键或唯一键冲突时，会尝试进行UPDATE操作。在进行UPDATE时，会按照ON DUPLICATE KEY UPDATE语句进行更新，UPDATE未指定字段会保留现有字段值。如新插入记录和多条已有记录在不同唯一键或主键上冲突时，ON DUPLICATE KEY UPDATE语句执行会触发唯一键错误导致插入失败。

操作性能

当数据无冲突时，REPLACE INTO和INSERT INTO ON DUPLICATE KEY UPDATE 都会被转换为INSERT INTO操作，操作性能相同。

当数据有冲突时，REPLACE INTO被转换为DELETE+INSERT操作，尤其在自增主键发生变化时，需要对所有索引进行DELETE+INSER操作。而INSERT INTO ON DUPLICATE KEY UPDATE被转换为UPDATE操作，仅需要更新需要更新的索引(非主键索引更新会被转换为DELETE+INSERT)。DELETE+INSERT的操作性能会略高于UPDATE操作性能，但性能差异较小可忽略。

使用选择

无论REPLACE INTO还是INSERT INTO ON DUPLICATE KEY UPDATE，对于数据冲突场景，都会被拆解为插入+更新或插入+删除+插入等多步操作，在很多场景尤其高并发场景下，多个线程同时操作相同数据，可能引发严重的锁阻塞和死锁问题。虽然唯一索引能最终保证数据唯一性，但唯一索引不能作为保证数据唯一性的唯一手段，需要在业务层通过全局锁或其他手段来进行合理的并发控制，避免滥用REPLACE INTO或INSERT INTO ON DUPLICATE KEY UPDATE两种MySQL特殊语法。