MySQL replace into那些隐藏的风险

目录

• replace into时存在主键冲突
• replace into时存在唯一索引冲突
• replace into时存在主键冲突&唯一索引冲突
• 存在问题
• 结论

MySQL中 replace into是否像预期样：若表中有已经存在的数据，则把已经存在的数据删除，插入新数据？

准备数据

CREATE TABLE `test_replace` ( `id` int(11) NOT NULL AUTO_INCREMENT, `str1` char(10) DEFAULT NULL, `str2` char(10) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `uqx_str` (`str1`)) ENGINE=InnoDB;insert into test_replace(id,str1,str2) values(2,1234,\'aaabbbb\'),(4,123456,\'bbbbxxxx\');select * from test_replace;+----+--------+----------+| id | str1  | str2   |+----+--------+----------+| 2 | 1234  | aaabbbb || 4 | 123456 | bbbbxxxx |+----+--------+----------+2 rows in set (0.00 sec)

replace into时存在主键冲突

replace into test_replace(id,str1,str2) values(2,\'xxxx\',\'yyy\');Query OK, 2 rows affected (0.00 sec)select * from test_replace;+----+--------+----------+| id | str1  | str2   |+----+--------+----------+| 2 | xxxx  | yyy   || 4 | 123456 | bbbbxxxx |+----+--------+----------+

binlog中记录内容

replace into时存在唯一索引冲突

replace into test_replace(id,str1,str2) values(8,\'xxxx\',\'ppppp\');Query OK, 2 rows affected (0.01 sec)select * from test_replace;+----+--------+----------+| id | str1  | str2   |+----+--------+----------+| 4 | 123456 | bbbbxxxx || 8 | xxxx  | ppppp  |+----+--------+----------+show create table `test_replace`\\G*************************** 1. row ***************************   Table: test_replaceCreate Table: CREATE TABLE `test_replace` ( `id` int(11) NOT NULL AUTO_INCREMENT, `str1` char(10) DEFAULT NULL, `str2` char(10) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `uqx_str` (`str1`)) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8###下一次插入非冲突数据时自增主键为9

binlog中记录内容

replace into时存在主键冲突&唯一索引冲突

replace into test_replace(id,str1,str2) values(8,\'123456\',\'主键和唯一索引冲突\');Query OK, 3 rows affected (0.01 sec)####插入了这条数据后，原来的两条数据(主键4，8)变成了一条（主键 8），数据丢失！！！select * from test_replace;+----+--------+-----------------------------+| id | str1  | str2            |+----+--------+-----------------------------+| 8 | 123456 | 主键和唯一索引冲突     |+----+--------+-----------------------------+show create table test_replace\\G*************************** 1. row ***************************   Table: test_replaceCreate Table: CREATE TABLE `test_replace` ( `id` int(11) NOT NULL AUTO_INCREMENT, `str1` char(10) DEFAULT NULL, `str2` char(10) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `uqx_str` (`str1`)) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf81 row in set (0.00 sec)

binlog中记录内容

存在问题

场景2：

• replace into时存在唯一索引冲突：会把冲突数据删掉，插入新数据，但binlog中记录的是update格式，从库同步update binlog不会更新该表的自增主键，主库自增主键9，从库自增主键8，若主从库角色发生切换后，新主库会存在主键冲突问题
• replace into唯一索引冲突会导致下游大数据hive（同步binlog写入hive中）中数据和mysql中数据不一致问题（hive基于唯一主键进行处理，mysql一条数据，hive中多条数据情况）

场景3:

• replace into时存在主键冲突&唯一索引冲突：会把表中主键冲突和唯一索引冲突的数据都删掉，再插入新数据，丢失一条数据

经验证：mysql5.7 和mysql8.0均是上诉情况

结论

replace into在只存在主键冲突时会按预期的那样；若只有唯一索引冲突时 主从切换后导致新主库主键冲突错误、下游大数据数据不一致问题；同时存在主键冲突和唯一索引冲突可能会导致丢失数据。业务上不应使用replace into，应该在代码对唯一数据冲突作处理