replace into导致的主键冲突

原创

食品安全辛吉飞 2023-11-08 11:07:04 博主文章分类：Mysql ©著作权

文章标签 主键 mysql 自增 replace into 文章分类 MySQL 数据库 指尖人生

©著作权归作者所有：来自51CTO博客作者食品安全辛吉飞的原创作品，请联系作者获取转载授权，否则将追究法律责任

概念描述

mysql5.7版本在使用replace into语法往表中插入记录时，如果违反主键或唯一键约束，则会进行数据"覆盖"，但这种"覆盖"的规则，会引起一定的问题。下面从2方面来说明问题：

replace into数据"覆盖"规则
replace into主从切换后主键冲突

测试验证

replace into数据"覆盖"规则

假如一张表上同时存在主键和唯一键，我们分5种情况进行分析：

主键不冲突 + 唯一键不冲突
主键不冲突 + 唯一键冲突
主键冲突 + 唯一键不冲突
主键冲突 + 唯一键冲突（同一行）
主键冲突 + 唯一键冲突（两行）

创建测试数据：

mysql> use modb;
Database changed
mysql> drop table if exists t_replaceinto;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE t_replaceinto (
    ->   `id` int NOT NULL AUTO_INCREMENT,
    ->   `c` int DEFAULT NULL,
    ->   PRIMARY KEY (`id`),
    ->   UNIQUE KEY `c` (`c`)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.03 sec)

情况1：主键不冲突 + 唯一键不冲突

#插入2条不存在的记录
mysql> replace into t_replaceinto(c) values(1),(2);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

#查看相关binlog
### INSERT INTO `modb`.`t_replaceinto`
### SET
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
###   @2=1 /* INT meta=0 nullable=1 is_null=0 */
### INSERT INTO `modb`.`t_replaceinto`
### SET
###   @1=2 /* INT meta=0 nullable=0 is_null=0 */
###   @2=2 /* INT meta=0 nullable=1 is_null=0 */

从结果得出：相当于insert操作。

情况2：主键不冲突 + 唯一键冲突

#插入c=2的记录，让唯一键冲突
mysql> replace into t_replaceinto(c) values(2);
Query OK, 2 rows affected (0.02 sec)

#查看相关binlog
### UPDATE `modb`.`t_replaceinto`
### WHERE
###   @1=2 /* INT meta=0 nullable=0 is_null=0 */
###   @2=2 /* INT meta=0 nullable=1 is_null=0 */
### SET
###   @1=3 /* INT meta=0 nullable=0 is_null=0 */
###   @2=2 /* INT meta=0 nullable=1 is_null=0 */

#查看自增值的变化
mysql> show create table t_replaceinto\G
*************************** 1. row ***************************
       Table: t_replaceinto
