灯下哥谭
灯下哥谭
2022-03-10
目录

MySQL insert on duplicate key update,replace into , insert ignore的理解

假设有一个表test，结构如下：

版本说明

本文写于 2022-03。
INSERT ... ON DUPLICATE KEY UPDATE 语义未变，经 2026-07 复核仍适用。

CREATE TABLE test (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT
) ENGINE=InnoDB
1
2
3
4
5

现在要执行一条REPLACE INTO语句，用于向表中插入数据，如果发现id重复，则更新该记录的name和age字段。例如：

mysql> REPLACE INTO test(id, name, age) VALUES(1, 'replace', 30);select * from test;
Query OK, 1 row affected (0.01 sec)

+----+---------+------+
| id | name    | age  |
+----+---------+------+
|  1 | replace |   30 |
+----+---------+------+
1 row in set (0.00 sec)

mysql> REPLACE INTO test(id, name, age) VALUES(1, 'replace', 33);select * from test;
Query OK, 2 rows affected (0.00 sec)

+----+---------+------+
| id | name    | age  |
+----+---------+------+
|  1 | replace |   33 |
+----+---------+------+
1 row in set (0.00 sec)

mysql> INSERT INTO test(id, name, age) VALUES(1, 'ON DUPLICATE KEY UPDATE', 33)  ON DUPLICATE KEY UPDATE `id`=VALUES(`id`),`name`=VALUES(`name`),`age`=VALUES(`age`);select * from test;
Query OK, 2 rows affected, 3 warnings (0.01 sec)
+----+-------------------------+------+
| id | name                    | age  |
+----+-------------------------+------+
|  1 | ON DUPLICATE KEY UPDATE |   33 |
+----+-------------------------+------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

binlog 记录如下

BEGIN
/*!*/;
# at 7006
#230317  4:06:28 server id 1  end_log_pos 7087 CRC32 0x2cd47a60 	Rows_query
# REPLACE INTO test(id, name, age) VALUES(1, 'replace', 30)
# at 7087
#230317  4:06:28 server id 1  end_log_pos 7148 CRC32 0x60606164 	Table_map: `demo`.`test` mapped to number 98
# at 7148
#230317  4:06:28 server id 1  end_log_pos 7200 CRC32 0x55b54522 	Write_rows: table id 98 flags: STMT_END_F
### INSERT INTO `demo`.`test`
### SET
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
###   @2='replace' /* VARSTRING(200) meta=200 nullable=1 is_null=0 */
###   @3=30 /* INT meta=0 nullable=1 is_null=0 */
# at 7200
#230317  4:06:28 server id 1  end_log_pos 7231 CRC32 0xe178ee43 	Xid = 126
COMMIT/*!*/;
# at 7231
#230317  4:06:34 server id 1  end_log_pos 7310 CRC32 0xabcd17c8 	Anonymous_GTID	last_committed=22	sequence_number=23	rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 7310
#230317  4:06:34 server id 1  end_log_pos 7387 CRC32 0xa584b414 	Query	thread_id=14	exec_time=0	error_code=0
SET TIMESTAMP=1678997194/*!*/;
BEGIN
/*!*/;
# at 7387
#230317  4:06:34 server id 1  end_log_pos 7468 CRC32 0x0d6bbbb0 	Rows_query
# REPLACE INTO test(id, name, age) VALUES(1, 'replace', 33)
# at 7468
#230317  4:06:34 server id 1  end_log_pos 7529 CRC32 0xf3688ef0 	Table_map: `demo`.`test` mapped to number 98
# at 7529
#230317  4:06:34 server id 1  end_log_pos 7599 CRC32 0xa914e987 	Update_rows: table id 98 flags: STMT_END_F
### UPDATE `demo`.`test`
### WHERE
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
###   @2='replace' /* VARSTRING(200) meta=200 nullable=1 is_null=0 */
###   @3=30 /* INT meta=0 nullable=1 is_null=0 */
### SET
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
###   @2='replace' /* VARSTRING(200) meta=200 nullable=1 is_null=0 */
###   @3=33 /* INT meta=0 nullable=1 is_null=0 */
# at 7599
#230317  4:06:34 server id 1  end_log_pos 7630 CRC32 0x5cc70722 	Xid = 128
COMMIT/*!*/;
# at 7630
#230317  4:06:46 server id 1  end_log_pos 7709 CRC32 0x028a0392 	Anonymous_GTID	last_committed=23	sequence_number=24	rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 7709
#230317  4:06:46 server id 1  end_log_pos 7786 CRC32 0x8ffcb18b 	Query	thread_id=14	exec_time=0	error_code=0
SET TIMESTAMP=1678997206/*!*/;
BEGIN
/*!*/;
# at 7786
#230317  4:06:46 server id 1  end_log_pos 7967 CRC32 0x56a81bce 	Rows_query
# INSERT INTO test(id, name, age) VALUES(1, 'ON DUPLICATE KEY UPDATE', 33)  ON DUPLICATE KEY UPDATE `id`=VALUES(`id`),`name`=VALUES(`name`),`age`=VALUES(`age`)
# at 7967
#230317  4:06:46 server id 1  end_log_pos 8028 CRC32 0x83a6d339 	Table_map: `demo`.`test` mapped to number 98
# at 8028
#230317  4:06:46 server id 1  end_log_pos 8114 CRC32 0x83832170 	Update_rows: table id 98 flags: STMT_END_F
### UPDATE `demo`.`test`
### WHERE
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
###   @2='replace' /* VARSTRING(200) meta=200 nullable=1 is_null=0 */
###   @3=33 /* INT meta=0 nullable=1 is_null=0 */
### SET
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
###   @2='ON DUPLICATE KEY UPDATE' /* VARSTRING(200) meta=200 nullable=1 is_null=0 */
###   @3=33 /* INT meta=0 nullable=1 is_null=0 */
# at 8114
#230317  4:06:46 server id 1  end_log_pos 8145 CRC32 0xb32f2b14 	Xid = 130
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78

