MySQL进行Replace操作时造成数据丢失——那些坑你踩了吗？

最新推荐文章于 2024-03-13 14:18:54 发布
最新推荐文章于 2024-03-13 14:18:54 发布
阅读量908 收藏
点赞数
分类专栏： MySQL 文章标签： mysql replace 数据丢失
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/xueyanzhang/article/details/77770564
版权

一、问题说明

公司开发人员在更新数据时使用了 replace into 语句，由于使用不当导致了数据的大量丢失，到底是如何导致的数据丢失？现分析如下。

二、问题分析

a. REPLACE 原理

REPLACE INTO 原理的官方解释为：

REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted.

如果新插入行的主键或唯一键在表中已经存在，则会删除原有记录并插入新行；如果在表中不存在，则直接插入

地址：https://dev.mysql.com/doc/refman/5.6/en/replace.html

b. 问题现象

丢失数据的表结构如下：

CREATE TABLE `active_items` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `ad_id` char(32) NOT NULL DEFAULT '' COMMENT 'XXX',
  `ap_id` tinyint(4) NOT NULL DEFAULT '1' COMMENT 'XXX',
  `price` bigint(20) NOT NULL DEFAULT '0' COMMENT 'xxx',
  `rate` int(11) NOT NULL DEFAULT '0' COMMENT 'XXX',
  `cc_price` bigint(20) NOT NULL DEFAULT '0' COMMENT 'XXX',
  `sale_count` int(11) NOT NULL DEFAULT '0' COMMENT 'XXX',
  `prom_count` int(11) NOT NULL DEFAULT '0' COMMENT 'XXX',
  `c1` int(11) NOT NULL DEFAULT '0' COMMENT 'XXX',
  `status` tinyint(4) NOT NULL DEFAULT '0' COMMENT 'XXX',
  `order_num` int(11) NOT NULL DEFAULT '0' COMMENT 'XXX',
  `score` int(11) NOT NULL DEFAULT '0' COMMENT 'XXX',
  `audit_time` int(11) NOT NULL DEFAULT '0' COMMENT 'XXX',
  `prom_modify_time` int(11) NOT NULL DEFAULT '0' COMMENT 'XXX',
  `sales_modify_time` int(11) NOT NULL DEFAULT '0' COMMENT 'XXX',
  `create_time` int(11) NOT NULL DEFAULT '0' COMMENT '创建时间',
  `modify_time` int(11) NOT NULL DEFAULT '0' COMMENT '最后修改时间',
  `score_modify_time` int(11) NOT NULL DEFAULT '0' COMMENT 'XXX',
  `brand_id` int(11) NOT NULL DEFAULT '0' COMMENT 'XXX',
  `freight_status` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'XXX',
  `flag` tinyint(4) NOT NULL DEFAULT '0' COMMENT 'XXX',
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_ad_id` (`ad_id`) USING BTREE,
  ...
) ENGINE=InnoDB AUTO_INCREMENT=2699 DEFAULT CHARSET=utf8 COMMENT='XXXXX'

执行的replace语句如下（多条）：

REPLACE INTO active_items(ad_id,score) VALUES('XXXXXXX', 1800);

通过查询binlog找到执行记录，部分如下：

### UPDATE `items`.`active_items`
### WHERE
###   @1=21926 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @2='XXXXXXX' /* STRING(96) meta=65120 nullable=0 is_null=0 */
###   @3=1 /* TINYINT meta=0 nullable=0 is_null=0 */
###   @4=3900 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @5=315 /* INT meta=0 nullable=0 is_null=0 */
###   @6=1228 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @7=19 /* INT meta=0 nullable=0 is_null=0 */
###   @8=0 /* INT meta=0 nullable=0 is_null=0 */
###   @9=-6 (4294967290) /* INT meta=0 nullable=0 is_null=0 */
###   @10=0 /* TINYINT meta=0 nullable=0 is_null=0 */
###   @11=0 /* INT meta=0 nullable=0 is_null=0 */
###   @12=8 /* INT meta=0 nullable=0 is_null=0 */
###   @13=1489997272 /* INT meta=0 nullable=0 is_null=0 */
###   @14=1495765800 /* INT meta=0 nullable=0 is_null=0 */
###   @15=1495728000 /* INT meta=0 nullable=0 is_null=0 */
###   @16=1489996426 /* INT meta=0 nullable=0 is_null=0 */
###   @17=1489997272 /* INT meta=0 nullable=0 is_null=0 */
###   @18=1495728725 /* INT meta=0 nullable=0 is_null=0 */
###   @19=0 /* INT meta=0 nullable=0 is_null=0 */
###   @20=1 /* TINYINT meta=0 nullable=0 is_null=0 */
###   @21=0 /* TINYINT meta=0 nullable=0 is_null=0 */
### SET
###   @1=35313 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @2='XXXXXXX' /* STRING(96) meta=65120 nullable=0 is_null=0 */
###   @3=1 /* TINYINT meta=0 nullable=0 is_null=0 */
###   @4=0 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @5=0 /* INT meta=0 nullable=0 is_null=0 */
###   @6=0 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @7=0 /* INT meta=0 nullable=0 is_null=0 */
###   @8=0 /* INT meta=0 nullable=0 is_null=0 */
###   @9=0 /* INT meta=0 nullable=0 is_null=0 */
###   @10=0 /* TINYINT meta=0 nullable=0 is_null=0 */
###   @11=0 /* INT meta=0 nullable=0 is_null=0 */
###   @12=3150 /* INT meta=0 nullable=0 is_null=0 */
###   @13=0 /* INT meta=0 nullable=0 is_null=0 */
###   @14=0 /* INT meta=0 nullable=0 is_null=0 */
###   @15=0 /* INT meta=0 nullable=0 is_null=0 */
###   @16=0 /* INT meta=0 nullable=0 is_null=0 */
###   @17=0 /* INT meta=0 nullable=0 is_null=0 */
###   @18=0 /* INT meta=0 nullable=0 is_null=0 */
###   @19=0 /* INT meta=0 nullable=0 is_null=0 */
###   @20=0 /* TINYINT meta=0 nullable=0 is_null=0 */
###   @21=0 /* TINYINT meta=0 nullable=0 is_null=0 */
  • 操作的ad_id已经存在，因此先删除后插入，可以看到除了指定的 ad_id,score，其他字段都变为默认值，导致原有数据丢失（虽然在日志中转为了update）

c. 对比测试

接下来我进行了如下测试：

对比测试

  • 左侧使用 REPLACE 语句，右侧使用 DELETE + INSERT 语句，最后结果完全相同
  • 原主键id为1的行被删除，新插入行主键id更新为4，没有指定内容的字段c则插入了默认值
  • 使用 REPLACE 更新了一行数据，MySQL提示受影响行数为2行
  • 综上所述，说明确实是删除一行，插入一行

三、数据恢复

数据丢失或数据错误后，可以有如下几种方式恢复：

  1. 业务方自己写脚本恢复
  2. 通过MySQL的binlog查出误操作sql，生成反向sql进行数据恢复（适合sql数据量较小的情况）
  3. 通过历史备份文件+增量binlog将数据状态恢复到误操作的前一刻

四、问题扩展

通过上述分析可以发现，REPLACE 会删除旧行并插入新行，但是binlog中是以update形式记录，这样就带来另一个问题：

从库自增长值小于主库

1. 测试

a. 主从一致：

主库：
mysql> show create table t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` int(11) DEFAULT '0',
  `b` int(11) DEFAULT '0',
  `c` int(11) DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `a` (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

从库：
mysql> show create table t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` int(11) DEFAULT '0',
  `b` int(11) DEFAULT '0',
  `c` int(11) DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `a` (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

b. 主库REPLACE：

主库：
mysql> replace into t (a,b)values(1,7);
Query OK, 2 rows affected (0.01 sec)

mysql> show create table t\G           
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` int(11) DEFAULT '0',
  `b` int(11) DEFAULT '0',
  `c` int(11) DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `a` (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

从库：
mysql> show create table t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` int(11) DEFAULT '0',
  `b` int(11) DEFAULT '0',
  `c` int(11) DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `a` (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
  • 注意此时主从两个表的AUTO_INCREMENT值已经不同了

c. 模拟从升主，在从库进行INSERT：

mysql> insert into t (a,b,c)values(4,4,4);
ERROR 1062 (23000): Duplicate entry '4' for key 'PRIMARY'
mysql> show create table t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` int(11) DEFAULT '0',
  `b` int(11) DEFAULT '0',
  `c` int(11) DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `a` (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> insert into t (a,b,c)values(4,4,4);
Query OK, 1 row affected (0.00 sec)
  • 从库插入时会报错，主键重复，报错后AUTO_INCREMENT会 +1，因此再次执行就可以成功插入

2. 结论

这个问题在平时不会有丝毫影响，但是：

如果主库平时大量使用 REPLACE 语句，造成从库 AUTO_INCREMENT 值落后主库太大，当主从发生切换后，再次插入数据时新的主库就会出现大量主键重复报错，导致数据无法插入。

3. 参考文章

http://www.cnblogs.com/monian/archive/2014/10/09/4013784.html

翡翠谷
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
mysql replace报错_Mysql中replace与replace into的用法讲解
weixin_31971181的博客
01-18 809
Mysql replace与replace into都是经常会用到的功能；replace其实是做了一次update操作，而不是先delete再insert；而replace into其实与insert into很相像，但对于replace into，假如表中的一个旧记录与一个用于PRIMARY KEY或一个UNIQUE索引的新记录具有相同的值，则在新记录被插入之前，旧记录被删除。replace是m...
MySQL使用Replace操作时造成数据丢失的问题解决
01-19
公司开发人员在更新数据时使用了 replace into 语句，由于使用不当导致了数据的大量丢失，到底是如何导致的数据丢失本文对此进行分析。 一、问题说明 公司开发人员在更新数据时使用了 replace into 语句，由于使用...
mysql(五)mysql replace into 缺陷的解决方案
文若书生的专栏
02-21 2991
replace into 实现原理是：如果新插入行的主键或唯一键在表中已经存在，则会删除原有记录并插入新行;如果在表中不存在，则直接插入。这样就会存在缺陷，如果某个字段有默认值，在使用replace into做数据修改时会将默认值的字段恢复到默认值，造成数据丢失。 如下所示，假设有一个表role，其中role_create_time与role_update_time默认值为当前时间，初始数据如下： 使用replace into 新增或修改数据： replace into role (role_id, .
MySQL中用replace批量替换数据
我的技术日常
03-10 2221
REPLACE的运行与INSERT很相似。只有一点例外，假如表中的一个旧记录与一个用于PRIMARY KEY或一个UNIQUE索引的新记录具有相同的值，则在新记录被插入之前，旧记录被删除。 举例如下（.xml中的sql语句） <insert id="..." parameterType="..." > REPLACE INTO evaluate_tag_map (tag...
利用事务日志来恢复Update、Delete误操作引起的数据丢失(转)
08-10 123
利用事务日志来恢复Update、Delete误操作引起的数据丢失(转)[@more@]　　可能有不少朋友遇到过这样的问题：　　update或delete语句忘带了where子句，或where子句精度不够，执行之后造成了严重的后果...
mysql中replace和insert的区别
u013787599的博客
05-27 3257
起源 碰到这个问题是这样的，最近在做一个项目开发中碰到一种写法 $Db->table('tablename')->insert($data,true); 之前都是直接用的insert，不知道这个true有啥用 分析 查看了一下文档，这个参数支持insert和replace两种方法，而replace的主要特点如下： 圆善的学习园地-慎用MySQL replace语句 大概意思就是，replace可以检查插入数据时的主键冲突或唯一索引冲突，如果冲突的话就会对之前的数据进行更新。 但这里有一点小坑，
mysql的增_MySQL之增_insert-replace
weixin_34532284的博客
02-28 965
MySQL增删改查之增insert、replace一、INSERT语句带有values子句的insert语句，用于数据的增加语法：INSERT [INTO] tbl_name[(col_name,...)]{VALUES | VALUE} (expr ,...),(...),...①用来把一个新行插入到表中②为和其它数据库保持一致，不要省略INTO关键字以及使用VALUES而不是value关...
mysql学习——数据表的基本操作
12-14
嗯，接上一章数据库的基本操作后，这篇是基于数据表的基本操作，我们将数据库建立好后，就有创建数据表，数据库好比一个文件柜，而数据表才是存放数据的地方，，数据表的基本操作何数据库类似，分别为，创建数据表、...
MySQL连接查询——MySQL 基础查询你会了吗？
12-14
上一篇：MySQL函数查询——MySQL 基础查询你会了吗？ 文章目录2、MySql连接查询2.1、内连接(sql92 和sql99)2.1.1、sql92`1、等值连接`2、 为表起别名6、可以加排序吗？7、可以实现三表连接吗？2、非等值连接3、自...
mysql解决replace无法替换\r\n
最新发布
JePenseDoncJeSuis的博客
03-13 492
REPLACE(字段,'\\r\\n','')
MySQL的Replace into 与Insert into ..... on duplicate key update ...真正的不同之处
caobihole
03-07 416
今天听同事介绍oracle到mysql的数据migration，他用了Insert into ..... on duplicate key update ...，我当时就想怎么不用Replace呢，于是回来就仔细查了下，它们果然还是有区别的，看下面的例子吧： 1 Replace into ...1.1 录入原始数据 mysql&gt; use test; Database changed...
mysql 字符串替换
daniel1227的博客
10-11 736
问题：需要将 www.baidu.com 中的‘baidu’替换为 ‘abc’ 数据库版本 5.6.16 最开始使用replace方法，一直不生效，只能使用subString_index方法来进行替换 首先学习一下这个方法 substring_index(str,delim,count) str:要处理的字符串 delim:分隔符 count...
数据库插入操作insert，replace，load
weixin_53402685的博客
02-18 2540
先创建一个表进行插入操作 create table wx_student( sno int(6) primary key , sname varchar(20) not null , sphone char(11) unique ,/*唯一，可以为空，不能重复*/ sage tinyint unsigned default 18, saddr varchar(25), uid int ); 1.insert insert插入数据有以下几种方式 其中
MySQL 进行 Replace 操作时造成数据丢失——那些坑你踩了吗？
u014315200的博客
09-28 3673
原文章地址：http://mp.weixin.qq.com/s/nCryxMQK00ZjjuTsfYvk3w 一、问题说明 公司开发人员在更新数据时使用了 replace into 语句，由于使用不当导致了数据的大量丢失，到底是如何导致的数据丢失？现分析如下。 二、问题分析 a. REPLACE 原理 REPLACE INTO 原理的官方解释为
mysql replace的弊端_MySQL "replace into" 的坑
weixin_32067105的博客
01-16 892
MySQL 对 SQL 有很多扩展，有些用起来很方便，但有一些被误用之后会有性能问题，还会有一些意料之外的副作用，比如 REPLACE INTO。比如有这样一张表：CREATE TABLE`auto` (`id`int(10) unsigned NOT NULLAUTO_INCREMENT,`k`int(10) unsigned NOT NULL,`v`varchar(100) DEFAULT N...
使用MySql replace into（insert into 的增强版）时你不得不注意的坑
热门推荐
helloxiaozhe的博客
08-20 5万+
使用MySql replace into（insert into 的增强版）时你不得不注意的坑 使用之前，你必须先搞懂它的原理，本文从以下几个方面介绍：replace into的应用场景；replace into的原理；replace into的应用注意事项,replace into的应用形式
mysql replace的弊端_慎用MySQL replace语句
weixin_42468901的博客
01-16 195
语法：REPLACE [LOW_PRIORITY | DELAYED][INTO] tbl_name[PARTITION (partition_name,...)][(col_name,...)]{VALUES | VALUE} ({expr | DEFAULT},...),(...),...Or:REPLACE [LOW_PRIORITY | DELAYED][INTO] tbl_name[PA...
Mysql INSERT、REPLACE、UPDATE的区别
02-19 1万+
用于操作数据库的SQL一般分为两种，一种是查询语句，也就是我们所说的SELECT语句，另外一种就是更新语句，也叫做数据操作语句。言外之意，就是对数据进行修改。在标准的SQL中有3个语句，它们是INSERT、UPDATE以及DELETE。在MySQL中又多了一个REPLACE语句，因此，本文以MySQL为背景来讨论如何使有SQL中的更新语句。 　　一、INSERT和REPLACE 　　I
mysql死锁会导致数据丢失吗？
05-24
一般来说，MySQL死锁不会导致数据丢失。 MySQL的死锁是指两个或多个事务相互等待对方释放资源，从而导致它们都无法继续执行的情况。MySQL会检测到死锁并自动回滚其中一个事务，以解除死锁。因此，虽然死锁会导致某些事务失败，但数据不会丢失，因为MySQL会自动回滚事务并恢复数据库到之前的一致状态。 然而，如果在死锁发生时，有其他错误或异常发生，例如硬件故障，MySQL可能无法自动恢复事务或恢复数据库到正确的状态。这种情况下，可能会导致数据丢失。因此，为了保证数据的完整性，建议定期备份数据库，并确保备份的数据是可靠的。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值