Create Table: CREATE TABLE `t_replaceinto` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `c` (`c`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4

从结果得出：唯一键值不变，自增主键+1。从"2 rows affected"可知，此操作逻辑上相当于delete+insert操作，但binlog实际记录为1次update操作。

情况3：主键冲突 + 唯一键不冲突

#插入id=1的记录，让主键冲突
mysql> replace into t_replaceinto(id, c) values(1, 11);
Query OK, 2 rows affected (0.01 sec)

#查看相关binlog
### DELETE FROM `modb`.`t_replaceinto`
### WHERE
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
###   @2=1 /* INT meta=0 nullable=1 is_null=0 */
### INSERT INTO `modb`.`t_replaceinto`
### SET
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
###   @2=11 /* INT meta=0 nullable=1 is_null=0 */

从结果得出：先删除主键冲突的记录，再插入新记录。从"2 rows affected"可知，此操作相当于delete+insert操作，binlog中记录为delete和insert操作。

情况4：主键冲突 + 唯一键冲突（同一行）

#再插入1条新数据测试
mysql> insert into t_replaceinto(id, c) values(9, 9);
Query OK, 1 row affected (0.00 sec)

#让主键和唯一键均冲突
mysql> replace into t_replaceinto(id, c) values(9, 9);
Query OK, 2 rows affected (0.01 sec)

#查看相关binlog
### DELETE FROM `modb`.`t_replaceinto`
### WHERE
###   @1=9 /* INT meta=0 nullable=0 is_null=0 */
###   @2=9 /* INT meta=0 nullable=1 is_null=0 */
### INSERT INTO `modb`.`t_replaceinto`
### SET
###   @1=9 /* INT meta=0 nullable=0 is_null=0 */
###   @2=9 /* INT meta=0 nullable=1 is_null=0 */

从结果得出：同情况3

情况5：主键冲突 + 唯一键冲突（两行）

#查看当前数据
mysql> select * from t_replaceinto;
+----+------+
| id | c    |
+----+------+
|  3 |    2 |
|  9 |    9 |
|  1 |   11 |
+----+------+
3 rows in set (0.00 sec)

#让id=9和c=11两行记录均冲突
mysql> replace into t_replaceinto(id, c) values(9, 11);
Query OK, 3 rows affected (0.02 sec)

#查看相关binlog
### DELETE FROM `modb`.`t_replaceinto`
### WHERE
###   @1=9 /* INT meta=0 nullable=0 is_null=0 */
###   @2=9 /* INT meta=0 nullable=1 is_null=0 */
### UPDATE `modb`.`t_replaceinto`
### WHERE
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
###   @2=11 /* INT meta=0 nullable=1 is_null=0 */
### SET
###   @1=9 /* INT meta=0 nullable=0 is_null=0 */
###   @2=11 /* INT meta=0 nullable=1 is_null=0 */

从结果得出：先按主键delete冲突记录，再按唯一键update冲突记录。注意，影响行数为：“3 rows affected”。

现总结如下：

当表中自增主键和唯一键都存在时：

主键不冲突 + 唯一键不冲突，相当于insert。
主键不冲突 + 唯一键冲突，update唯一键冲突的记录，且自增主键值+1。
主键冲突 + 唯一键不冲突，先删除主键冲突的记录，再插入新记录。
主键冲突 + 唯一键冲突（同一行），先删除主键冲突的记录，再插入新记录。
主键冲突 + 唯一键冲突（两行），先删除主键冲突的记录，再update唯一键冲突记录。

当表中只存在主键时（这个大家自行测试）：

主键冲突，相当于update操作
主键不冲突，相当于insert操作

从实验和总结中可以看出：主键冲突时，基本规则是先delete再insert记录；唯一键冲突时，基本规则是进行update操作，但自增主键值会更新为当前的AUTO_INCREMENT值。

replace into主从切换后主键冲突

从上面的测试可以看出，replace into语法可能导致自增主键的值发生变化，由于binlog一般都使用ROW模式，所以主从数据同步是正常的。但这儿会有一个潜在的问题，那就是：表的AUTO_INCREMENT值主从可能会不一致。当主从因故障切换后，从库再次插入数据时，生成的主键自增id，就有可能与已存在的主键值冲突。测试如下：

#主库：插入3条记录，并在最后1条记录的唯一键上做replace into
mysql> truncate table t_replaceinto;
Query OK, 0 rows affected (0.04 sec)

mysql> replace into t_replaceinto(c) values(1),(2),(3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> replace into t_replaceinto(c) values(3);
Query OK, 2 rows affected (0.00 sec)

#主库上的AUTO_INCREMENT=5
mysql> show create table t_replaceinto\G
*************************** 1. row ***************************
       Table: t_replaceinto
Create Table: CREATE TABLE `t_replaceinto` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `c` (`c`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

mysql> select * from t_replaceinto;
+----+------+
| id | c    |
+----+------+
|  1 |    1 |
|  2 |    2 |
|  4 |    3 |
+----+------+
3 rows in set (0.00 sec)

#从库：复制正常，但表的AUTO_INCREMENT比主库小1
mysql> select * from t_replaceinto;
+----+------+
| id | c    |
+----+------+
|  1 |    1 |
|  2 |    2 |
|  4 |    3 |
+----+------+
3 rows in set (0.00 sec)

#从库上的AUTO_INCREMENT=4
mysql> show create table t_replaceinto\G
*************************** 1. row ***************************
       Table: t_replaceinto
Create Table: CREATE TABLE `t_replaceinto` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `c` (`c`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

#当在从库插入1条新记录时，报主键冲突。因为按最新AUTO_INCREMENT生成的id值已经存在。
mysql> insert into t_replaceinto(c) values(999);
ERROR 1062 (23000): Duplicate entry '4' for key 'PRIMARY'

#或者：用replace into更新一条旧记录时，也报主键冲突。
mysql> insert into t_replaceinto(c) values(2);
ERROR 1062 (23000): Duplicate entry '4' for key 'PRIMARY'

从上面结果可以得出：replace into语法在自增主键+唯一键的情况下，可能会导致主从AUTO_INCREMENT值不一致。从而主从切换后，造成自增主键冲突。

知识总结

mysql5.7版本，如果表存在自增主键和唯一键时，当使用replace into语法处理唯一键冲突时，会导致主从的AUTO_INCREMENT值不一致，当主从切换后，从库可能出现唯一键冲突。
mysql8.0版本，由于持久化了AUTO_INCREMENT值，故AUTO_INCREMENT值可以同步至从库，故不存在这个问题。