# 上面这段 binlog 说明了什么——以及不说明什么

binlog 里两条语句记录的都是 Update_rows，没有出现 Delete_rows。很容易由此得出结论：「所谓 REPLACE 是先删后插，是谣传」。

这个结论是错的，得区分两件事：

REPLACE 的语义 REPLACE 在 binlog 里的记录形式
是不是先删后插 是，官方文档明确定义为「冲突时 DELETE 旧行再 INSERT 新行」 不一定，8.0 在「只冲突一个唯一键、且能原地改」时会优化成一条 Update_rows

binlog 是结果的行镜像，不是执行过程的逐步录像。行式复制只要保证从库回放后的最终行状态与主库一致即可，把「删一行 + 插一行」压成「更新一行」是合法的等价优化。用 binlog 的记录形式去反推语义，推错了。

而且这段实测里其实已经留下了反证——注意第二条 REPLACE 的返回：

mysql> REPLACE INTO test(id, name, age) VALUES(1, 'replace', 33);
Query OK, 2 rows affected (0.00 sec)
1
2

表里明明只有一行，为什么是 2 rows affected？因为 REPLACE 就是把「删掉 1 行」和「插入 1 行」加在一起报的。没冲突时它返回 1，冲突时返回 2——这个计数规则本身就是「先删后插」的直接证据。

# 「先删后插」在什么时候会咬人

这不是一个只在文档里成立的细节，它有四个能直接把生产打出问题的后果：

  1. 未指定的列会被重置为默认值。REPLACE INTO test(id, name) VALUES(1,'x') 之后，age 不是保留原值，而是变成 NULL/默认值——因为旧行已经被整行删掉了。这是 REPLACE 最常见的事故形态。
  2. AUTO_INCREMENT 会持续消耗。每次冲突都产生一次新插入，自增值一路往前跳，不会复用。
  3. 触发器按 DELETE + INSERT 触发，而不是 UPDATE。挂了审计触发器的表用 REPLACE，审计日志会记出一条删除。
  4. 外键级联会真的级联删除。子表上如果是 ON DELETE CASCADE，父表一次 REPLACE 就会把子表数据带走——这一条最危险，也最容易被「binlog 里没有 DELETE」这个观察误导。

# 所以两者的真正区别

  • REPLACE：整行替换。你没写的列会丢原值，且带上面四条副作用。
  • INSERT ... ON DUPLICATE KEY UPDATE：真正的 UPDATE，只改你在 ON DUPLICATE KEY UPDATE 后面列出的列，其余列保持不变，不触发 DELETE 触发器、不做级联删除。

比如下面这条，age 不会被改成 43，只有 name 会变——这是 REPLACE 做不到的：

INSERT INTO test(id, name, age) VALUES(1, 'myname', 43)  ON DUPLICATE KEY UPDATE `name`=VALUES(`name`);  select * from test;
1

MySQL replace into 有三种形式：

1. replace into tbl_name(col_name, ...) values(...)

2. replace into tbl_name(col_name, ...) select ...

3. replace into tbl_name set col_name=value, ...

  1. insert ignore into

    当插入数据时，如出现错误时，如重复数据，将不返回错误，只以警告形式返回。所以使用ignore请确保语句本身没有问题，否则也会被忽略掉。例如：

