Analysis of MySQL replace into statement (II.)

This article mainly introduces the MySQL replace into the sentence analysis (ii), this article focuses on a number of special case analysis, the need for friends can refer to the

An introduction

The basic principle of replace into is introduced in the previous article. This chapter provides an example of the potential data quality risk associated with the replace into, and when a table involving a replace into operation contains a self-added primary key, the primary switch causes inconsistencies in data coverage, and so on.

Two cases analysis

Operating on the main library

The 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 (one) not NULL auto_increment,

' Name ' varchar 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 ')

At this point, check the T1 table structure on the primary repository is the same, Auto_increment is 2.

The 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 (one) not NULL auto_increment,

' Name ' varchar 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 a replace into operation on the main library

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)

At this point, check the T1 table structure in the primary repository, note auto_increment=4

The 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 (one) not NULL auto_increment,

' Name ' varchar DEFAULT NULL,

PRIMARY KEY (' id '),

UNIQUE KEY ' name ' (' name ')

) Engine=innodb auto_increment=4 DEFAULT Charset=utf8

1 row in Set (0.00 sec)

T1 the table structure from the library, auto_increment=2

The 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 (one) not NULL auto_increment,

' Name ' varchar 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 T1 the table structure auto_increment=2 and the auto_increment=4 of the T1 table structure on the main library. The original replace operation is Delete+insert when a unique key conflict is encountered while the primary key is being added. However, when recording binlog, it is logged as an update operation and the update operation does not involve auto_increment modifications. After the Binlog is applied, the Auto_increment property of the table for the standby is unchanged.

Three risk points:

If the main repository occurs when the master and slave switch, the backup library into the original main library, according to the original business logic down what will happen?

The 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 changed from 3 to 2.

+----+------+

1 row in Set (0.00 sec)

If there are many records in the T1 table, after the master-slave switch, the application write a new main library will have a primary key conflict, this left to the reader to test their own. ^_^

Four ways to solve the problem

Insert into table values (,....) on duplicate key update syntax structure is recommended to address business requirements. To avoid the master-slave inconsistencies in the replace into the table containing the self-added primary key.

The 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 (one) not NULL auto_increment,

' Name ' varchar 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 the 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 (one) not NULL auto_increment,

' Name ' varchar DEFAULT NULL,

PRIMARY KEY (' id '),

UNIQUE KEY ' name ' (' name ')

) Engine=innodb auto_increment=4 DEFAULT Charset=utf8

1 row in Set (0.00 sec)

Five summary

Because the replace into operation modifies the value of the primary key when it encounters a primary key conflict, if the business logic relies strongly on the ID, never replace, and the normal environment does not recommend it, because the replace into operation may cause the primary key to be organized. Recommended use Insert into table values (,....) on duplicate key update to address business requirements.