摘要： mysql replace into 错误案例 背景 * mysql5.7 * row模式 * 表结构 create table `test` ( `id` int(10) unsigned not null auto_increment, `col_1` varc

背景

* 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

错误场景一

其他字段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)
  • 总结
  1. col_3 的值，从原来的c，变成了null，天呐，数据不见了。 id 也变了。
  2. 用户原本的需求，应该是如果col_1='c' 存在，那么就改变col_2='cc'，其余的记录保持不变，结果id,col_3都变化了
  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 |
  • 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)
  • 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'
  • 总结
* 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