INSERT IGNORE INTO books (name) VALUES ('MySQL Manual')

  1. on duplicate key update

    当primary或者unique重复时，则执行update语句，如update后为无用语句，如id=id，则同1功能相同，但错误不会被忽略掉。例如，为了实现name重复的数据插入不报错，可使用一下语句：

    INSERT INTO books (name) VALUES ('MySQL Manual') ON duplicate KEY UPDATE id = id INSERT INTO test(id, name, age) VALUES(1, 'myname', 43) ON DUPLICATE KEY UPDATE name=VALUES(name); select * from test;

  2. insert … select … where not exist

    根据select的条件判断是否插入，可以不光通过primary 和unique来判断，也可通过其它条件。例如：

    INSERT INTO books (name) SELECT 'MySQL Manual' FROM dual WHERE NOT EXISTS (SELECT id FROM books WHERE id = 1)

  3. replace into

    如果存在 primary 或 unique 相同的记录，先删除旧记录再插入新记录（副作用见上一节）。

    REPLACE INTO books SELECT 1, 'MySQL Manual' FROM books

# 怎么选

需求 用哪个
冲突时只更新部分列，保留其余列 INSERT ... ON DUPLICATE KEY UPDATE
冲突时整行以新数据为准，且表上没有触发器/级联外键/自增依赖 REPLACE INTO
冲突时什么都不做，跳过即可 INSERT IGNORE（但要清楚它会连别的错误一起吞）
判断条件不是主键/唯一键 INSERT ... SELECT ... WHERE NOT EXISTS

默认选 ON DUPLICATE KEY UPDATE。它是四种里语义最窄、副作用最少的一个。

# 坑与边界

  • INSERT IGNORE 会吞掉的远不止重复键。数据被截断（Data truncated）、NULL 插进 NOT NULL 列（会被改写成隐式默认值）、外键不满足等错误，全部降级成 warning。它相当于给整条语句关掉了报错，用之前务必确认语句本身没别的问题，插完记得 SHOW WARNINGS 看一眼。

  • 表上有多个唯一键时，REPLACE 可能删掉不止一行。冲突判定是对所有唯一约束做的，若新行同时和 A、B 两行分别冲突，两行都会被删掉再插入一行——从 2 行变 1 行，静默丢数据。ON DUPLICATE KEY UPDATE 在这种情况下只会更新它遇到的第一个冲突行，同样不符合直觉。表上有多个唯一键时，这两个语句都不要用，老老实实先查后写或用显式事务。

  • VALUES() 函数在 8.0.20 起被标记弃用。新写法是给插入行取别名：

    INSERT INTO test(id, name, age) VALUES(1, 'myname', 43) AS new
  ON DUPLICATE KEY UPDATE name = new.name;
    1
    2

    旧的 VALUES(name) 目前仍能用，但会在错误日志里留弃用告警，新代码建议直接用别名写法。

  • affected_rows 的返回值不能当成「改了几行」：ON DUPLICATE KEY UPDATE 插入返回 1、更新返回 2、值没变化的更新返回 0。用返回值判断「写成功没有」的代码，会在「重复提交同样的数据」时误判成失败。

  • 并发下这几个语句都可能死锁。它们都要先做唯一性检查再决定插入还是更新，高并发同键写入时容易在唯一索引上互相等待。表现为 ERROR 1213，处置见 MySQL死锁问题。

# 验证

自己动手确认「未指定列被重置」这个行为，比记结论可靠：

CREATE TABLE t_rp (id INT PRIMARY KEY, name VARCHAR(20), age INT);
INSERT INTO t_rp VALUES (1, 'origin', 30);

REPLACE INTO t_rp(id, name) VALUES (1, 'replaced');
SELECT * FROM t_rp;
1
2
3
4
5

预期输出（age 丢了）：

+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  1 | replaced | NULL |
+----+----------+------+
1
2
3
4
5

换成 ODKU 对比：

UPDATE t_rp SET age = 30;
INSERT INTO t_rp(id, name) VALUES (1, 'odku') AS new
  ON DUPLICATE KEY UPDATE name = new.name;
SELECT * FROM t_rp;
1
2
3
4

预期输出（age 保住了）：

+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | odku |   30 |
+----+------+------+
1
2
3
4
5
#学习笔记#MySQL
上次更新: 9/11/2026

← MySQL不同字符集之间的区别和选择→

最近更新
01
DeepSeek Harness 实战 06｜学习笔记：插件、工具、技能不在同一个维度上 原创
09-11
02
DeepSeek Harness 实战 05｜让两个编码 Agent 共用一份长期记忆 原创
09-09
03
DeepSeek Harness 实战 04｜学习笔记：从「已知限制」里读出三处设计张力 原创
09-08
更多文章>
Theme by Vdoing