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，如需转载请自行联系原作者