MySQL replace into those hidden risks

---

Is replace into in MySQL as expected: if there is existing data in the table, delete the existing data and insert new data?

Prepare data

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)

Primary key conflict during 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 | 
+----+--------+----------+

Contents recorded in binlog

Unique index conflict during 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
###The next time you insert non conflicting data, the auto increment primary key is 9

Contents recorded in binlog

Primary key Conflict & replace unique index conflict

replace into test_ Replace (ID, STR1, STR2) values (8, '123456', 'primary key and unique index 
Protrusion '); 
Query OK, 3 rows affected (0.01 sec)

####After inserting this piece of data, the original two pieces of data (primary key 4 and 8) become one (primary key 8), and the data is lost!!!
select * from test_replace; 
+----+--------+-----------------------------+ 
| id | str1   | str2                        | 
+----+--------+-----------------------------+ 
|8 | 123456 | conflict between primary key and unique index| 
+----+--------+-----------------------------+

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)

Contents recorded in binlog

Existing problems

Scenario 2:

• There is a unique index conflict during replace into: the conflicting data will be deleted and new data will be inserted, but the update format is recorded in binlog. When updating binlog from the database, the self incremented primary key of the table will not be updated, and the primary database will be self incrementedPrimary key 9, auto increment from libraryPrimary key 8, if the role of master-slave database is switched, the new master database will have primary key conflict
• Replace into unique index conflict will lead to inconsistency between data in downstream big data hive (synchronous binlog written to hive) and data in MySQL (hive is processed based on unique primary key, one piece of data in MySQL and multiple pieces of data in hive)

Scenario 3:

• Primary key Conflict & unique index conflict during replace into: delete the data with primary key conflict and unique index conflict in the table, insert new data, and lose a piece of data

Scene 2 and Scene 3 we are online mysql5 7. The environment is flat~
Verified: mysql5 7 and mysql8 0 is an appeal

conclusion

Replace into when there is only primary key conflict, it will be as expected; If there is only one index conflict, the primary key conflict error of the new primary database and the inconsistency of downstream big data will be caused after the master-slave switch; There are both primary key conflicts and unique index conflicts, which may lead to data loss. Replace into should not be used in business. Unique data conflicts should be handled in code