MySQL "replace into" 的坑 自增id，備機會有問題，這個問題在mysql5.7.17上是不會出現

MySQL "replace into" 的坑 自增id，備機會有問題，這個問題在mysql5.7.17上是不會出現

以下是網上資料,

來源于：http://www.cnblogs.com/monian/archive/2014/10/09/4013784.html

MySQL "replace into" 的坑

MySQL 對 SQL 有很多擴展，有些用起來很方便，但有一些被誤用之后會有性能問題，還會有一些意料之外的副作用，比如 REPLACE INTO。

比如有這樣一張表：

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 表有一個自增的 id 字段作為主鍵，字段 k 有 UNIQUE KEY 做唯一性約束。寫入幾條記錄之后會是這樣：

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: 0
xupeng@diggle7:3600(dba_m) [dba] mysql> SHOW CREATE TABLE auto\G
*************************** 1. row ***************************
       Table: auto
Create 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
1 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)

在 slave 節點上是和 master 一致的：

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: auto
Create 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
1 row in set (0.00 sec)

可以看到，寫入三條記錄之后，auto 表的 AUTO_INCREMENT 增長為 4，也就是說下一條不手工為 id 指定值的記錄，id 字段的值會是 4。

接下來使用 REPLACE INTO 來寫入一條記錄：

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: auto
Create 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=latin1
1 row in set (0.00 sec)

可以看到 MySQL 說 “2 rows affected”，可是明明是只寫一條記錄，為什么呢？這是因為 MySQL 在執行 REPLACE INTO auto (k) VALUES (1) 時首先嘗試 INSERT INTO auto (k) VALUES (1)，但由于已經存在一條 k=1 的記錄，發生了 duplicate key error，于是 MySQL 會先刪除已有的那條 k=1 即 id=1 的記錄，然后重新寫入一條新的記錄。

這時候 slave 上出現了詭異的問題：

xupeng@diggle8:3600(dba_s) [dba] mysql> SHOW CREATE TABLE auto\G
*************************** 1. row ***************************
       Table: auto
Create 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

可以知道，當前表內數據 id 字段的最大值是 4，AUTO_INCREMENT 應該為 5，但在 slave 上 AUTO_INCREMENT 卻并未更新，這會有什么問題呢？把這個 slave 提升為 master 之后，由于 AUTO_INCREMENT 比實際的 next id 還要小，寫入新記錄時就會發生 duplicate key error，每次沖突之后 AUTO_INCREMENT += 1，直到增長為 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)

沒有預料到 MySQL 在數據沖突時實際上是刪掉了舊記錄，再寫入新記錄，這是使用 REPLACE INTO 時最大的一個誤區，拿之前的例子來說，執行完 REPLACE INTO auto (k, v) VALUES (1, ‘1-1’) 之后，由于新寫入記錄時并未給 extra 字段指定值，原記錄 extra 字段的值就「丟失」了，而通常這并非是業務上所預期的，更常見的需求實際上是，當存在 k=1 的記錄時，就把 v 字段的值更新為 ‘1-1’，其他未指定的字段則保持原狀，而滿足這一需求的 MySQL 方言是 INSERT INTO auto (k, v) VALUES (1, ‘1-1’) ON DUPLICATE KEY UPDATE v=VALUES(v);

鑒于此，很多使用 REPLACE INTO 的場景，實際上需要的是 INSERT INTO … ON DUPLICATE KEY UPDATE，在正確理解 REPLACE INTO 行為和副作用的前提下，謹慎使用 REPLACE INTO。