成人免费xxxxx在线视频软件_久久精品久久久_亚洲国产精品久久久_天天色天天色_亚洲人成一区_欧美一级欧美三级在线观看

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

作者：o翡翠谷o
數據庫 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 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值已經不同了

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，因此再次執行就可以成功插入

2. 結論

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

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

3. 參考文章

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

責任編輯：龐桂玉 來源： o翡翠谷o的博客
MySQLReplace操作數據丟失
相關推薦

2024-04-01 08:05:27

Go開發Java

2009-03-25 08:32:33

Windows Ser微軟操作系統

2009-03-24 14:29:04

LinuxExt4數據丟失

2023-05-04 10:08:00

Windows 10WinAFL二進制

2020-10-10 17:34:11

大數據IT技術

2018-07-30 16:18:51

容災備份

2017-11-08 11:13:14

大數據Spark數據傾斜

2024-04-03 09:50:33

MySQLMDL事務

2025-05-27 08:45:00

2017-07-19 14:26:01

前端JavaScriptDOM

2025-04-29 10:17:42

2021-10-15 06:49:37

MySQL

2025-05-27 01:55:00

MySQL數據庫工具鏈

2010-09-09 17:43:03

APC

2019-05-20 16:30:36

PythonMySQL存儲

2019-10-30 14:44:41

Prometheus開源監控系統

2023-03-15 14:24:02

2023-03-13 13:36:00

Go擴容切片

2018-09-11 09:14:52

面試公司缺點

2022-06-06 00:25:09

Golangpanic死鎖
點贊
收藏

51CTO技術棧公眾號

主站蜘蛛池模板： 成人午夜在线 | 日本不卡一区二区三区 | 欧美精品一区二区三区四区 在线 | 亚洲国产激情 | 午夜精品一区二区三区在线观看 | 国产福利视频网站 | 国产av毛片 | av网站免费观看 | 另类在线| 日韩中文字幕高清 | 久久免费香蕉视频 | av大片| 91热爆在线观看 | 亚洲成人一区二区三区 | 亚洲第一av | 久久精品99国产精品 | 91视频一区二区 | www久久av| 久久久久国产一区二区三区 | 国产三区四区 | 欧洲一区二区在线 | www亚洲精品 | 精品国产欧美一区二区 | 91精品久久久久久久久 | 欧美激情一区二区三区 | 中文字幕在线看 | 欧美区在线 | 干一干操一操 | 玖玖视频免费 | 国产成人免费视频网站高清观看视频 | xx性欧美肥妇精品久久久久久 | 成人精品一区二区三区 | 欧美视频三区 | 久久一区二区av | 精品欧美色视频网站在线观看 | 日本粉嫩一区二区三区视频 | 色综合欧美| av手机在线播放 | 狠狠操av | 欧美精品一区二区三区四区五区 | 婷婷成人在线 |