MySQL replace into Statement Analysis (2), mysqlreplace

MySQL replace into Statement Analysis (2), mysqlreplace

1. Introduction

The previous article introduced the basic principles of replace. This chapter uses an example to describe the potential data quality risks caused by replace into. When a table involved in the replace into operation contains an auto-incrementing primary key, after the master-slave switchover, data overwrites and other inconsistencies may occur.

Case Study

Operate on the master database
Copy codeThe Code is as follows:
Root @ test 12:36:51> show create table t1 \ G
* *************************** 1. row ***************************
Table: t1
Create Table: create table 't1 '(
'Id' int (11) not null AUTO_INCREMENT,
'Name' varchar (20) default null,
Primary key ('id '),
Unique key 'name' ('name ')
) ENGINE = InnoDB default charset = utf8
1 row in set (0.00 sec)
Root @ test 12:37:41> insert into t1 (name) values ('A ')

In this case, check that the table structure of t1 on the master and slave databases is the same, and that of AUTO_INCREMENT is both 2.
Copy codeThe Code is as follows:
Root @ test 12:37:51> show create table t1 \ G
* *************************** 1. row ***************************
Table: t1
Create Table: create table 't1 '(
'Id' int (11) not null AUTO_INCREMENT,
'Name' varchar (20) default null,
Primary key ('id '),
Unique key 'name' ('name ')
) ENGINE = InnoDB AUTO_INCREMENT = 2 default charset = utf8
1 row in set (0.00 sec)
Perform the replace into operation on the master database
Root @ test 12:37:58> replace into t1 (name) values ('A ');
Root @ test 12:38:40> replace into t1 (name) values ('A ');
Root @ test 12:38:49> select * from t1;
+ ---- + ------ +
| Id | name |
+ ---- + ------ +
| 3 | a |
+ ---- + ------ +
1 row in set (0.00 sec)

Check the table t1 structure in the Master/Slave database. Note that AUTO_INCREMENT = 4
Copy codeThe Code is as follows:
Root @ test 12:38:51> show create table t1 \ G
* *************************** 1. row ***************************
Table: t1
Create Table: create table 't1 '(
'Id' int (11) not null AUTO_INCREMENT,
'Name' varchar (20) default null,
Primary key ('id '),
Unique key 'name' ('name ')
) ENGINE = InnoDB AUTO_INCREMENT = 4 default charset = utf8
1 row in set (0.00 sec)

Table Structure of table t1 on the slave database, AUTO_INCREMENT = 2
Copy codeThe Code is as follows:
Root @ test 12:39:35> show create table t1 \ G
* *************************** 1. row ***************************
Table: t1
Create Table: create table 't1 '(
'Id' int (11) not null AUTO_INCREMENT,
'Name' varchar (20) default null,
Primary key ('id '),
Unique key 'name' ('name ')
) ENGINE = InnoDB AUTO_INCREMENT = 2 default charset = utf8
1 row in set (0.00 sec)
Root @ test 12:39:43> select * from t1;
+ ---- + ------ +
| Id | name |
+ ---- + ------ +
| 3 | a |
+ ---- + ------ +
1 row in set (0.00 sec)

[Analysis]

Table Structure AUTO_INCREMENT = 2 for table t1 and table structure AUTO_INCREMENT = 4 for table t1 in the master database. originally, the replace operation performed the delete + insert operation in case of a unique key conflict with the auto-increment primary key, but it was recorded as the update operation when the binlog was recorded, the update operation does not involve the modification of auto_increment. After binlog is applied to the slave database, the auto_increment attribute of the slave database table remains unchanged.

Three risk points:

If a master-slave switchover occurs between the master and slave databases and the slave database changes to the original master database, what will happen next according to the original business logic?
Copy codeThe Code is as follows:
Root @ test 12:40:46> replace into t1 (name) values ('A ');
Query OK, 2 rows affected (0.00 sec)
Root @ test 12:40:48> select * from t1;
+ ---- + ------ +
| Id | name |
+ ---- + ------ +
| 2 | a | --- the id is changed from 3 to 2.
+ ---- + ------ +
1 row in set (0.00 sec)

If table t1 already has multiple records, after the master-slave switchover, primary key conflicts will occur when the application writes a new master database. This is left for your own test. Pai_^

4. Solution

We recommend that you use the insert into table values (,...) on duplicate key update syntax structure to meet your business needs. Replace into to avoid the inconsistency of the primary key with auto-incrementing primary key.
Copy codeThe Code is as follows:
Root @ test 01:14:28> insert into t1 (name) values ('A ');
Query OK, 1 row affected (0.00 sec)
Root @ test 01:14:51> insert into t1 (name) values ('B ');
Query OK, 1 row affected (0.00 sec)
Root @ test 01:14:54> insert into t1 (name) values ('C ');
Query OK, 1 row affected (0.00 sec)
Root @ test 01:14:57> select * from t1;
+ ---- + ------ +
| Id | name |
+ ---- + ------ +
| 1 | a |
| 2 | B |
| 3 | c |
+ ---- + ------ +
3 rows in set (0.00 sec)
Root @ test 01:16:17> show create table t1 \ G
* *************************** 1. row ***************************
Table: t1
Create Table: create table 't1 '(
'Id' int (11) not null AUTO_INCREMENT,
'Name' varchar (20) default null,
Primary key ('id '),
Unique key 'name' ('name ')
) ENGINE = InnoDB AUTO_INCREMENT = 4 default charset = utf8
1 row in set (0.00 sec)
Root @ test 01:16:26> insert into tt (name) values ('A') on duplicate key update name = 'a ';
Query OK, 1 row affected (0.00 sec)
Root @ test 01:17:09> show create table t1 \ G
* *************************** 1. row ***************************
Table: t1
Create Table: create table 't1 '(
'Id' int (11) not null AUTO_INCREMENT,
'Name' varchar (20) default null,
Primary key ('id '),
Unique key 'name' ('name ')
) ENGINE = InnoDB AUTO_INCREMENT = 4 default charset = utf8
1 row in set (0.00 sec)

Summary

Because the replace into operation will modify the value of the primary key in case of a primary key conflict, if the business logic is strongly dependent on the auto-increment ID, replace is definitely not used. This is not recommended in normal environments, because the replace into operation may cause the primary key to be reorganized. we recommend that you use insert into table values (,....) on duplicate key update to meet business needs.