MySQL 進行 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. 問題現象

丟失資料的表結構如下：

執行的replace語句如下（多條）：

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

• 操作的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. 主從一致：

b. 主庫REPLACE：

• 注意此時主從兩個表的AUTO_INCREMENT值已經不同了

c. 模擬從升主，在從庫進行INSERT：

• 從庫插入時會報錯，主鍵重複，報錯後AUTO_INCREMENT會 +1，因此再次執行就可以成功插入

2. 結論

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

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

3. 參考文章

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