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

数据库 MySQL
公司开发人员在更新数据时使用了 replace into 语句,由于使用不当导致了数据的大量丢失,到底是如何导致的数据丢失?本文对此进行分析。

一、问题说明

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

丢失数据的表结构如下:

  1. CREATE TABLE `active_items` ( 
  2.   `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键'
  3.   `ad_id` char(32) NOT NULL DEFAULT '' COMMENT 'XXX'
  4.   `ap_id` tinyint(4) NOT NULL DEFAULT '1' COMMENT 'XXX'
  5.   `price` bigint(20) NOT NULL DEFAULT '0' COMMENT 'xxx'
  6.   `rate` int(11) NOT NULL DEFAULT '0' COMMENT 'XXX'
  7.   `cc_price` bigint(20) NOT NULL DEFAULT '0' COMMENT 'XXX'
  8.   `sale_count` int(11) NOT NULL DEFAULT '0' COMMENT 'XXX'
  9.   `prom_count` int(11) NOT NULL DEFAULT '0' COMMENT 'XXX'
  10.   `c1` int(11) NOT NULL DEFAULT '0' COMMENT 'XXX'
  11.   `status` tinyint(4) NOT NULL DEFAULT '0' COMMENT 'XXX'
  12.   `order_num` int(11) NOT NULL DEFAULT '0' COMMENT 'XXX'
  13.   `score` int(11) NOT NULL DEFAULT '0' COMMENT 'XXX'
  14.   `audit_time` int(11) NOT NULL DEFAULT '0' COMMENT 'XXX'
  15.   `prom_modify_time` int(11) NOT NULL DEFAULT '0' COMMENT 'XXX'
  16.   `sales_modify_time` int(11) NOT NULL DEFAULT '0' COMMENT 'XXX'
  17.   `create_time` int(11) NOT NULL DEFAULT '0' COMMENT '创建时间'
  18.   `modify_time` int(11) NOT NULL DEFAULT '0' COMMENT '最后修改时间'
  19.   `score_modify_time` int(11) NOT NULL DEFAULT '0' COMMENT 'XXX'
  20.   `brand_id` int(11) NOT NULL DEFAULT '0' COMMENT 'XXX'
  21.   `freight_status` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'XXX'
  22.   `flag` tinyint(4) NOT NULL DEFAULT '0' COMMENT 'XXX'
  23.   PRIMARY KEY (`id`), 
  24.   UNIQUE KEY `idx_ad_id` (`ad_id`) USING BTREE, 
  25.   ... 
  26. ) ENGINE=InnoDB AUTO_INCREMENT=2699 DEFAULT CHARSET=utf8 COMMENT='XXXXX'  

执行的replace语句如下(多条):

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

通过查询binlog找到执行记录,部分如下:

  1. ### UPDATE `items`.`active_items` 
  2. ### WHERE 
  3. ###   @1=21926 /* LONGINT meta=0 nullable=0 is_null=0 */ 
  4. ###   @2='XXXXXXX' /* STRING(96) meta=65120 nullable=0 is_null=0 */ 
  5. ###   @3=1 /* TINYINT meta=0 nullable=0 is_null=0 */ 
  6. ###   @4=3900 /* LONGINT meta=0 nullable=0 is_null=0 */ 
  7. ###   @5=315 /* INT meta=0 nullable=0 is_null=0 */ 
  8. ###   @6=1228 /* LONGINT meta=0 nullable=0 is_null=0 */ 
  9. ###   @7=19 /* INT meta=0 nullable=0 is_null=0 */ 
  10. ###   @8=0 /* INT meta=0 nullable=0 is_null=0 */ 
  11. ###   @9=-6 (4294967290) /* INT meta=0 nullable=0 is_null=0 */ 
  12. ###   @10=0 /* TINYINT meta=0 nullable=0 is_null=0 */ 
  13. ###   @11=0 /* INT meta=0 nullable=0 is_null=0 */ 
  14. ###   @12=8 /* INT meta=0 nullable=0 is_null=0 */ 
  15. ###   @13=1489997272 /* INT meta=0 nullable=0 is_null=0 */ 
  16. ###   @14=1495765800 /* INT meta=0 nullable=0 is_null=0 */ 
  17. ###   @15=1495728000 /* INT meta=0 nullable=0 is_null=0 */ 
  18. ###   @16=1489996426 /* INT meta=0 nullable=0 is_null=0 */ 
  19. ###   @17=1489997272 /* INT meta=0 nullable=0 is_null=0 */ 
  20. ###   @18=1495728725 /* INT meta=0 nullable=0 is_null=0 */ 
  21. ###   @19=0 /* INT meta=0 nullable=0 is_null=0 */ 
  22. ###   @20=1 /* TINYINT meta=0 nullable=0 is_null=0 */ 
  23. ###   @21=0 /* TINYINT meta=0 nullable=0 is_null=0 */ 
  24. ### SET 
  25. ###   @1=35313 /* LONGINT meta=0 nullable=0 is_null=0 */ 
  26. ###   @2='XXXXXXX' /* STRING(96) meta=65120 nullable=0 is_null=0 */ 
  27. ###   @3=1 /* TINYINT meta=0 nullable=0 is_null=0 */ 
  28. ###   @4=0 /* LONGINT meta=0 nullable=0 is_null=0 */ 
  29. ###   @5=0 /* INT meta=0 nullable=0 is_null=0 */ 
  30. ###   @6=0 /* LONGINT meta=0 nullable=0 is_null=0 */ 
  31. ###   @7=0 /* INT meta=0 nullable=0 is_null=0 */ 
  32. ###   @8=0 /* INT meta=0 nullable=0 is_null=0 */ 
  33. ###   @9=0 /* INT meta=0 nullable=0 is_null=0 */ 
  34. ###   @10=0 /* TINYINT meta=0 nullable=0 is_null=0 */ 
  35. ###   @11=0 /* INT meta=0 nullable=0 is_null=0 */ 
  36. ###   @12=3150 /* INT meta=0 nullable=0 is_null=0 */ 
  37. ###   @13=0 /* INT meta=0 nullable=0 is_null=0 */ 
  38. ###   @14=0 /* INT meta=0 nullable=0 is_null=0 */ 
  39. ###   @15=0 /* INT meta=0 nullable=0 is_null=0 */ 
  40. ###   @16=0 /* INT meta=0 nullable=0 is_null=0 */ 
  41. ###   @17=0 /* INT meta=0 nullable=0 is_null=0 */ 
  42. ###   @18=0 /* INT meta=0 nullable=0 is_null=0 */ 
  43. ###   @19=0 /* INT meta=0 nullable=0 is_null=0 */ 
  44. ###   @20=0 /* TINYINT meta=0 nullable=0 is_null=0 */ 
  45. ###   @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. 主从一致:

主库:

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

从库:

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

b. 主库REPLACE:

主库:

  1. mysql> replace into t (a,b)values(1,7); 
  2. Query OK, 2 rows affected (0.01 sec) 
  3.  
  4. mysql> show create table t\G            
  5. *************************** 1. row *************************** 
  6.        Table: t 
  7. Create TableCREATE TABLE `t` ( 
  8.   `id` int(11) NOT NULL AUTO_INCREMENT, 
  9.   `a` int(11) DEFAULT '0'
  10.   `b` int(11) DEFAULT '0'
  11.   `c` int(11) DEFAULT '0'
  12.   PRIMARY KEY (`id`), 
  13.   UNIQUE KEY `a` (`a`) 
  14. ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 
  15. 1 row in set (0.00 sec)  

从库:

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

c. 模拟从升主,在从库进行INSERT:

  1. mysql> insert into t (a,b,c)values(4,4,4); 
  2. ERROR 1062 (23000): Duplicate entry '4' for key 'PRIMARY' 
  3. mysql> show create table t\G 
  4. *************************** 1. row *************************** 
  5.        Table: t 
  6. Create TableCREATE TABLE `t` ( 
  7.   `id` int(11) NOT NULL AUTO_INCREMENT, 
  8.   `a` int(11) DEFAULT '0'
  9.   `b` int(11) DEFAULT '0'
  10.   `c` int(11) DEFAULT '0'
  11.   PRIMARY KEY (`id`), 
  12.   UNIQUE KEY `a` (`a`) 
  13. ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 
  14. 1 row in set (0.00 sec) 
  15.  
  16. mysql> insert into t (a,b,c)values(4,4,4); 
  17. 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

责任编辑:庞桂玉 来源: o翡翠谷o的博客
相关推荐

2009-03-25 08:32:33

Windows Ser微软操作系统

2009-03-24 14:29:04

LinuxExt4数据丢失

2024-04-01 08:05:27

Go开发Java

2020-10-10 17:34:11

大数据IT技术

2023-05-04 10:08:00

Windows 10WinAFL二进制

2018-07-30 16:18:51

容灾备份

2017-11-08 11:13:14

大数据Spark数据倾斜

2024-04-03 09:50:33

MySQLMDL事务

2017-07-19 14:26:01

前端JavaScriptDOM

2010-09-09 17:43:03

APC

2021-10-15 06:49:37

MySQL

2019-10-30 14:44:41

Prometheus开源监控系统

2019-05-20 16:30:36

PythonMySQL存储

2010-01-25 11:55:41

MySQL数据库备份数据库安全

2018-01-10 13:40:03

数据库MySQL表设计

2021-09-25 13:05:10

MYSQL开发数据库

2023-03-13 13:36:00

Go扩容切片

2018-09-11 09:14:52

面试公司缺点

2022-04-19 11:48:54

开发npm踩坑

2018-04-26 16:35:44

PythonMySQL存储
点赞
收藏

51CTO技术栈公众号