MySQL使用Replace操作時(shí)造成數(shù)據(jù)丟失的問題解決

前言

公司開發(fā)人員在更新數(shù)據(jù)時(shí)使用了 replace into 語句，由于使用不當(dāng)導(dǎo)致了數(shù)據(jù)的大量丟失，到底是如何導(dǎo)致的數(shù)據(jù)丟失本文對(duì)此進(jìn)行分析。

一、問題說明

公司開發(fā)人員在更新數(shù)據(jù)時(shí)使用了 replace into 語句，由于使用不當(dāng)導(dǎo)致了數(shù)據(jù)的大量丟失，到底是如何導(dǎo)致的數(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)存在，則會(huì)刪除原有記錄并插入新行;如果在表中不存在，則直接插入

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

b. 問題現(xiàn)象

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

CREATE TABLE `active_items` (

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

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

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

### UPDATE `items`.`active_items`
### @21=0 /* TINYINT meta=0 nullable=0 is_null=0 */

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

c. 對(duì)比測(cè)試

接下來我進(jìn)行了如下測(cè)試：

• MySQL進(jìn)行Replace操作時(shí)造成數(shù)據(jù)丟失
• 左側(cè)使用 REPLACE 語句，右側(cè)使用 DELETE + INSERT 語句，最后結(jié)果完全相同
• 原主鍵id為1的行被刪除，新插入行主鍵id更新為4，沒有指定內(nèi)容的字段c則插入了默認(rèn)值
• 使用 REPLACE 更新了一行數(shù)據(jù)，MySQL提示受影響行數(shù)為2行

綜上所述，說明確實(shí)是刪除一行，插入一行

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

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

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

四、問題擴(kuò)展

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

從庫(kù)自增長(zhǎng)值小于主庫(kù)

1. 測(cè)試

a. 主從一致：

主庫(kù)：

mysql> show create table tG

從庫(kù)：

mysql> show create table tG

b. 主庫(kù)REPLACE：

主庫(kù)：

mysql> replace into t (a,b)values(1,7);

從庫(kù)：

mysql> show create table tG

注意此時(shí)主從兩個(gè)表的AUTO_INCREMENT值已經(jīng)不同了

c. 模擬從升主，在從庫(kù)進(jìn)行INSERT：

mysql> insert into t (a,b,c)values(4,4,4);

從庫(kù)插入時(shí)會(huì)報(bào)錯(cuò)，主鍵重復(fù)，報(bào)錯(cuò)后AUTO_INCREMENT會(huì) +1，因此再次執(zhí)行就可以成功插入

2. 結(jié)論

這個(gè)問題在平時(shí)不會(huì)有絲毫影響，但是：

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

總結(jié)

以上就是這篇文章的全部?jī)?nèi)容了，希望本文的內(nèi)容對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，謝謝大家對(duì)腳本之家的支持。