当 REPLACE INTO 遇上主从复制：那个悄悄溜走的自增 ID

作者：张金牛， 爱可生华东交付服务部 DBA 成员，主要负责 MySQL 故障处理及相关技术支持。

爱可生开源社区出品，原创内容未经授权不得随意使用，转载请联系小编并注明来源。

本文约 1600 字，预计阅读需要 5 分钟。

一、背景：切换后的诡异报错

前几天，有位客户朋友反馈了一个挺有意思的问题：他们的 MySQL 主从切换后，往新主库（原从库）插入数据时，竟然报错了！错误信息是熟悉的 1062 - Duplicate entry ... for key 'PRIMARY'（主键冲突）。

排查过程像破案一样。

他们发现原从库上那张表，实际的最大自增 ID (MAX(id)) 居然比 SHOW CREATE TABLE 里显示的 AUTO_INCREMENT 值大了不少！这可就奇怪了，按常理，MAX(id) 应该比 AUTO_INCREMENT 小 1 才对呀！

于是他们带着疑问找了过来。

二、初步排查：谁动了我的自增 ID？

我们知道，数据库里自增 ID (AUTO_INCREMENT) 这个值，就像是给下一条新数据准备的“号码牌”。

正常情况下：DELETE 或者 UPDATE 操作，是不会动这个 “号码牌” AUTO_INCREMENT 的；只有 TRUNCATE TABLE 或者手动执行 ALTER TABLE ... AUTO_INCREMENT=... 这种 “重置” 操作，才会改变它。

那么问题来了，客户的 MAX(id) 为什么会比 AUTO_INCREMENT 大呢？

这明显违背了“号码牌”的发放规则。经过进一步沟通，发现客户在日常业务中大量使用了 REPLACE INTO 语句来操作这张表。好了，重点嫌疑人锁定！

三、现场复现：看 REPLACE INTO 如何“暗度陈仓”

为了验证猜想，我们用 MySQL 5.7.39 搭建主从环境来 “重现案发现场”。

1. 建张测试表

带自增主键 ID 和一个唯一索引 unique_code。

