分析在MySQL中使用replace into 的坑

MySQL 对 SQL 有很多扩展，有些用起来很方便，但有一些被误用之后会有性能问题，还会有一些意料之外的副作用，比如 REPLACE INTO。
背景

    * MySQL5.7
     
    * ROW模式
     
     
    * 表结构
    CREATE TABLE `test` (
      `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
      `col_1` varchar(100) DEFAULT NULL,
      `col_2` varchar(100) DEFAULT NULL,
      `col_3` varchar(100) DEFAULT NULL,
      PRIMARY KEY (`id`),
      UNIQUE KEY `col_1` (`col_1`)
    ) ENGINE=InnoDB  DEFAULT CHARSET=utf8

test表有一个自增的 id 字段作为主键，字段 col_1 有 UNIQUE KEY 做唯一性约束。
错误场景一

    其他字段value莫名其妙的没了

    step1 初始化记录

    mater:lc> REPLACE INTO test (col_1,col_2,col_3) values('a','a','a');
    Query OK, 1 row affected (0.00 sec) --注意，这里是影响了1条记录
     
    master:lc> REPLACE INTO test (col_1,col_2,col_3) values('b','b','b');
    Query OK, 1 row affected (0.00 sec) --注意，这里是影响了1条记录
     
    master:lc> REPLACE INTO test (col_1,col_2,col_3) values('c','c','c');
    Query OK, 1 row affected (0.00 sec) --注意，这里是影响了1条记录
     
     
    master > show create table test
     
    | test  | CREATE TABLE `test` (
      `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
      `col_1` varchar(100) DEFAULT NULL,
      `col_2` varchar(100) DEFAULT NULL,
      `col_3` varchar(100) DEFAULT NULL,
      PRIMARY KEY (`id`),
      UNIQUE KEY `col_1` (`col_1`)
    ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 |
    mater > select * from test;
    +----+-------+-------+-------+
    | id | col_1 | col_2 | col_3 |
    +----+-------+-------+-------+
    |  1 | a     | a     | a     |
    |  2 | b     | b     | b     |
    |  3 | c     | c     | c     |
    +----+-------+-------+-------+
    3 rows in set (0.00 sec)

    step2 构造错误场景

    master:lc> replace into test(col_1,col_2) values('c','cc');
    Query OK, 2 rows affected (0.00 sec)
     
    dba:lc> select * from test;
    +----+-------+-------+-------+
    | id | col_1 | col_2 | col_3 |
    +----+-------+-------+-------+
    |  1 | a     | a     | a     |
    |  2 | b     | b     | b     |
    |  4 | c     | cc    | NULL  |
    +----+-------+-------+-------+
    3 rows in set (0.00 sec)
     

    总结

    col_3 的值，从原来的c，变成了NULL，天呐，数据不见了。 id 也变了。
    用户原本的需求，应该是如果col_1='c' 存在，那么就改变col_2='cc'，其余的记录保持不变，结果id,col_3都变化了
    解决方案就是：将replace into 改成 INSERT INTO … ON DUPLICATE KEY UPDATE

但是你以为这样就完美的解决了吗？ 马上就会带来另外一场灾难，请看下面的错误场景
错误场景二

    ERROR 1062 (23000): Duplicate entry 'x' for key 'PRIMARY'

    step1 初始化记录

     
    mater:lc> REPLACE INTO test (col_1,col_2) values('a','a');
    Query OK, 1 row affected (0.00 sec) --注意，这里是影响了1条记录
     
    master:lc> REPLACE INTO test (col_1,col_2) values('b','b');
    Query OK, 1 row affected (0.00 sec) --注意，这里是影响了1条记录
     
    master:lc> REPLACE INTO test (col_1,col_2) values('c','c');
    Query OK, 1 row affected (0.00 sec) --注意，这里是影响了1条记录
     
     
    master > show create table test
     
    | test  | CREATE TABLE `test` (
      `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
      `col_1` varchar(100) DEFAULT NULL,
      `col_2` varchar(100) DEFAULT NULL,
      PRIMARY KEY (`id`),
      UNIQUE KEY `col_1` (`col_1`)
    ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 |
     
     
    slave > show create table test
     
    | test  | CREATE TABLE `test` (
      `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
      `col_1` varchar(100) DEFAULT NULL,
      `col_2` varchar(100) DEFAULT NULL,
      PRIMARY KEY (`id`),
      UNIQUE KEY `col_1` (`col_1`)
    ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 |

可以看到：

（1）写入三条记录之后，test表的 AUTO_INCREMENT 增长为 4，也就是说下一条不手工为 id 指定值的记录，id 字段的值会是 4；

（2）Master库和Slave库的AUTO_INCREMENT 均增长到4，是一致的：因为初始化记录的该部分replace into操作等效于insert into操作，没有key冲突

    step2 构造错误场景

    * master
     
    mater:lc> REPLACE INTO test (col_1,col_2) values('c','cc');
    Query OK, 2 rows affected (0.00 sec)  --注意，这里是影响了两条记录
     
    mater:lc> show create table test
     
    | test  | CREATE TABLE `test` (
      `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
      `col_1` varchar(100) DEFAULT NULL,
      `col_2` varchar(100) DEFAULT NULL,
      PRIMARY KEY (`id`),
      UNIQUE KEY `col_1` (`col_1`)
    ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 |
    master:lc> select * from test
    +----+-------+-------+
    | id | col_1 | col_2 |
    +----+-------+-------+
    |  1 | a     | a     |
    |  2 | b     | b     |
    |  4 | c     | cc    |
    +----+-------+-------+
    3 rows in set (0.00 sec)
    * slave
    slave:lc> show create table test
    | test  | CREATE TABLE `test` (
      `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
      `col_1` varchar(100) DEFAULT NULL,
      `col_2` varchar(100) DEFAULT NULL,
      PRIMARY KEY (`id`),
      UNIQUE KEY `col_1` (`col_1`)
    ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 |
     
    slave:lc> select * from test
    +----+-------+-------+
    | id | col_1 | col_2 |
    +----+-------+-------+
    |  1 | a     | a     |
    |  2 | b     | b     |
    |  4 | c     | cc    |
    +----+-------+-------+
    3 rows in set (0.00 sec)

有key冲突的replace into操作后，即相当于delete+insert，这时候 slave 上出现了诡异的问题：可以知道，当前表内数据 id 字段的最大值是 4，AUTO_INCREMENT 应该为 5，但在 slave 上 AUTO_INCREMENT 却并未更新，这会有什么问题呢？

接着往下看

    step3 错误案例产生

    * 假设有一天，master 挂了， 由slave 提升为 new mater
     
    原slave:lc> show create table test
     
    | test  | CREATE TABLE `test` (
      `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
      `col_1` varchar(100) DEFAULT NULL,
      `col_2` varchar(100) DEFAULT NULL,
      PRIMARY KEY (`id`),
      UNIQUE KEY `col_1` (`col_1`)
    ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 |
     
    原slave:lc> select * from test
    +----+-------+-------+
    | id | col_1 | col_2 |
    +----+-------+-------+
    |  1 | a     | a     |
    |  2 | b     | b     |
    |  4 | c     | cc    |
    +----+-------+-------+
    3 rows in set (0.00 sec)
     
     
    ===注意==
     
    root:lc> REPLACE INTO test (col_1,col_2) values('d','d');
    ERROR 1062 (23000): Duplicate entry '4' for key 'PRIMARY'

    把这个 slave 提升为 master 之后，由于 AUTO_INCREMENT 比实际的 next id 还要小，写入新记录时就会发生 duplicate key error，每次冲突之后 AUTO_INCREMENT += 1，直到增长为 max(id) + 1 之后才能恢复正常。

    总结

    * Row 模式，主从情况下，replace into 和 INSERT INTO … ON DUPLICATE KEY UPDATE 都会导致以上问题的发生
    * 解决方案： 最后可以通过alter table auto_increment值解决，但是这样已经造成mater的表很长时间没有写入了。。。

最后总结

    replace with unique key

    1. 禁止 replace into (错误一，错误二 都会发生)
    2. 禁止 INSERT INTO … ON DUPLICATE KEY UPDATE （错误二 会发生）

    replace with primary key

    1. 禁止 replace into (会发生错误场景一的案例，丢失部分字段数据)
    2. 可以使用INSERT INTO … ON DUPLICATE KEY UPDATE 代替 replace into

参考资料：

    MySQL "replace into" 的坑 : https://www.cnblogs.com/monian/archive/2014/10/09/4013784.html

    MySQL 小心使用 replace into : https://yq.aliyun.com/articles/57855