MySQL & quot; replace into & quot;, mysqlreplaceinto

MySQL "replace into", mysqlreplaceinto

MySQL has many extensions for SQL, some of which are very convenient to use, but some may have performance problems after misuse, and some unexpected side effects, such as REPLACE.

For example, there is a table:

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=InnoD

The auto table has an auto-increment id field as the primary KEY, and field k has a unique key as the uniqueness constraint. After writing several records, it will look like this:

xupeng@diggle7: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: 0xupeng@diggle7:3600(dba_m) [dba] mysql> SHOW CREATE TABLE auto\G*************************** 1. row ***************************       Table: autoCreate Table: 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 AUTO_INCREMENT=4 DEFAULT CHARSET=latin11 row in set (0.01 sec)xupeng@diggle7: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)

The slave node is consistent with the master node:

xupeng@diggle8: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)xupeng@diggle8:3600(dba_s) [dba] mysql> SHOW CREATE TABLE auto\G*************************** 1. row ***************************       Table: autoCreate Table: 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 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 increases to 4, that is to say, the next record that does not manually specify the value for the id field will be 4.

Next, use replace into to write a record:

xupeng@diggle7:3600(dba_m) [dba] mysql> REPLACE INTO auto (k, v) VALUES (1, '1-1');Query OK, 2 rows affected (0.01 sec)xupeng@diggle7: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)xupeng@diggle7:3600(dba_m) [dba] mysql> SHOW CREATE TABLE auto\G*************************** 1. row ***************************       Table: autoCreate Table: 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 AUTO_INCREMENT=5 DEFAULT CHARSET=latin11 row in set (0.00 sec)

We can see that MySQL says "2 rows affected", but it clearly only writes one 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 a record with k = 1 already exists, A duplicate key error occurs, so MySQL will first Delete the existing k = 1 (id = 1) record, and then write a new record.

At this time, slave encountered a strange problem:

xupeng@diggle8:3600(dba_s) [dba] mysql> SHOW CREATE TABLE auto\G*************************** 1. row ***************************       Table: autoCreate Table: 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 AUTO_INCREMENT=4 DEFAULT CHARSET=latin1

As you can know, the maximum value of the data id field in the current table is 4 and AUTO_INCREMENT should be 5, but AUTO_INCREMENT on slave is not updated. What is the problem? After the slave is upgraded to the master, because AUTO_INCREMENT is smaller than the actual next id, a duplicate key error occurs when writing a new record. After each conflict, AUTO_INCREMENT + = 1, it cannot be restored until it is increased to max (id) + 1:

xupeng@diggle8:3600(dba_s) [dba] mysql> REPLACE INTO auto (k, v) VALUES (4, '4');ERROR 1062 (23000): Duplicate entry '4' for key 'PRIMARY'xupeng@diggle8:3600(dba_s) [dba] mysql> REPLACE INTO auto (k, v) VALUES (5, '5');Query OK, 1 row affected (0.00 sec)xupeng@diggle8: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)

I didn't expect MySQL to delete the old record and write the new record in the case of data conflicts. This is the biggest misunderstanding when REPLACE INTO is used. In the previous example, after the replace into auto (k, v) VALUES (1, '1-1') is executed, because the extra field is not specified for the new write record, the value of the extra field in the original record is "lost", which is generally not as expected in the business. A more common requirement is that when a record with k = 1 extra exists, the value of the v field is updated to '1-1', and other unspecified fields are retained as original. The MySQL dialect that meets this requirement is insert into auto (k, v) VALUES (1, '1-1') on duplicate key update v = VALUES (v );

In view of this, many scenarios that use replace into actually require insert... On duplicate key update, use replace into with caution while correctly understanding the behavior and side effects of replace.

Mysql replace

It should be update.
 
What is the MySQL database replace?

If it does not exist, insert it. Is the original data replaced?
However, the premise is that one field in the field is the primary key. Otherwise, it cannot be replaced. It is the same as insert.