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

最新推荐文章于 2024-12-04 20:01:29 发布
原创 最新推荐文章于 2024-12-04 20:01:29 发布 · 1k 阅读 · 0 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#mysql #replace #数据丢失

本文详细分析了在MySQL中不当使用REPLACE语句导致的数据丢失问题，并通过对比测试验证其行为，同时探讨了数据恢复的方法及REPLACE语句对主从同步的影响。
部署运行你感兴趣的模型镜像

一、问题说明

公司开发人员在更新数据时使用了 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

您可能感兴趣的与本文相关的镜像

ACE-Step

ACE-Step

音乐合成
ACE-Step

ACE-Step是由中国团队阶跃星辰（StepFun）与ACE Studio联手打造的开源音乐生成模型。 它拥有3.5B参数量，支持快速高质量生成、强可控性和易于拓展的特点。 最厉害的是，它可以生成多种语言的歌曲，包括但不限于中文、英文、日文等19种语言

数据同步自动化——如何用Python打造高效工具？
Echo_Wish
05-23 429
Echo_Wish的新专栏《Python进阶》和《Python！实战！》正式启动，旨在帮助Python开发者提升技能。专栏内容涵盖高级概念解析、实战案例、学习指导及前沿技术分享，适合不同水平的编程爱好者。专栏还特别探讨了如何利用Python构建自动化数据同步工具，包括数据库同步、文件同步、API数据同步和云存储同步，并提供了相关代码示例。通过这些内容，读者可以掌握高效的数据同步方法，提升编程能力，紧跟技术前沿。
MYSQL——数据库的自增id类型
庄小焱
04-17 4683
MySQL 里有很多自增的 id，每个自增 id 都是定义了初始值，然后不停地往上加步长。虽然自然数是没有上限的，但是在计算机里，只要定义了表示这个数的字节长度，那它就有上限。比如，无符号整型 (unsigned int) 是 4 个字节，上限就是 2^32-1。既然自增 id 有上限，就有可能被用完。但是，自增 id 用完了会怎么样呢？
mysql(五)mysql replace into 缺陷的解决方案
文若书生的专栏
02-21 3305
replace into 实现原理是：如果新插入行的主键或唯一键在表中已经存在，则会删除原有记录并插入新行;如果在表中不存在，则直接插入。这样就会存在缺陷，如果某个字段有默认值，在使用replace into做数据修改时会将默认值的字段恢复到默认值，造成数据丢失。 如下所示，假设有一个表role，其中role_create_time与role_update_time默认值为当前时间，初始数据如下： 使用replace into 新增或修改数据： replace into role (role_id, .
MySQL中用replace批量替换数据
我的技术日常
03-10 2353
REPLACE的运行与INSERT很相似。只有一点例外，假如表中的一个旧记录与一个用于PRIMARY KEY或一个UNIQUE索引的新记录具有相同的值，则在新记录被插入之前，旧记录被删除。 举例如下（.xml中的sql语句） <insert id="..." parameterType="..." > REPLACE INTO evaluate_tag_map (tag...
利用事务日志来恢复Update、Delete误操作引起的数据丢失(转)
08-10 166
利用事务日志来恢复Update、Delete误操作引起的数据丢失(转)[@more@]　　可能有不少朋友遇到过这样的问题：　　update或delete语句忘带了where子句，或where子句精度不够，执行之后造成了严重的后果...
MySQL中REPLACE INTO和INSERT INTO的区别分析
09-11
REPLACE的运行与INSERT很相似。只有一点例外，假如表中的一个旧记录与一个用于PRIMARY KEY或一个UNIQUE索引的新记录具有相同的值，则在新记录被插入之前，旧记录被删除。
Mysql 使用replace into 出现数据丢失插不进去
小猪跑得快
12-19 1305
  REPLACE INTO TDCS_BOX_SHIPMENT_LAND_20 ( SHIPMENT_NO, SHIPMENT_ITEM, SHOULD_BOX_CODE, SWEPT_BOX_CODE,VOLUME_UNIT, LOAD_SITE, UNLOAD_SITE, UNLOAD_FUNCTION...
Mysql学习总结（78）——MySQL各版本差异整理
科技D人生
11-27 3919
Mysql 3.23 集成 berkeley db存储引擎 2000年isam 升级myisam。 Mysql 4.x 集成 innodb引擎，支持事务，支持行锁、支持子查询 支持Unicode (UTF-8)，允许更广泛地进行国际化。 Mysql 5.x 支持游标、存储过程、触发器、视图、XA事务 Mysql 5.1 支持分区和基于行的复制 Mysql 5.5...
速记Java八股文—— MySQL篇
Hardy Army的博客
08-06 695
分类汇总 50+ 常见的 MySQL篇 经典后端面试题，并对题目进行了精炼总结，旨在帮助大家高效记忆，在面试中游刃有余，不至于陷入词穷的窘境。
MySQL数据库安装及基本操作
handsomewzj的博客
12-04 1427
MySQL安装，基本操作，数据类型，列属性，数据完整性，数据库设计，查询语句，视图，事务，索引，函数，Go连接MySQL相关学习知识
Mysql REPLACE INTO 用法
热门推荐
了解—学习—进步—满足
05-06 2万+
一、replace(object,search,replace) 把object中出现search的全部替换为replace select replace('www.jb51.net','w','n') from ... 例：把表table中的name字段中的detail替换为description update table set name=replace(name,'detail','description') 二、replace into replace into 主要作用类似inse
MySQL之replace into
qq_42417269的博客
11-21 1510
replace into和insert into的区别，以及replace into的基本知识点
mysql中replace into的用法详解
qq_40548741的博客
04-04 1万+
replace into语句会根据唯一键/主键删除原有的一条记录， 并且插入一条新的记录来替换原记录。如果没有表不存在唯一键/主键 或者 插入的数据唯一键/主键没有重复的，那么会新增一条记录。 新建一个test表，三个字段，uid，name，mobile, id是自增的主键，mobile是唯一索引； create table test( uid int(11) NOT NULL AUTO_IN...
mysql replace报错_Mysql中replace与replace into的用法讲解
weixin_31971181的博客
01-18 920
Mysql replace与replace into都是经常会用到的功能；replace其实是做了一次update操作，而不是先delete再insert；而replace into其实与insert into很相像，但对于replace into，假如表中的一个旧记录与一个用于PRIMARY KEY或一个UNIQUE索引的新记录具有相同的值，则在新记录被插入之前，旧记录被删除。replace是m...
mysql replace的死锁分析（二）
zht245648124的博客
08-24 3093
mysql死锁分析
REPLACE与INSERT INTO ... ON DUPLICATE KEY总结
半瓶子酱油
02-18 1154
原始数据: mysql> select * from emp; +-------+---------+---------+------------+ | empno | empname | salary  | birthday   | +-------+---------+---------+------------+ |  1001 | tom     | 2999.00 | 2013
MySQL的Replace into 与Insert into on duplicate key update真正的不同之处
chinacmt的博客
01-12 622
这篇文章写得很详细，这里借鉴下: http://www.jb51.net/article/47090.htm
MySQL replace
最新发布
03-26
MySQL 中的 `REPLACE` 语句是一种用于插入或更新数据的命令。它的工作原理类似于 `INSERT`，但如果表中已经存在具有相同主键或唯一索引值的记录，则会先删除该旧记录再插入新记录。 以下是关于 `REPLACE` 的详细介绍： 1. **基本语法**： ```sql REPLACE INTO table_name (column_list) VALUES(value_list); ``` 其中 `table_name` 表示目标表名；`column_list` 和 `value_list` 分别列出需要操作的列及其对应的值。 2. **工作流程**： - 如果指定行不存在（即没有冲突），则直接执行插入操作。 - 若发现有重复的关键字(如 PRIMARY KEY 或 UNIQUE 约束)，那么就首先删除原始记录然后再添加新的记录进去。 3. **注意事项**： - 使用前需明确是否真的适合使用此功能，因为它可能会导致一些意外的数据丢失情况发生。 - 每次成功完成一次替换都会增加两个计数到信息数据库里的“受影响行”数目上——一个是因删除产生的影响，另一个则是由于新增加而来的效果。 4. 示例案例： 假设有一个名为 users 的用户信息表格包含 id、name 字段，并设置了id为主键。下面演示如何利用replace语句往里边存入一条数据以及当遇到同样ID时候的行为模式: 第一步: 创建测试表并初始化几条样本数据; ```sql CREATE TABLE IF NOT EXISTS users( id INT AUTO_INCREMENT, name VARCHAR(50), PRIMARY KEY(id) ); INSERT INTO users(name) VALUE ('Alice'),('Bob'); ``` 第二步: 尝试对现有用户Alic做修改尝试； ```sql -- 此处将 Alice 改成 Charlie 同时保留其原有的 ID 编号不变 REPLACE INTO users(id,name)VALUES(1,'Charlie'); SELECT * FROM users; -- 输出结果应显示 [1,'Charlie'],[2,'Bob'] ```
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值