MySQL replace into Statement Analysis (1), mysqlreplace

MySQL replace into Statement Analysis (1), mysqlreplace

1. Introduction

I often encounter development consultation replace into application scenarios and precautions when supporting my business. Here is a summary. The functional principle, performance, and precautions are described.

Principle 2

2.1 When a table has a primary key but does not have a unique primary key.
Table Structure
Copy codeThe Code is as follows:
Create table 'yy '(
'Id' bigint (20) not null,
'Name' varchar (20) default null,
Primary key ('id ')
) ENGINE = InnoDB default charset = utf8;
Root @ test 02:43:58> insert into yy values (1, 'abc ');
Query OK, 1 row affected (0.00 sec)
Root @ test 02:44:25> replace into yy values (2, 'bbb ');
Query OK, 1 row affected (0.00 sec)
Root @ test 02:55:42> select * from yy;
+ ---- + ------ +
| Id | name |
+ ---- + ------ +
| 1 | abc |
| 2 | bbb |
+ ---- + ------ +
2 rows in set (0.00 sec)
Root @ test 02:55:56> replace into yy values (1, 'ccc ');
Query OK, 2 rows affected (0.00 sec)

If the primary key value already exists, MySQL performs the update operation.
Copy codeThe Code is as follows:
### UPDATE test. yy
### WHERE
###@ 1 = 1/* LONGINT meta = 0 nullable = 0 is_null = 0 */
###@ 2 = 'abc'/* VARSTRING (60) meta = 60 nullable = 1 is_null = 0 */
### SET
###@ 1 = 1/* LONGINT meta = 0 nullable = 0 is_null = 0 */
###@ 2 = 'ccc '/* VARSTRING (60) meta = 60 nullable = 1 is_null = 0 */

If the corresponding primary key value does not exist, perform the insert operation replace into yy values (2, 'bbb ');
Copy codeThe Code is as follows:
### Insert into test. yy
### SET
###@ 1 = 2/* LONGINT meta = 0 nullable = 0 is_null = 0 */
###@ 2 = 'bbb '/* VARSTRING (60) meta = 60 nullable = 1 is_null = 0 */
# At 623
#140314 2:55:42 server id 136403306 end_log_pos 650 Xid = 6090885569

2.2 When the primary key and unique key both exist in the table
Copy codeThe Code is as follows:
Create table 'yy '(
'Id' int (11) not null default \ '0 \',
'B' int (11) DEFAULT NULL,
'C' int (11) DEFAULT NULL
Primary key ('A '),
Unique key 'uk _ bc' ('B', 'C ')
) ENGINE = InnoDB default charset = utf8

Case 1 primary key conflict
Copy codeThe Code is as follows:
Root @ test 04:37:18> replace into yy values (, 3 );
Query OK, 1 row affected (0.00 sec)
Root @ test 04:37:37> replace into yy values (, 4 );
Query OK, 1 row affected (0.00 sec)
Root @ test 04:38:05> select * from yy;
+ ---- + ------ +
| Id | B | c |
+ ---- + ------ +
| 1 | 2 | 3 |
| 2 | 2 | 4 |
+ ---- + ------ +
2 rows in set (0.00 sec)
Root @ test 04:38:50> replace into yy values (, 5 );
Query OK, 2 rows affected (0.00 sec)
Root @ test 04:38:58> select * from yy;
+ ---- + ------ +
| Id | B | c |
+ ---- + ------ +
| 2 | 2 | 4 |
| 1 | 2 | 5 |
+ ---- + ------ +
2 rows in set (0.00 sec)

When a primary key conflict occurs, the database first deletes the table and then inserts it, that is, first deletes the records with id = 1, and then inserts a new record with id = 1 (, 5 ).
Copy codeThe Code is as follows:
Binlog'
Io5hVROWYHC + kwaaaeicaaamomaaaaaaeabhrlc3qaanl5aamdawmabg =
Io5hVRmWYHC + KgAAAGwCAAAAAMoMAAAAAAAAA // 4 AQAAAAIAAAADAAAA
### Delete from test. yy
### WHERE
###@ 1 = 1/* INT meta = 0 nullable = 0 is_null = 0 */
###@ 2 = 2/* INT meta = 0 nullable = 1 is_null = 0 */
###@ 3 = 3/* INT meta = 0 nullable = 1 is_null = 0 */
Io5hVReWYHC + KgAAAJYCAAAAAMoMAAAAAAEAA // 4 AQAAAAIAAAAFAAAA
'/*! */;
### Insert into test. yy
### SET
###@ 1 = 1/* INT meta = 0 nullable = 0 is_null = 0 */
###@ 2 = 2/* INT meta = 0 nullable = 1 is_null = 0 */
###@ 3 = 5/* INT meta = 0 nullable = 1 is_null = 0 */
# At 662
#150524 16:38:58 server id 3195035798 end_log_pos 689 Xid = 22962508
COMMIT /*! */

