MySQL replace into 错误案例 | Focus on MySQL,Focus on Life

Contents

1. 背景
2. 错误场景一
3. 错误场景二
4. 最后总结

背景

* 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)

总结

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 |

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'

总结

1
2

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

最后总结

replace with unique key

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

replace with primary key

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