[Turn] MySQL "replace into" pit (5.5 row format)

MySQL has a lot of extensions for SQL, some of which are handy, but there are performance issues with some of them being misused, and there are some unintended side effects, like REPLACE into.

For example, there is a table:

12345678

CREATE TABLE `auto` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `k` int(10) unsigned NOT NULL, `v` varchar(100) DEFAULT NULL, `extra` varchar(200) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `uk_k` (`k`)) ENGINE=InnoDB DEFAULT CHARSET=latin1

The Auto table has a self-increment ID field as the primary key, and the field K has a unique key to do the uniqueness constraint. After writing a few records, this will be the case:

1234567891011121314151617181920212223242526

[Email protected]:3600 (dba_m) [DBA] mysql> INSERT into auto (k, V, Extra) VALUES (1, ' 1 ', ' extra 1 '), (2, ' 2 ', ' Extra 2 '), (3, ' 3 ', ' extra 3 ');Query OK, 3 rows affected (0.01 sec)Records:3 duplicates:0 warnings:0[Email protected]:3600 (dba_m) [DBA] mysql> SHOW CREATE TABLE auto\g1. Row ***************************Table:autoCreate table:create Table ' Auto ' ( ' id ' int (ten) unsigned not NULL auto_increment, ' k ' int (ten) unsigned not Null, ' extra ' varchar ($) Default Null, PRIMARY key (' id '), Engine=innodb auto_increment=4 DEFAULT charset=latin11 row in Set (0.01 sec) [email  protected]:3600 (Dba_m) [DBA] mysql> SELECT * from auto; +----+---+------+---------+| ID | k | v | extra | +----+---+------+---------+| 1 | 1 | 1 | extra 1 | | 2 | 2 | 2 | extra 2 | | 3 | 3 | 3 | Extra 3 | +----+---+------+---------+3 rows in Set (0.00 sec)

On the slave node is the same as master:

123456789-ten-19 the

[Email protected]:3600 (dba_s) [DBA] mysql> SELECT * from auto;+----+---+------+---------+| ID | K | V | Extra |+----+---+------+---------+| 1 | 1 | 1 | Extra 1 || 2 | 2 | 2 | extra 2 | | 3 | 3 | 3 | Extra 3 | +----+---+------+---------+3 rows in Set (0.00 sec) [email protected]:3600 (dba_s) [DBA] mysql> SHOW CREATE TABLE auto\g********* 1. Row ***************************create table:create Table ' Auto ' ( Span class= "line" > ' id ' int (ten) unsigned not NULL auto_increment, ' k ' int (ten) unsigned not Null, ' extra ' varchar ($) Default Null, PRIMARY key (' id '), Engine=innodb auto_increment=4 DEFAULT charset=latin11 row in Set (0.00 sec)

As you can see, after writing three records, the auto_increment of the auto table grows to 4, that is, the next record that does not manually specify a value for the ID, the value of the ID field is 4.

Next use REPLACE into to write a record:

12345678910111213141516171819202122232425

[Email protected]:3600 (dba_m) [DBA] mysql> REPLACE into auto (k, v) VALUES (1, ' 1-1 ');Query OK, 2 rows affected (0.01 sec)[Email protected]:3600 (dba_m) [DBA] mysql> SELECT * from auto;+----+---+------+---------+| ID | K | V | Extra |+----+---+------+---------+| 2 | 2 | 2 | Extra 2 || 3 | 3 | 3 | Extra 3 | | 4 | 1 | 1-1 | NULL | +----+---+------+---------+3 rows in Set (0.00 sec) [email protected]:3600 (dba_m) [DBA] mysql> SHOW CREATE TABLE auto\g********* 1. Row ***************************create table:create Table ' Auto ' ( Span class= "line" > ' id ' int (ten) unsigned not NULL auto_increment, ' k ' int (ten) unsigned not Null, ' extra ' varchar ($) Default Null, PRIMARY key (' id '), Engine=innodb auto_increment=5 DEFAULT charset=latin11 row in Set (0.00 sec)

Can see MySQL said "2 rows affected", but obviously only write a record, why? This is because MySQL first tries to INSERT into auto (k) VALUES (1) when executing REPLACE into auto (k) VALUES (1), but since there is already a k=1 record, duplicate key has occurred. Error, MySQL will first delete the existing k=1 that is the id=1 record, and then write a new record again.

At this time the slave appeared on the strange question:

123456789

[email protected]:3600 (dba_s) [DBA] mysql> SHOW CREATE TABLE Auto\g*************************** 1. Row *************************** table:a Utocreate table:create Table ' auto ' ( ' id ' int (ten) unsigned not NULL auto_incremen T, ' k ' int (ten) unsigned not null, ' V ' varchar (+) DEFAULT NULL, PRIMARY KEY (' id '), UNIQUE KEY ' Uk_k ' (' K ') ) Engine=innodb auto_increment=4 DEFAULT charset=latin1

As you can see, the maximum value for the Data ID field in the current table is 4,auto_increment should be 5, but it is not updated on slave, what is the problem? 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:

123456789

[email protected]:3600 (dba_s) [DBA] mysql> REPLACE into auto (k, V) VALUES (4, ' 4 '); error 1062 (23000): Duplicate entry ' 4 ' for key ' PRIMARY ' [email protected] : 3600 (dba_s) [DBA] mysql> REPLACE into auto (k, v) VALUES (5, ' 5 '); query OK, 1 row affected (0.00 sec) [email protected] : 3600 (dba_s) [DBA] mysql> SELECT * from auto; +----+---+------+---------+| ID | k | v | extra | +----+---+------+---------+| 2 | 2 | 2 | extra 2 | | 3 | 3 | 3 | Extra 3 | | 4 | 1 | 1-1 | NULL | | 5 | 5 | 5 | NULL | +----+---+------+---------+4 rows in Set (0.00 sec)

It was not anticipated that MySQL actually deleted the old record when the data conflict, and then wrote the new record, which is the biggest mistake when using replace into, taking the previous example, after executing REPLACE into auto (k, v) VALUES (1, ' 1-1 '), Since the value of the extra field is not specified in the new write record, the values of the extra field of the original record are "lost", which is usually not expected in business, and the more common requirement is actually that when there is a k=1 record, the value of the V field is updated to ' 1-1 ', other unspecified fields remain intact, while the MySQL dialect that satisfies this requirement is INSERT into auto (k, v) of VALUES (1, ' 1-1 ') on DUPLICATE KEY UPDATE v=values (v);

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.

Original address:

https://blog.xupeng.me/2013/10/11/mysql-replace-into-trap/

[Turn] MySQL "replace into" pit (5.5 row format)