【MySQL】MySQL 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)
 

1、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中记录内容

 

 

2、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_replace 
Create 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中记录内容

 

 

3、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_replace 
Create 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 
1 row in set (0.00 sec)
 
binlog中记录内容

 

 

4、存在问题

场景2:
replace into时存在唯一索引冲突:会把冲突数据删掉,插入新数据,但binlog中记录的是update格式,从库同步update binlog不会更新该表的自增主键,主库自增主键9,从库自增主键8,若主从库角色发生切换后,新主库会存在主键冲突问题
replace into唯一索引冲突会导致下游大数据hive中数据和mysql中数据不一致问题(hive基于唯一主键进行处理,mysql一条数据,hive中多条数据情况)
 
场景3:
replace into时存在主键冲突&唯一索引冲突:会把表中主键冲突和唯一索引冲突的数据都删掉,再插入新数据,丢失一条数据
 

5、结论

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

 

 

posted @ 2023-08-31 20:06  Angel挤一挤  阅读(65)  评论(0编辑  收藏  举报