Scenario 2 unique conflict
Copy codeThe Code is as follows:
Root @ test 04:48:30> select * from yy;
+ ---- + ------ +
| Id | B | c |
+ ---- + ------ +
| 1 | 2 | 4 |
| 2 | 2 | 5 |
| 3 | 3 | 5 |
| 4 | 3 | 6 |
+ ---- + ------ +
4 rows in set (0.00 sec)
Root @ test 04:53:21> replace into yy values (, 6 );
Query OK, 2 rows affected (0.00 sec)
Root @ test 04:53:40> select * from yy;
+ ---- + ------ +
| Id | B | c |
+ ---- + ------ +
| 1 | 2 | 4 |
| 2 | 2 | 5 |
| 3 | 3 | 5 |
| 5 | 3 | 6 |
+ ---- + ------ +
4 rows in set (0.00 sec)

The primary key does not conflict. In case of a unique key conflict, the database updates the rows with the unique key (3, 6) and changes the primary key to the value to be inserted. id = 4 is changed to id = 5.
Copy codeThe Code is as follows:
BINLOG \'
LJFhVROWYHC + kwaaanoaaaamomaaaaeabhrlc3qaanl5aamdawmabg =
LJFhVRiWYHC + oaaaabibaaamomaaaaaaeaa // + AQAAAADAAAABgAAAPgFAAAAAwAAAAYAAAA =
\'/*! */;
### UPDATE test. yy
### WHERE
###@ 1 = 4/* INT meta = 0 nullable = 0 is_null = 0 */
###@ 2 = 3/* INT meta = 0 nullable = 1 is_null = 0 */
###@ 3 = 6/* INT meta = 0 nullable = 1 is_null = 0 */
### SET
###@ 1 = 5/* INT meta = 0 nullable = 0 is_null = 0 */
###@ 2 = 3/* INT meta = 0 nullable = 1 is_null = 0 */
###@ 3 = 6/* INT meta = 0 nullable = 1 is_null = 0 */
# At 274
#150524 16:53:40 server id 3195035798 end_log_pos 301 Xid = 22962872
COMMIT /*! */

Case 3: The primary key conflicts with the unique key at the same timeIf the primary key of the value to be inserted conflicts with the unique one that already exists in the table.
Copy codeThe Code is as follows:
Root @ test 04:53:52> replace into yy values (, 6 );
Query OK, 3 rows affected (0.00 sec) --- note that the number of rows affected here is 3
Root @ test 04:55:35> select * from yy;
+ ---- + ------ +
| Id | B | c |
+ ---- + ------ +
| 2 | 2 | 5 |
| 3 | 3 | 5 |
| 1 | 3 | 6 |
+ ---- + ------ +
3 rows in set (0.00 sec)

The value (, 6) of the primary key to be inserted conflicts with the value id = 1 In the table. The unique key () conflicts with the record id = 5 in the table. During MySQL processing, delete the row with id = 1 first, and then update the row with id = 5.
 Copy codeThe Code is as follows:
BINLOG \'
B5JhVROWYHC + kwaaajwbaaaaamomaaaaeabhrlc3qaanl5aamdawmabg =
B5JhVRmWYHC + KgAAAMYBAAAAAMoMAAAAAAAAA // 4 AQAAAAIAAAAEAAAA
### Delete from test. yy
### WHERE
###@ 1 = 1/* INT meta = 0 nullable = 0 is_null = 0 */
###@ 2 = 2/* INT meta = 0 nullable = 1 is_null = 0 */
###@ 3 = 4/* INT meta = 0 nullable = 1 is_null = 0 */
B5JhVRiWYHC + OAAAAP4BAAAAAMoMAAAAAAEAA // + AUAAAADAAAABgAAAPgBAAAAAwAAAAYAAAA =
\'/*! */;
### UPDATE test. yy
### WHERE
###@ 1 = 5/* INT meta = 0 nullable = 0 is_null = 0 */
###@ 2 = 3/* INT meta = 0 nullable = 1 is_null = 0 */
###@ 3 = 6/* INT meta = 0 nullable = 1 is_null = 0 */
### SET
###@ 1 = 1/* INT meta = 0 nullable = 0 is_null = 0 */
###@ 2 = 3/* INT meta = 0 nullable = 1 is_null = 0 */
###@ 3 = 6/* INT meta = 0 nullable = 1 is_null = 0 */
# At 510
#150524 16:55:35 server id 3195035798 end_log_pos 537 Xid = 22962904
COMMIT /*! */

Conclusion 3

When performing the replace into operation on a table,
If there is no conflict, replace into is equivalent to the insert operation.
When a pk conflict exists, delete the primary key before insert. If the primary key is auto-incrementing, the auto-incrementing primary key performs the + 1 operation. [All Versions 5.5 and 5.6 have been tested]
If a uk conflict exists, update directly ., If the primary key is auto-incrementing, the auto-incrementing primary key performs the + 1 operation. [All Versions 5.5 and 5.6 have been tested]

After understanding the above principles and conclusions, we will encounter replace into again in the future. I believe that readers can know how to choose it. Due to space restrictions, subsequent articles will be based on the replace into principle, describes precautions during the production process.