自拍偷在线精品自拍偷,亚洲欧美中文日韩v在线观看不卡

MySQL進行Replace操作時造成數(shù)據(jù)丟失——那些坑你踩了嗎？

作者：o翡翠谷o
數(shù)據(jù)庫 MySQL
公司開發(fā)人員在更新數(shù)據(jù)時使用了 replace into 語句，由于使用不當導致了數(shù)據(jù)的大量丟失，到底是如何導致的數(shù)據(jù)丟失?本文對此進行分析。

一、問題說明

公司開發(fā)人員在更新數(shù)據(jù)時使用了 replace into 語句，由于使用不當導致了數(shù)據(jù)的大量丟失，到底是如何導致的數(shù)據(jù)丟失?現(xiàn)分析如下。

二、問題分析

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.

如果新插入行的主鍵或唯一鍵在表中已經(jīng)存在，則會刪除原有記錄并插入新行;如果在表中不存在，則直接插入

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

b. 問題現(xiàn)象

丟失數(shù)據(jù)的表結(jié)構(gòu)如下：

  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 '創(chuàng)建時間', 
  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'  

執(zhí)行的replace語句如下(多條)：

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

通過查詢binlog找到執(zhí)行記錄，部分如下：

  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已經(jīng)存在，因此先刪除后插入，可以看到除了指定的 ad_id,score，其他字段都變?yōu)槟J值，導致原有數(shù)據(jù)丟失(雖然在日志中轉(zhuǎn)為了update)

c. 對比測試

接下來我進行了如下測試：

 

  • 左側(cè)使用 REPLACE 語句，右側(cè)使用 DELETE + INSERT 語句，最后結(jié)果完全相同
  • 原主鍵id為1的行被刪除，新插入行主鍵id更新為4，沒有指定內(nèi)容的字段c則插入了默認值
  • 使用 REPLACE 更新了一行數(shù)據(jù)，MySQL提示受影響行數(shù)為2行
  • 綜上所述，說明確實是刪除一行，插入一行

三、數(shù)據(jù)恢復

數(shù)據(jù)丟失或數(shù)據(jù)錯誤后，可以有如下幾種方式恢復：

  1. 業(yè)務方自己寫腳本恢復
  2. 通過MySQL的binlog查出誤操作sql，生成反向sql進行數(shù)據(jù)恢復(適合sql數(shù)據(jù)量較小的情況)
  3. 通過歷史備份文件+增量binlog將數(shù)據(jù)狀態(tài)恢復到誤操作的前一刻

四、問題擴展

通過上述分析可以發(fā)現(xiàn)，REPLACE 會刪除舊行并插入新行，但是binlog中是以update形式記錄，這樣就帶來另一個問題：

從庫自增長值小于主庫

1. 測試

a. 主從一致：

主庫：

  1. mysql> show create table t\G 
  2. *************************** 1. row *************************** 
  3.        Table: t 
  4. Create Table: CREATE 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 Table: CREATE 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 Table: CREATE 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 Table: CREATE 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值已經(jīng)不同了

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 Table: CREATE 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，因此再次執(zhí)行就可以成功插入

2. 結(jié)論

這個問題在平時不會有絲毫影響，但是：

如果主庫平時大量使用 REPLACE 語句，造成從庫 AUTO_INCREMENT 值落后主庫太大，當主從發(fā)生切換后，再次插入數(shù)據(jù)時新的主庫就會出現(xiàn)大量主鍵重復報錯，導致數(shù)據(jù)無法插入。

3. 參考文章

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

責任編輯：龐桂玉 來源： o翡翠谷o的博客
MySQLReplace操作數(shù)據(jù)丟失
相關(guān)推薦

2024-04-01 08:05:27

Go開發(fā)Java

2009-03-24 14:29:04

LinuxExt4數(shù)據(jù)丟失

2009-03-25 08:32:33

Windows Ser微軟操作系統(tǒng)

2023-05-04 10:08:00

Windows 10WinAFL二進制

2020-10-10 17:34:11

大數(shù)據(jù)IT技術(shù)

2018-07-30 16:18:51

容災備份

2017-11-08 11:13:14

大數(shù)據(jù)Spark數(shù)據(jù)傾斜

2024-04-03 09:50:33

MySQLMDL事務

2017-07-19 14:26:01

前端JavaScriptDOM

2021-10-15 06:49:37

MySQL

2010-09-09 17:43:03

APC

2021-09-25 13:05:10

MYSQL開發(fā)數(shù)據(jù)庫

2019-05-20 16:30:36

PythonMySQL存儲

2019-10-30 14:44:41

Prometheus開源監(jiān)控系統(tǒng)

2024-11-26 08:20:53

程序數(shù)據(jù)歸檔庫

2018-01-10 13:40:03

數(shù)據(jù)庫MySQL表設(shè)計

2023-03-15 14:24:02

2022-06-06 00:25:09

Golangpanic死鎖

2018-09-11 09:14:52

面試公司缺點

2023-03-13 13:36:00

Go擴容切片
點贊
收藏

51CTO技術(shù)棧公眾號