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。

比如有這樣一張表：

<code>CREATE</code> <code>TABLE</code> <code>`auto` (</code>

<code>  </code><code>`id` </code><code>int</code><code>(10) unsigned </code><code>NOT</code> <code>NULL</code> <code>AUTO_INCREMENT,</code>

<code>  </code><code>`k` </code><code>int</code><code>(10) unsigned </code><code>NOT</code> <code>NULL</code><code>,</code>

<code>  </code><code>`v` </code><code>varchar</code><code>(100) </code><code>DEFAULT</code> <code>NULL</code><code>,</code>

<code>  </code><code>`extra` </code><code>varchar</code><code>(200) </code><code>DEFAULT</code> <code>NULL</code><code>,</code>

<code>  </code><code>PRIMARY</code> <code>KEY</code> <code>(`id`),</code>

<code>  </code><code>UNIQUE</code> <code>KEY</code> <code>`uk_k` (`k`)</code>

<code>) ENGINE=InnoDB</code>

auto 表有一個自增的 id 字段作為主鍵，字段 k 有 UNIQUE KEY 做唯一性限制。寫入幾條記錄之後會是這樣：

<code>xupeng@diggle7:3600(dba_m) [dba] mysql&gt; </code><code>INSERT</code> <code>INTO</code> <code>auto (k, v, extra) </code><code>VALUES</code> <code>(1, </code><code>'1'</code><code>, </code><code>'extra 1'</code><code>), (2, </code><code>'2'</code><code>, </code><code>'extra 2'</code><code>), (3, </code><code>'3'</code><code>, </code><code>'extra 3'</code><code>);</code>

<code>Query OK, 3 </code><code>rows</code> <code>affected (0.01 sec)</code>

<code>Records: 3  Duplicates: 0  Warnings: 0</code>

<code>xupeng@diggle7:3600(dba_m) [dba] mysql&gt; SHOW </code><code>CREATE</code> <code>TABLE</code> <code>auto\G</code>

<code>*************************** 1. row ***************************</code>

<code>       </code><code>Table</code><code>: auto</code>

<code>Create</code> <code>Table</code><code>: </code><code>CREATE</code> <code>TABLE</code> <code>`auto` (</code>

<code>) ENGINE=InnoDB AUTO_INCREMENT=4 </code><code>DEFAULT</code> <code>CHARSET=latin1</code>

<code>1 row </code><code>in</code> <code>set</code> <code>(0.01 sec)</code>

<code>xupeng@diggle7:3600(dba_m) [dba] mysql&gt; </code><code>SELECT</code> <code>* </code><code>FROM</code> <code>auto;</code>

<code>+</code><code>----+---+------+---------+</code>

<code>| id | k | v    | extra   |</code>

<code>|  1 | 1 | 1    | extra 1 |</code>

<code>|  2 | 2 | 2    | extra 2 |</code>

<code>|  3 | 3 | 3    | extra 3 |</code>

<code>3 </code><code>rows</code> <code>in</code> <code>set</code> <code>(0.00 sec)</code>

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

<code>xupeng@diggle8:3600(dba_s) [dba] mysql&gt; </code><code>SELECT</code> <code>* </code><code>FROM</code> <code>auto;</code>

<code>xupeng@diggle8:3600(dba_s) [dba] mysql&gt; SHOW </code><code>CREATE</code> <code>TABLE</code> <code>auto\G</code>

<code>1 row </code><code>in</code> <code>set</code> <code>(0.00 sec)</code>

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

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

<code>xupeng@diggle7:3600(dba_m) [dba] mysql&gt; </code><code>REPLACE</code> <code>INTO</code> <code>auto (k, v) </code><code>VALUES</code> <code>(1, </code><code>'1-1'</code><code>);</code>

<code>Query OK, 2 </code><code>rows</code> <code>affected (0.01 sec)</code>

<code>|  4 | 1 | 1-1  | </code><code>NULL</code>    <code>|</code>

<code>) ENGINE=InnoDB AUTO_INCREMENT=5 </code><code>DEFAULT</code> <code>CHARSET=latin1</code>

可以看到 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 上出現了詭異的問題：

可以知道，目前表内資料 id 字段的最大值是 4，AUTO_INCREMENT 應該為 5，但在 slave 上 AUTO_INCREMENT 卻并未更新，這會有什麼問題呢？把這個 slave 提升為 master 之後，由于 AUTO_INCREMENT 比實際的 next id 還要小，寫入新記錄時就會發生 duplicate key error，每次沖突之後 AUTO_INCREMENT += 1，直到增長為 max(id) + 1 之後才能恢複正常：

<code>xupeng@diggle8:3600(dba_s) [dba] mysql&gt; </code><code>REPLACE</code> <code>INTO</code> <code>auto (k, v) </code><code>VALUES</code> <code>(4, </code><code>'4'</code><code>);</code>

<code>ERROR 1062 (23000): Duplicate entry </code><code>'4'</code> <code>for</code> <code>key</code> <code>'PRIMARY'</code>

<code>xupeng@diggle8:3600(dba_s) [dba] mysql&gt; </code><code>REPLACE</code> <code>INTO</code> <code>auto (k, v) </code><code>VALUES</code> <code>(5, </code><code>'5'</code><code>);</code>

<code>Query OK, 1 row affected (0.00 sec)</code>

<code>|  5 | 5 | 5    | </code><code>NULL</code>    <code>|</code>

<code>4 </code><code>rows</code> <code>in</code> <code>set</code> <code>(0.00 sec)</code>

沒有預料到 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。

本文轉自 corasql 51CTO部落格，原文連結：http://blog.51cto.com/corasql/1913191，如需轉載請自行聯系原作者