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
Copy Code code 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.
Copy Code code 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 ');
Copy Code code 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
Copy Code code 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
Copy Code code 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).
Copy Code code 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
Copy Code code 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.
Copy Code code 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 simultaneously, if the primary key and unique of the value you want to insert and the existing conflicts in the table already exist.
Copy Code code 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.
Copy Code code 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/* Me Ta=0 nullable=1 is_null=0 */
### @3=4/* INT meta=0 nullable=1 is_null=0/
B5jhvriwyhc+oaaaap4baaaaamomaaaaaa Eaa///+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 nu Llable=0 is_null=0 */
### @2=3/* INT meta=0 nullable=1 is_null=0/
### @3=6/* INT meta=0 nullable=1 =0 */
# at 510
#150524 16:55:35 server ID 3195035798 end_log_pos 537 = Xid
22962904
/div>
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.
