A brief analysis of MySQL replace into statement (i.)

This article mainly introduces the MySQL replace into statement (a), this article explains the principle of replace into, the use of the method and the use of the scene and the use of examples, the need for friends can refer to the

An introduction

In the author supports the business process, often encounters the development consultation replace into the use scene as well as the attention matter, here makes a summary. Make a description from the functional principle, performance and precautions.

Two principles

2.1 When there is a primary key in the table but there is no unique build.

Table structure

The code is as follows:

CREATE TABLE ' yy ' (

' ID ' bigint not NULL,

' Name ' varchar 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, then MySQL does the update operation.

The code is as follows:

### UPDATE Test.yy

### WHERE

### @1=1/* longint meta=0 nullable=0 is_null=0 * *

### @2= ' abc '/* varstring meta=60 nullable=1 is_null=0 * *

### SET

### @1=1/* longint meta=0 nullable=0 is_null=0 * *

### @2= ' CCC '/* varstring meta=60 nullable=1 is_null=0 * *

If the corresponding primary key value is not, then do insert operation replace into YY values (2, ' BBB ');

The code is as follows:

### INSERT into Test.yy

### SET

### @1=2/* longint meta=0 nullable=0 is_null=0 * *

### @2= ' BBB '/* varstring 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 a primary key and a unique key exist simultaneously in a table

The code is as follows:

CREATE TABLE ' yy ' (

' id ' int (one) not NULL DEFAULT ' 0 ',

' B ' int (one) DEFAULT NULL,

' C ' int (one) DEFAULT NULL

PRIMARY KEY (' a '),

UNIQUE KEY ' UK_BC ' (' B ', ' C ')

) Engine=innodb DEFAULT Charset=utf8

Scenario 1 PRIMARY key conflict

The code is as follows:

Root@test 04:37:18>replace into yy values (1,2,3);

Query OK, 1 row Affected (0.00 sec)

Root@test 04:37:37>replace into yy values (2,2,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 (1,2,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 deletes and inserts the table first, deleting the id=1 record and inserting the new id=1 record (1,2,5).

The code is as follows:

Binlog '

io5hvrowyhc+kwaaaeicaaaaamomaaaaaaeabhrlc3qaanl5aamdawmabg==

Io5hvrmwyhc+kgaaagwcaaaaamomaaaaaaaaa//4aqaaaaiaaaadaaaa

### 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//4aqaaaaiaaaafaaaa

'/*!*/;

### 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 Single build conflict

The 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 (5,3,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, when the unique key conflicts, the database updates the rows of the table's unique key (3,6), modifies the primary key to the value to be inserted, and id=4 changes to id=5.

The code is as follows:

Binlog '

ljfhvrowyhc+kwaaanoaaaaaamomaaaaaaeabhrlc3qaanl5aamdawmabg==

Ljfhvriwyhc+oaaaabibaaaaamomaaaaaaeaa///+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 Xid = 22962872

commit/*!*/

Scenario 3 A primary key and a unique key collide at the same time, if the primary key and unique of the value that you want to insert and the existing conflicts exist in the table.

The code is as follows:

Root@test 04:53:52>replace into yy values (1,3,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 to be inserted (1,3,6) primary key in the table id=1 value conflict, unique key (3,6) and table id=5 record conflict, MySQL processing, first delete the id=1 row, and then updated the id=5 row.

The code is as follows:

Binlog '

b5jhvrowyhc+kwaaajwbaaaaamomaaaaaaeabhrlc3qaanl5aamdawmabg==

B5jhvrmwyhc+kgaaamybaaaaamomaaaaaaaaa//4aqaaaaiaaaaeaaaa

### 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/*!*/

Three conclusions

When the table is in the replace into operation,

When there is no conflict, replace into is equivalent to an insert operation.

When there is a PK conflict is the first delete and insert, if the primary key is self-added, then the self-added primary key will do +1 operations. "5.5, 5.6 versions have been tested"

When there is a UK conflict, it is immediate update. , if the primary key is self increasing, the self-added primary key does +1 operations. "5.5, 5.6 versions have been tested"

Understand the above principles and conclusions, later encountered in replace into, I believe that readers can know how to choose, due to space limitations, subsequent articles will be based on the principle of replace into, the production process to tell the points of attention.