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

作者：o翡翠谷o 2017-08-29 10:40:43

公司開發(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ù)的表結構如下：

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

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

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

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

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

c. 對比測試

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

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

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

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

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

四、問題擴展

通過上述分析可以發(fā)現(xiàn)，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值已經(jīng)不同了

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

2. 結論

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

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

3. 參考文章

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

責任編輯：龐桂玉來源： o翡翠谷o的博客

MySQL Replace操作數(shù)據(jù)丟失