CREATE TABLE tb_test (
  id INT(11) NOT NULL AUTO_INCREMENT,
  unique_code VARCHAR(20) COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY unique_code (unique_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

2. 插点基础数据：

INSERT INTO tb_test(unique_code) VALUES('code1'), ('code2'), ('code3'), ('code4');
-- Query OK, 4 rows affected

3. 初始状态 (一切正常)：

主库 & 从库：

SELECT MAX(id) FROM tb_test; 
-- 结果：4
SELECT table_name, AUTO_INCREMENT FROM information_schema.tables WHERE table_name='tb_test'; 
-- 结果：AUTO_INCREMENT=5 (符合预期)

此时主从的 MAX(id) 都是 4，AUTO_INCREMENT 都是 5，和谐一致。

4. 魔法时刻：执行 REPLACE INTO

REPLACE INTO tb_test(unique_code) VALUES('code1'); 
-- 注意，'code1' 已存在！
-- Query OK, 2 rows affected 

嗯？影响了 2 行？再看状态（问题初现）。

主库:

SELECT MAX(id) FROM tb_test; 
-- 结果：5 (新ID诞生了)
SELECT AUTO_INCREMENT ...; 
-- 结果：6 (为下一条准备了)

从库:

SELECT MAX(id) FROM tb_test; 
-- 结果：5 (新 ID 同步过来了)
SELECT AUTO_INCREMENT ...; 
-- 结果：5 (咦？它没变？！)

问题浮现：主库的 AUTO_INCREMENT 欢快地蹦到了 6，而从库的 AUTO_INCREMENT 却还在原地踏步（5）。

主从的“号码牌”开始不一致了！

6. 模拟切换

现在，把原从库提升为新主库。致命 INSERT （报错来了）：尝试往新主库（原从库）插入新数据。

INSERT INTO tb_test(unique_code) VALUES('code5');
-- ERROR 1062 (23000): Duplicate entry '5' for key 'PRIMARY'

BOOM！报错了！

因为新主库的 AUTO_INCREMENT 还是 5，它试图分配 ID=5，但这个 ID 已经被之前 REPLACE INTO 产生的那条数据占用了（MAX(id) 已经是 5）。主键冲突，完美复现客户的问题！

四、真相揭秘：REPLACE INTO 的“变脸术”

我们查查 MySQL 5.7 官方文档对 REPLACE INTO[1] 的说明。

简单说：REPLACE INTO 干起活来基本像 INSERT；不同之处在于如果新数据行的值（在 PRIMARY KEY 或 UNIQUE 索引上）和表里的就数据冲突了，它会先把就数据删掉，再把新数据插进去。

听起来是 DELETE + INSERT，对吧？但现实很骨感！看看 REPLACE INTO 在 binlog 里究竟记录了什么：

解析执行 REPLACE INTO tb_test(unique_code) VALUES('code1'); 产生的 binlog:

# ... (省略表映射信息)
### UPDATE `test`.`tb_test`         <-- 注意！是 UPDATE！
### WHERE
###   @1=5                         <-- 原ID是5 (冲突的那条)
###   @2='code1'
### SET
###   @1=6                         <-- 新ID是6！
###   @2='code1'
# ... (提交信息)

破案关键点

1. 不是 DELETE + INSERT，而是 UPDATE！ Binlog 里清清楚楚记录的是 UPDATE 操作。这就是为什么客户看到的 REPLACE INTO 返回 2 rows affected (相当于删 1 条旧 + 插 1 条新)。
2. 自增 ID 的“潜规则”
   • 在 主库 上，REPLACE INTO 遇到唯一键冲突时，为了完成这个“先删后插”的魔法，它会悄悄地申请一个新的自增 ID (6) 给新行用。因此主库的 AUTO_INCREMENT 会正常递增到 6。
   • 在 从库 上，它只忠实回放 binlog。binlog 里写的是 UPDATE ... SET id=6 ...。从库执行这个 UPDATE 时，仅仅是修改了已存在行的 ID（从 5 改成 6），并没有触发申请新自增 ID 的机制！所以从库的 AUTO_INCREMENT 还傻傻地停留在 5。
3. 问题根源 REPLACE INTO 在主库上实质是 DELETE + INSERT（消耗新 ID），但在 binlog 里记录为 UPDATE（不消耗新 ID）。这个行为差异导致了主从库的 AUTO_INCREMENT 值在 REPLACE INTO 后出现分歧。当从库变主库后，它以为下一个可用 ID 是 5，但这个 ID 很可能已经被之前的 REPLACE INTO 产生的数据占用了（ID=5 的那条被更新为 ID=6 了，但物理 ID=5 可能还“存在”过或者占用了位置），于是就发生了主键冲突。

五、应对策略：如何规避这个坑

知道了原因，解决方案就清晰了：

1. 釜底抽薪：和业务聊聊，尽量别用 REPLACE INTO（这是最根本的）。
2. 升级换代：上 MySQL 8.0！（好消息！MySQL 8.0 优化了 binlog 记录方式）。
3. 手动矫正：定期“对齐”号码牌。如果短期内无法升级或改业务，可以在从库（尤其是即将切换的从库）上，手动执行 ALTER TABLE your_table_name AUTO_INCREMENT = (SELECT MAX(id) + 1 FROM your_table_name); 来修正 AUTO_INCREMENT 值。 高并发下请谨慎操作，可能引起短暂阻塞或需要配合 pt-online-schema-change 等工具。
4. 表设计优化：慎用自增主键+唯一键组合。如果这张表只有自增主键一个唯一约束，REPLACE INTO 就不会触发这个“变脸”问题（因为冲突只会发生在主键，而主键值由 REPLACE 语句显式或隐式提供）。评估业务是否真的需要额外的唯一索引。如果必须，方案 1、2、3 更优先。

总结

在 MySQL 5.7 及更早版本的主从复制环境中，REPLACE INTO 是个“两面派”（实际执行 DELETE + INSERT，binlog UPDATE），这会导致从库的自增 ID “号码牌” (AUTO_INCREMENT) 落后于主库。主从切换后，这个落后就可能引发主键冲突 (ERROR 1062)。理解其机制，才能选择最适合自己场景的规避方案。

参考资料

[1]

replace: https://dev.mysql.com/doc/refman/5.7/en/replace.html

本文关键字：#MySQL #REPACE #主从复制