MySQL replace命令，不建议使用。

mysql replace操作导致主从自增主键不一致

今天在线上遇到一个问题，是由于replace语法导致的主从自增主键不一致问题，这里我模拟了一下，问题能够稳定复现。希望大家后续过程中，不要踩坑

01

问题还原

环境介绍：

MySQL版本5.7.18

关键参数介绍：

binlog_format：row

binlog_row_image:full

主库操作

主库上创建一个表test，并插入部分数据：

# 创建表
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的列：

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在遇到主键冲突或者唯一键冲突的时候，是先执行delete，然后再执行insert的。

所以我们查看表中的内容，可以看到，由于id=4的name列是'aaa', 和id=1的name列存在唯一键冲突，所以id=1的记录不存在了，取而代之的是id=4的记录。

此时我们查看主库的表结构：

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值;

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日志，可以看到：

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操作插入一个不冲突的新的数据记录，这个从库的自增值不就又同步了么。似乎对整个架构没什么影响？？？

这个想法是不对的。设想这样一个场景，如果这个时间点发生了主从切换，从库被提升为新主库，以我们上面的例子看：

此时新主库里面的数据记录如下：

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不一致，配置对应监控，第一时间发现问题，并解决问题。