在 MySQL 5.7 版本中，使用 REPLACE INTO 操作时，如果表具有自增主键，可能会出现主从数据库在 auto_increment 值上的不一致现象。这种情况在主从切换后可能导致业务写入操作触发主键冲突错误。本文将详细介绍这一问题的成因、影响、复现方法、原因分析以及解决方案。
问题描述
现象
在主从数据库架构中，当使用 REPLACE INTO 操作，并且表具有自增主键时，如果发生主从故障切换，业务的正常插入操作可能会触发主键冲突的错误（ERROR 1062 (23000): Duplicate entry 'XXX' for key 'PRIMARY'）。
影响评估
- 如果业务逻辑中使用了 REPLACE INTO 或 INSERT...ON DUPLICATE KEY UPDATE，主从数据库的 auto_increment 值不一致会导致写入操作失败。
- 当 auto_increment 值相差不大时，业务重试可能会成功绕过错误。但如果相差较大，可能会超出业务重试次数，造成更大的影响。
问题复现
环境搭建
- 使用 CentOS Linux release 7.3
- 安装 MySQL 社区版 5.7
- 搭建一主一从的 MySQL 架构
表结构
CREATE TABLE `test_autoincrement` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
`name` varchar(100) NOT NULL DEFAULT 'test' COMMENT '测试名字',
`uid` int(11) NOT NULL COMMENT '测试表唯一键',
PRIMARY KEY (`id`),
UNIQUE KEY `uid` (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
测试数据插入
INSERT INTO test_autoincrement (name, uid) SELECT '张三', 1001;
INSERT INTO test_autoincrement (name, uid) SELECT '李四', 1002;
INSERT INTO test_autoincrement (name, uid) SELECT '王五', 1003;
问题复现
- 使用 REPLACE INTO 操作更新数据：
REPLACE INTO test_autoincrement (name, uid) VALUES ('张三丰', 1001);
- 模拟主从切换：
- 停止从库复制线程
- 查看从库的 Executed_Gtid_Set 值
- 重做主从关系
- 启动从库复制线程
- 模拟业务正常写入：
INSERT INTO test_autoincrement (name, uid) SELECT '赵六', 1004;
原因分析
从库 autoincrement 未变化的原因
REPLACE INTO 操作在 binlog 中记录为 update 操作，从库在应用 update 操作时，不会改变 autoincrement 值。
REPLACE INTO 操作的官方定义
REPLACE INTO 操作在表具有主键或唯一索引时，会先删除旧行再插入新行。如果没有主键或唯一索引，REPLACE 等同于 INSERT。
binlog 日志记录为 update 操作的原因
- 当发生冲突的键是最后一个唯一键，且没有外键约束，且没有触发器时，MySQL 会将 replace 操作转换为 update 操作。
解决方案
升级 MySQL 版本
升级到 MySQL 8.0 版本，该版本中 AUTO_INCREMENT 值已做持久化处理，更新操作会更新 auto_increment 为更大的值。
修改 AUTO_INCREMENT 值
通过巡检发现问题表，并使用 ALTER TABLE 语句修改 AUTO_INCREMENT 值。
禁用 REPLACE INTO 操作
业务侧避免使用 REPLACE INTO 或 INSERT...ON DUPLICATE KEY UPDATE。
避免在 REPLACE INTO 操作的表中增加其他唯一索引
这可以避免触发从库 auto_increment 异常问题。
