MySQL 小心使用 replace into

  1. 云栖社区>
  2. 博客列表>
  3. 正文

MySQL 小心使用 replace into

兰春 2016-07-20 16:41:38 浏览3806 评论0

摘要: MySQL replace into 错误案例 背景 * MySQL5.7 * ROW模式 * 表结构 CREATE TABLE `test` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `col_1` varc

MySQL 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

错误场景一

其他字段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

用云栖社区APP,舒服~

【云栖快讯】诚邀你用自己的技术能力来用心回答每一个问题,通过回答传承技术知识、经验、心得,问答专家期待你加入!  详情请点击

网友评论

兰春
文章56篇 | 关注213
关注
MySQL 是全球最受欢迎的开源数据库,阿里云MySQL版 通过深度的内核优化和独享实例提供... 查看详情
提供海量、安全和高可靠的云存储服务。RESTful API的平台无关性,容量和处理能力的弹性... 查看详情
支持 PB 级数据存储的海量分布式关系型数据库。它支持 MySQL 数据库接口,采用可扩展的... 查看详情
为您提供简单高效、处理能力可弹性伸缩的计算服务,帮助您快速构建更稳定、安全的应用,提升运维效... 查看详情
阿里云总监课正式启航

阿里云总监课正式启航