Use MySQL replace statement with caution

Grammar:

REPLACE [Low_priority | DELAYED]    [into] tbl_name    [PARTITION (Partition_name,...)]     [(Col_name,...)]    {VALUES | VALUE} ({expr | DEFAULT},...), (...),... Or:replace [Low_priority | DELAYED]    [into] tbl_name    [PARTITION (Partition_name,...)]     SET col_name={expr | DEFAULT}, ... Or:replace [Low_priority | DELAYED]    [into] tbl_name    [PARTITION (Partition_name,...)]      [(Col_name,...)]    SELECT ...

　　

Principle

Replace works somewhat like an insert, except that if a row has a primary key or a unique index in the table, the old row is deleted and the new row is inserted. Such as:

[Email protected] 03:23:55>show CREATE TABLE lingluo\g*************************** 1.  Row ***************************       table:lingluocreate table:create Table ' Lingluo ' (  ' a ' int (one-by-one) not NULL DEFAULT ' 0 ',  ' b ' int (one) default null,  ' C ' int (one) default null,  ' d ' int (one) default null,  PRIMARY KEY (' a '),---- ----------------------There is a PK constraint unique  KEY ' UK_BC ' (' B ', ' C ')----------------unique index constraint) Engine=innodb DEFAULT CHARSET =GBK1 row in Set (0.01 sec) [e-mail protected] 02:01:44>select * from Lingluo; Empty Set (0.00 sec) [email protected] 03:27:40>replace into Lingluo values (1,10000,3,4);-------- The table does not have an existing record equivalent to InsertQuery OK, 1 row Affected (0.00 sec)-----------------------Affect_rows is 1

Binlog format:

[email protected] 02:11:18>replace into Lingluo values (1,10000,3,5),-------already exists, and when PK and UK collide, Equivalent to delete and InsertQuery OK, 2 rows Affected (0.00 sec)----------------------Affect_rows is 2, which is the sum of the number of delete and insert rows

Binlog format:

[Email protected] 02:26:09>select * FROM lingluo;+---+-------+------+------+| A | b     | c    | d    |+---+-------+------+------+| 1 | 10000 |    3 |    5 |+---+-------+------+------+1 row in Set (0.00 sec) [email protected] 02:31:54>replace into Lingluo values (1,10000,4, 5),-------already exist record, and PK conflict at the same time, the equivalent of the first delete and then InsertQuery OK, 2 rows Affected (0.00 sec)--------------------------------- Affect_rows is 2, which is the sum of the delete and insert rows [email protected] 02:32:02>select * FROM lingluo;+---+-------+------+------+| A | b     | c    | d    |+---+-------+------+------+| 1 | 10000 |    4 |    5 |+---+-------+------+------+

Binlog format:

[Email protected]test 02:37:04>replace into lingluo values (4,10000,6,5); Query OK, 1 row Affected (0.00 sec) [email protected] 02:37:59>replace into Lingluo values (6,10000,6,5);-------already exist and UK simultaneous conflict, direct UpdateQuery OK, 2 rows Affected (0.00 sec)---------------------------------Affect_rows is 2[email protected] 02:40:31>select * FROM lingluo;+---+-------+------+------+| A | b     | c    | d    |+---+-------+------+------+| 1 | 10000 |    4 |    5 | | 3 | 10000 |    5 |    5 | | 6 | 10000 |    6 |    5 |+---+-------+------+------+3 rows in Set (0.00 sec)

Questions:

Since the UK conflict is update, why are Affect_rows 2?

Specify column replace:[email protected] 03:34:37>select * FROM u;+----+------+------+| ID | Age |  D |+----+------+------+|    0 |  1 |  126 | |    1 |    0 |  1 | |    3 |  1 |  123 | |    4 |  1 |  127 | |    5 |   0 |  12 | |    7 |  2 | 129 |+----+------+------+6 rows in Set (0.00 sec) [email protected] 03:34:37>select * FROM u;+----+------+------+ | ID | Age |  D |+----+------+------+|    0 |  1 |  126 | |    1 |    0 |  1 | |    3 |  1 |  123 | |    4 |  1 |  127 | |    5 |   0 |  12 | |    7 |  2 | 129 |+----+------+------+6 rows in Set (0.00 sec) [email protected] 03:34:40>replace into U (age,d) values (0,130); Query OK, 2 rows affected, 1 warning (0.01 sec) [email protected] 03:40:39>show warnings;+---------+------+------ -----------------------------------+| Level | Code | Message |+---------+------+-----------------------------------------+| Warning | 1364 | Field ' id ' doesn ' t has a default value |+---------+------+-----------------------------------------+1 Row in Set (0.00 sec) [email protected] 03:40:47>select * FROM u;+----+------+---- --+| ID | Age |  D |+----+------+------+|    0 |  0 |  -----------------because the ID is parimary but there is no auto_creasement, 126 becomes 130|    1 |    0 |  1 | |    3 |  1 |  123 | |    4 |  1 |  127 | |    5 |   0 |  12 | |    7 |  2 | 129 |+----+------+------+6 rows in Set (0.00 sec)

