MySQL replace操作导致主从自增主键不一致
今天在线上遇到一个问题，是由于replace语法导致的主从自增主键不一致问题，这里我模拟了一下，问题能够稳定复现。希望大家后续过程中，不要踩坑
01
问题还原
环境介绍：
MySQL版本5.7.18
关键参数介绍：
binlog_format：row
binlog_row_image:full
主库操作
主库上创建一个表test，并插入部分数据：
代码语言：javascript复制# 创建表
create table test1 (
id int not null auto_increment primary key, # 主键
name varchar(10) unique, # 唯一索引
age int );
Query OK, 0 rows affected (0.13 sec)
# 插入数据
insert into test1 values (1,'aaa',1),(2,'bbb',2);
Query OK, 2 rows affected (0.04 sec)
Records: 2 Duplicates: 0 Warnings: 0
# 利用replace插入数据
replace into test1 values (3,'ccc',3);
Query OK, 1 row affected (0.04 sec)
# 查看数据
select * from test1;
---- ------ ------
| id | name | age |
---- ------ ------
| 1 | aaa | 1 |
| 2 | bbb | 2 |
| 3 | ccc | 3 |
---- ------ ------
3 rows in set (0.03 sec)
此时可以看到，replace插入id=3的数据时候，是可以正常插入的，返回是1 row affected，代表有1行数据受到了影响。
然后我们再次利用replace语法插入id=4的列：
代码语言：javascript复制replace into test1 values (4,'aaa',4);
Query OK, 2 rows affected (0.03 sec)
select * from test1;
---- ------ ------
| id | name | age |
---- ------ ------
| 2 | bbb | 2 |
| 3 | ccc | 3 |
| 4 | aaa | 4 |
---- ------ ------
3 rows in set (0.04 sec)
注意，这里返回值是2行，为什么是2行？这就要用官方文档中的话来解释了：
REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted.
从上述描述中不难看出：replace在遇到主键冲突或者唯一键冲突的时候，是先执行delete，然后再执行insert的。
所以我们查看表中的内容，可以看到，由于id=4的name列是'aaa', 和id=1的name列存在唯一键冲突，所以id=1的记录不存在了，取而代之的是id=4的记录。
此时我们查看主库的表结构：
代码语言：javascript复制show create table test1G
*************************** 1. row ***************************
Table: test1
Create Table: CREATE TABLE `test1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(10) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4
1 row in set (0.14 sec)
我们可以看到，由于上次操作进行了删除再插入，所以主库的AUTO_INCREMENT已经变成了5了。
从库操作
我们再来看从库的数据记录和自增id值;
代码语言：javascript复制select * from test1;
---- ------ ------
| id | name | age |
---- ------ ------
| 2 | bbb | 2 |
| 3 | ccc | 3 |
| 4 | aaa | 4 |
---- ------ ------
3 rows in set (0.13 sec)
show create table test1G
*************************** 1. row ***************************
Table: test1
Create Table: CREATE TABLE `test1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(10) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4
1 row in set (0.13 sec)
可以看到，从库的AUTO_INCREMENT值是4。
可以看到2个问题：
1、从库的AUTO_INCREMENT值和主库AUTO_INCREMENT值不一致。
2、AUTO_INCREMENT的值代表下一个插入表的记录的默认id，但是我们的从库里已经存在id=4的记录
02
原因分析
其实产生这个问题的本质原因，是MySQL将这个replace语句的delete和insert操作，在binlog中合并成了一个update的语句，而这个update语句，只会更新记录中id列的值，没有主动更新AUTO_INCREMENT的值的（注意理解这句话），我们解析对应的binlog日志，可以看到：
代码语言：javascript复制replace id=3的记录，记录的binlog是insert操作
BEGIN
/*!*/;
# at 139995629
#220413 20:22:45 server id 2157944972 end_log_pos 139995681 CRC32 0xea7d7120 Table_map: `test`.`test1` mapped to number 153
# at 139995681
#220413 20:22:45 server id 2157944972 end_log_pos 139995729 CRC32 0x5b8b0ebc Write_rows: table id 153 flags: STMT_END_F
### INSERT INTO `test`.`test1`
### SET
### @1=3 /* INT meta=0 nullable=0 is_null=0 */
### @2='ccc' /* VARSTRING(40) meta=40 nullable=1 is_null=0 */
### @3=3 /* INT meta=0 nullable=1 is_null=0 */
# at 139995729
#220413 20:22:45 server id 2157944972 end_log_pos 139995760 CRC32 0xc69ecc26 Xid = 12340849656
COMMIT/*!*/;
replace id=4的记录，记录的binlog是update操作
BEGIN
/*!*/;
# at 139996477
#220413 20:22:58 server id 2157944972 end_log_pos 139996529 CRC32 0xb8805056 Table_map: `test`.`test1` mapped to number 153
# at 139996529
#220413 20:22:58 server id 2157944972 end_log_pos 139996591 CRC32 0x07d4f31b Update_rows: table id 153 flags: STMT_END_F
### UPDATE `test`.`test1`
### WHERE
### @1=1 /* INT meta=0 nullable=0 is_null=0 */
### @2='aaa' /* VARSTRING(40) meta=40 nullable=1 is_null=0 */
### @3=1 /* INT meta=0 nullable=1 is_null=0 */
### SET
### @1=4 /* INT meta=0 nullable=0 is_null=0 */
### @2='aaa' /* VARSTRING(40) meta=40 nullable=1 is_null=0 */
### @3=4 /* INT meta=0 nullable=1 is_null=0 */
# at 139996591
#220413 20:22:58 server id 2157944972 end_log_pos 139996622 CRC32 0xe0cf7229 Xid = 12340849733
COMMIT/*!*/;
在这个实验的过程中，我分别测试了MySQL8.0版本和MySQL5.7版本，发现MySQL8.0的版本，虽然binlog内容一致，但是更新了AUTO_INCREMENT的值。
这个现象，可以理解为MySQL 5.7 版本的一个bug。
03
潜在影响
可能你会想，如果主库此时利用replace操作插入一个不冲突的新的数据记录，这个从库的自增值不就又同步了么。似乎对整个架构没什么影响？？？
这个想法是不对的。设想这样一个场景，如果这个时间点发生了主从切换，从库被提升为新主库，以我们上面的例子看：
此时新主库里面的数据记录如下：
代码语言：javascript复制select * from test1;
---- ------ ------
| id | name | age |
---- ------ ------
| 2 | bbb | 2 |
| 3 | ccc | 3 |
| 4 | aaa | 4 |
---- ------ ------
3 rows in set (0.13 sec)
但是新主库的auto_increment值是4，意味着新主库上下一个不指定自增id的insert语句，一定会报主键冲突。因为他会默认生成自增id=4的记录，但是这条记录在表里面已经有了。
04
总结
既然上述auto_increment值不一致，主从切换之后会带来隐患，那我们应该如何杜绝？
1、升级MySQL版本到8.0版本。
2、业务侧杜绝replace这种非标准SQL语法，利用业务逻辑来判断数据冲突。
3、检测自增ID不一致，配置对应监控，第一时间发现问题，并解决问题。