When you use it, be aware that:

1. If you specify the Replace column, try to write the whole, otherwise the column data without the input value will be assigned the default value (because the delete is in insert), and the normal insert is the same, So if you want to execute the Replace statement, insert and delete permissions are required.

   If you need to execute SET col_name = col_name + 1 , it's equivalent to execution col_name = DEFAULT(col_name) + 1 .

2. Replace statement If you don't look in depth, just like insert, nothing happens when you're done.

Cases:

[email protected] 04:20:04>select * FROM u;+----+------+------+| ID | Age |  D |+----+------+------+|    0 |  0 |  130 | |    1 |    0 |  1 | |    3 |  1 |  123 | |    4 |  1 |  127 | |    5 |   0 |  12 | |    7 |  2 | 129 |+----+------+------+6 rows in Set (0.00 sec) [email protected] 04:20:10>replace into U (id,d) values (8,232); Query OK, 1 row affected (0.01 sec) [email protected] 04:20:39>select * FROM u;+----+------+------+| ID | Age |  D |+----+------+------+|    0 |  0 |  130 | |    1 |    0 |  1 | |    3 |  1 |  123 | |    4 |  1 |  127 | |    5 |   0 |  12 | |    7 |  2 |  129 | | 8 |  NULL | 232 |+----+------+------+7 rows in Set (0.00 sec) [email protected] 04:20:43>replace into U (id,d) values (7,232); Query OK, 3 rows affected (0.01 sec)----------Note here the Affect_rows is 3 because the primary key 7 already exists and the unique index 232 already exists, so you need to delete the rows with IDs 7 and 8, and then insert the new line [email  protected] 04:20:52>select * FROM u;+----+------+------+| ID | Age |  D |+----+------+------+|    0 |  0 |  130 | |    1 |  0 |  1 | |    3 |  1 |  123 | |    4 |  1 |  127 | |    5 |   0 |  12 | | 7 |  NULL | 232 |+----+------+------+6 rows in Set (0.00 sec) [email protected] 04:20:55>

The algorithm that MySQL uses for replace and load Data....replace is:

1. Try inserting a new row into the table

2. When the table unique index or primary key conflict:

   A. Delete conflict row

   B. Insert a new row into the table again

If duplicate row collisions are encountered, the stored procedure is likely to be executed as an update instead of Delete+insert, but explicitly the same. There is no user visible influence in addition to storage engine xxx layer handler_ state variables.

Because replace ... The result of the SELECT statement depends on the order of the rows of the Select, but the order is not guaranteed to be the same, possibly from both master and slave. It is for this reason that MySQL 5.6.4 after the REPLACE ... The SELECT statement is marked as statement-based replication mode is unsafe. Based on this change, when using statement to log binary logs, if there is such a statement will be in the log output an alarm, also when using the mixed line copy mode will also record the alarm.

In versions prior to MySQL5.6.6, replace affects partitioned tables just as MyISAM locks all partition tables with table-level locks. This happens when you use a REPLACE ... PARTITION statement. (This behavior is not caused by using row-locked innodb.) After MySQL 5.6.6 version MySQL uses a partition lock, only if the partition (as long as there is no Partition table column update) contains the Replace statement and where the actual match will lock the partition, otherwise it will lock the entire table.

Operation form:

Binlog format:

Conclusion

1. When there is a PK conflict is the first delete and then insert

2. Direct update when there is a UK conflict

So what's the use of knowing this?

Give two examples:
1. Primary and Standby replication

At the time of primary and standby replication, the row mode generates a self-growth increment primary key for the replace into statement, which is inconsistent between the master and slave.
After executing on the main library, the auto_increment will not change if it is in the repository! What's wrong with that? After this slave is promoted to master, because Auto_increment is smaller than the actual next ID, duplicate key error occurs when the new record is written, auto_increment + = 1 after each conflict until it grows to Max (ID) + 1 will not return to normal.
So what is the solution to this problem? @ Xiao Qiang-zju classmate has given the answer here: http://bugs.mysql.com/bug.php?id=73563
2. Data migration
Somehow found the values of some fields to be overwritten
In view of this, many of the scenes that use REPLACE into are actually required to INSERT into ... On DUPLICATE KEY UPDATE, use replace into sparingly if you understand the replace into behavior and side effects correctly.

Use MySQL replace statement with caution