10種減少數(shù)據(jù)庫(kù)誤操作的方法

無(wú)論是開(kāi)發(fā)、測(cè)試，還是DBA，都難免會(huì)涉及到數(shù)據(jù)庫(kù)的操作，比如：創(chuàng)建某張表，添加某個(gè)字段、添加數(shù)據(jù)、更新數(shù)據(jù)、刪除數(shù)據(jù)、查詢(xún)數(shù)據(jù)等等。

正常情況下還好，但如果操作數(shù)據(jù)庫(kù)時(shí)出現(xiàn)失誤，比如：

刪除訂單數(shù)據(jù)時(shí)where條件寫(xiě)錯(cuò)了，導(dǎo)致多刪了很多用戶(hù)訂單。

更新會(huì)員有效時(shí)間時(shí)，一次性把所有會(huì)員的有效時(shí)間都更新了。

修復(fù)線上數(shù)據(jù)時(shí)，改錯(cuò)了，想還原。

還有很多很多場(chǎng)景，我就不一一列舉了。

如果出現(xiàn)線上環(huán)境數(shù)據(jù)庫(kù)誤操作怎么辦？有沒(méi)有后悔藥？

答案是有的，請(qǐng)各位看官仔細(xì)往下看。

1.不要用聊天工具發(fā)sql語(yǔ)句

通常開(kāi)發(fā)人員寫(xiě)好sql語(yǔ)句之后，習(xí)慣通過(guò)聊天工具，比如：qq、釘釘、或者騰訊通等，發(fā)給團(tuán)隊(duì)老大或者DBA在線上環(huán)境執(zhí)行。但由于有些聊天工具，對(duì)部分特殊字符會(huì)自動(dòng)轉(zhuǎn)義，而且有些消息由于內(nèi)容太長(zhǎng)，會(huì)被自動(dòng)分成多條消息。

這樣會(huì)導(dǎo)致團(tuán)隊(duì)老大或者DBA復(fù)制出來(lái)的sql不一定是正確的。

他們需要手動(dòng)拼接成一條完整的sql，有時(shí)甚至需要把轉(zhuǎn)義后的字符替換回以前的特殊字符，無(wú)形之中會(huì)浪費(fèi)很多額外的時(shí)間。即使最終sql拼接好了，真正執(zhí)行sql的人，心里一定很虛。

所以，強(qiáng)烈建議你把要在線上執(zhí)行的sql語(yǔ)句用郵件發(fā)過(guò)去，可以避免使用聊天工具的一些弊端，減少一些誤操作的機(jī)會(huì)。而且有個(gè)存檔，方便今后有問(wèn)題的時(shí)候回溯原因。很多聊天工具只保留最近7天的歷史記錄，郵件會(huì)保留更久一些。

別用聊天工具發(fā)sql語(yǔ)句！

別用聊天工具發(fā)sql語(yǔ)句！

別用聊天工具發(fā)sql語(yǔ)句！

重要的事情說(shuō)三遍，它真的能減少一些誤操作。

2.把sql語(yǔ)句壓縮成一行

有些時(shí)候，開(kāi)發(fā)人員寫(xiě)的sql語(yǔ)句很長(zhǎng)，使用了各種join和union，而且使用美化工具，將一條sql變成了多行。在復(fù)制sql的時(shí)候，自己都無(wú)法確定sql是否完整。（為了裝逼，把自己也坑了，哈哈哈）

線上環(huán)境有時(shí)候需要通過(guò)命令行連接數(shù)據(jù)庫(kù)，比如：mysql，你把sql語(yǔ)句復(fù)制過(guò)來(lái)后，在命令行界面執(zhí)行，由于屏幕滾動(dòng)太快，這時(shí)根本無(wú)法確定sql是否都執(zhí)行成功。

針對(duì)這類(lèi)問(wèn)題，強(qiáng)烈建議把sql語(yǔ)句壓縮成一行，去掉多余的換行符和空格，可以有效的減少一些誤操作。

sql壓縮工具推薦使用：https://tool.lu/sql/

3.操作數(shù)據(jù)之前先select一下

需要特別說(shuō)明的是：本文的操作數(shù)據(jù)主要指修改和刪除數(shù)據(jù)。

很多時(shí)候，由于我們?nèi)藶槭д`，把where條件寫(xiě)錯(cuò)了。但沒(méi)有怎么仔細(xì)檢查，就把sql語(yǔ)句直接執(zhí)行了。影響范圍小還好，如果影響幾萬(wàn)、幾十萬(wàn)，甚至幾百萬(wàn)行數(shù)據(jù)，我們可能要哭了。

針對(duì)這種情況，在操作數(shù)據(jù)之前，把sql先改成select count（*）語(yǔ)句，比如：

update order set status=1 where status=0;

改成：

select count（*） from order where status=0;

查一下該sql執(zhí)行后影響的記錄行數(shù)，做到自己心中有數(shù)。也給自己一次測(cè)試sql是否正確，確認(rèn)是否執(zhí)行的機(jī)會(huì)。

4.操作數(shù)據(jù)sql加limit

即使通過(guò)上面的select語(yǔ)句確認(rèn)了sql語(yǔ)句沒(méi)有問(wèn)題，執(zhí)行后影響的記錄行數(shù)是對(duì)的。

也建議你不要立刻執(zhí)行，建議在正在執(zhí)行的時(shí)候，加上limit + select出的記錄行數(shù)。例如：

update order set status=1 where status=0 limit 1000;

假設(shè)有一次性更新的數(shù)據(jù)太多，所有相關(guān)記錄行都會(huì)被鎖住，造成長(zhǎng)時(shí)間的鎖等待，而造成用戶(hù)請(qǐng)求超時(shí)。

此外，加limit可以避免一次性操作太多數(shù)據(jù)，對(duì)服務(wù)器的cpu造成影響。

還有一個(gè)最重要的原因：加limit后，操作數(shù)據(jù)的影響范圍是完全可控的。

5.update時(shí)更新修改人和修改時(shí)間

很多人寫(xiě)update語(yǔ)句時(shí)，如果要修改狀態(tài)，就只更新?tīng)顟B(tài)，不管其他的字段。比如：

update order set status=1 where status=0;

這條sql會(huì)把status等于0的數(shù)據(jù)，全部更新成1。

后來(lái)發(fā)現(xiàn)業(yè)務(wù)邏輯有問(wèn)題，不應(yīng)該這么更新，需要把status狀態(tài)回滾。

這時(shí)你可能會(huì)很自然想到這條sql：

update order set status=0 where status=1;

但仔細(xì)想想又有些不對(duì)。

這樣不是會(huì)把有部分以前status就是1的數(shù)據(jù)更新成0？

這回真的要哭了，嗚嗚嗚。

這時(shí)，送你一個(gè)好習(xí)慣：在更新數(shù)據(jù)的時(shí)候，同時(shí)更新修改人和修改時(shí)間字段。

update order set status=1，edit_date=now（），edit_user=‘admin’ where status=0;

這樣在恢復(fù)數(shù)據(jù)時(shí)就能通過(guò)修改人和修改時(shí)間字段過(guò)濾數(shù)據(jù)了。

后面需要用到的修改時(shí)間通過(guò)這條sql語(yǔ)句可以輕松找到：

select edit_user ，edit_date from `order` order by edit_date desc limit 50;

當(dāng)然，如果是高并發(fā)系統(tǒng)不建議這種批量更新方式，可能會(huì)鎖表一定時(shí)間，造成請(qǐng)求超時(shí)。

有些同學(xué)可能會(huì)問(wèn)：為什么要同時(shí)更新修改人，只更新修改時(shí)間不行嗎？

主要有如下的原因：

為了標(biāo)識(shí)非正常用戶(hù)操作，方便后面統(tǒng)計(jì)和定位問(wèn)題。

有些情況下，在執(zhí)行sql語(yǔ)句的過(guò)程中，正常用戶(hù)產(chǎn)生數(shù)據(jù)的修改時(shí)間跟你的可能一模一樣，導(dǎo)致回滾時(shí)數(shù)據(jù)查多了。

6.多用邏輯刪除，少用物理刪除

在業(yè)務(wù)開(kāi)發(fā)中，刪除數(shù)據(jù)是必不可少的一種業(yè)務(wù)場(chǎng)景。

有些人開(kāi)發(fā)人員習(xí)慣將表設(shè)計(jì)成物理刪除，根據(jù)主鍵只用一條delete語(yǔ)句就能輕松搞定。

他們給出的理由是：節(jié)省數(shù)據(jù)庫(kù)的存儲(chǔ)空間。

想法是好的，但是現(xiàn)實(shí)很殘酷。

如果有條極重要的數(shù)據(jù)刪錯(cuò)了，想恢復(fù)怎么辦？

此時(shí)只剩八個(gè)字：沒(méi)有數(shù)據(jù)，恢復(fù)不了。（PS：或許通過(guò)binlog二進(jìn)制文件可以恢復(fù)）

如果之前設(shè)計(jì)表的時(shí)候用的邏輯刪除，上面的問(wèn)題就變得好辦了。刪除數(shù)據(jù)時(shí)，只需update刪除狀態(tài)即可，例如：

update order set del_status=1，edit_date=now（），edit_user=‘a(chǎn)dmin’ where id=123;

假如出現(xiàn)異常，要恢復(fù)數(shù)據(jù)，把該id的刪除狀態(tài)還原即可，例如：

update order set del_status=0，edit_date=now（），edit_user=‘a(chǎn)dmin’ where id=123;

7.操作數(shù)據(jù)之前先做備份

如果只是修改了少量的數(shù)據(jù)，或者只執(zhí)行了一兩條sql語(yǔ)句，通過(guò)上面的修改人和修改時(shí)間字段，在需要回滾時(shí)，能快速的定位到正確的數(shù)據(jù)。

但是如果修改的記錄行數(shù)很多，并且執(zhí)行了多條sql，產(chǎn)生了很多修改時(shí)間。這時(shí)，你可能就要犯難了，沒(méi)法一次性找出哪些數(shù)據(jù)需要回滾。

為了解決這類(lèi)問(wèn)題，可以將表做備份。

可以使用如下sql備份：

create table order_bak_2021031721 like`order`;

insert into order_bak_2021031721 select * from`order`;

先創(chuàng)建一張一模一樣的表，然后把數(shù)據(jù)復(fù)制到新表中。

也可以簡(jiǎn)化成一條sql：

create table order_bak_2021031722 select * from`order`;

創(chuàng)建表的同時(shí)復(fù)制數(shù)據(jù)到新表中。

此外，建議在表名中加上bak和時(shí)間，一方面是為了通過(guò)表名快速識(shí)別出哪些表是備份表，另一方面是為了備份多次時(shí)好做區(qū)分。因?yàn)橛袝r(shí)需要執(zhí)行多次sql才能把數(shù)據(jù)修復(fù)好，這種情況建議把表備份多次，如果出現(xiàn)異常，把數(shù)據(jù)回滾到最近的一次備份，可以節(jié)省很多重復(fù)操作的時(shí)間。

恢復(fù)數(shù)據(jù)時(shí)，把sql語(yǔ)句改成select語(yǔ)句，先在備份庫(kù)找出相關(guān)數(shù)據(jù)，每條數(shù)據(jù)對(duì)應(yīng)一條update語(yǔ)句，還原到老表中。

8.中間結(jié)果寫(xiě)入臨時(shí)表

有時(shí)候，我們要先用一條sql查詢(xún)出要更新的記錄的id，然后通過(guò)這些id更新數(shù)據(jù)。

批量更新之后，發(fā)現(xiàn)不對(duì)，要回滾數(shù)據(jù)。但由于有些數(shù)據(jù)已更新，此時(shí)使用相同的sql相同的條件，卻查不出上次相同的id了。

這時(shí)，我們開(kāi)始慌了。

針對(duì)這種情況，我們可以先將第一次查詢(xún)的id存入一張臨時(shí)表，然后通過(guò)臨時(shí)表中的id作為查詢(xún)條件更新數(shù)據(jù)。

如果要恢復(fù)數(shù)據(jù)，只用通過(guò)臨時(shí)表中的id作為查詢(xún)條件更新數(shù)據(jù)即可。

修改完，3天之后，如果沒(méi)有出現(xiàn)問(wèn)題，就可以把臨時(shí)表刪掉了。

9.表名前面一定要帶庫(kù)名

我們?cè)趯?xiě)sql時(shí)為了方便，習(xí)慣性不帶數(shù)據(jù)庫(kù)名稱(chēng)。比如：

update order set status=1，edit_date=now（），edit_user=‘a(chǎn)dmin’ where status=0;

假如有多個(gè)數(shù)據(jù)庫(kù)中有相同的表order，表結(jié)構(gòu)一模一樣，只是數(shù)據(jù)不一樣。

由于執(zhí)行sql語(yǔ)句的人一個(gè)小失誤，進(jìn)錯(cuò)數(shù)據(jù)庫(kù)了。

use trade1;

然后執(zhí)行了這條sql語(yǔ)句，結(jié)果悲劇了。

有個(gè)非常有效的預(yù)防這類(lèi)問(wèn)題的方法是加數(shù)據(jù)庫(kù)名：

update `trade2`。`order` set status=1，edit_date=now（），edit_user=‘a(chǎn)dmin’ where status=0;

這樣即使執(zhí)行sql語(yǔ)句前進(jìn)錯(cuò)數(shù)據(jù)庫(kù)了，也沒(méi)什么影響。

10.字段增刪改的限制

很多時(shí)候，我們少不了對(duì)表字段的操作，比如：新加、修改、刪除字段，但每種情況都不一樣。

新加的字段一定要允許為空

新加的字段一定要允許為空。為什么要這樣設(shè)計(jì)呢？

正常情況下，如果程序新加了字段，一般是先在數(shù)據(jù)庫(kù)中加字段，然后再發(fā)程序的最新代碼。

為什么是這種順序？

因?yàn)槿绻劝l(fā)程序，然后在數(shù)據(jù)庫(kù)中加字段。在該程序剛部署成功，但數(shù)據(jù)庫(kù)新字段還沒(méi)來(lái)得及加的這段時(shí)間內(nèi)，最新程序中，所有使用了新加字段的增刪改查sql都會(huì)報(bào)字段不存在的異常。

好了，就按先在數(shù)據(jù)庫(kù)中加字段，再發(fā)程序的順序。

如果數(shù)據(jù)庫(kù)中新加的字段非空，最新的程序還沒(méi)發(fā)，線上跑的還是老代碼，這時(shí)如果有insert操作，就會(huì)報(bào)字段不能為空的異常。因?yàn)樾录拥姆强兆侄?，老代碼是沒(méi)法賦值的。

所以說(shuō)新加的字段一定要允許為空。

除此之外，這種設(shè)計(jì)更多的考慮是為了程序發(fā)布失敗時(shí)的回滾操作。如果新加的字段允許為空，則可以不用回滾數(shù)據(jù)庫(kù)，只需回滾代碼即可，是不是很方便？

不允許刪除字段

刪除字段是不允許的，特別是必填字段一定不能刪除。

為什么這么說(shuō)？

假設(shè)開(kāi)發(fā)人員已經(jīng)把程序改成不使用刪除字段了，接下來(lái)如何部署呢？

如果先把程序部署好了，還沒(méi)來(lái)得及刪除數(shù)據(jù)庫(kù)相關(guān)表字段。當(dāng)有insert請(qǐng)求時(shí)，由于數(shù)據(jù)庫(kù)中該字段是必填的，會(huì)報(bào)必填字段不能為空的異常。

如果先把數(shù)據(jù)庫(kù)中相關(guān)表字段刪了，程序還沒(méi)來(lái)得及發(fā)。這時(shí)所有涉及該刪除字段的增刪改查，都會(huì)報(bào)字段不存在的異常。

所以，線上環(huán)境必填字段一定不能刪除的。

根據(jù)實(shí)際情況修改字段

修改字段要分為這三種情況：

1.修改字段名稱(chēng)

修改字段名稱(chēng)也不允許，跟刪除必填字段的問(wèn)題差不多。

如果把程序部署好了，還沒(méi)來(lái)得及修改數(shù)據(jù)庫(kù)中表字段名稱(chēng)。這時(shí)所有涉及該字段的增刪改查，都會(huì)報(bào)字段不存在的異常。

如果先把數(shù)據(jù)庫(kù)中字段名稱(chēng)改了，程序還沒(méi)來(lái)得及發(fā)。這時(shí)所有涉及該字段的增刪改查，同樣也會(huì)報(bào)字段不存在的異常。

所以，線上環(huán)境字段名稱(chēng)一定不要修改。

2.修改字段類(lèi)型

修改字段類(lèi)型時(shí)一定要兼容之前的數(shù)據(jù)。例如：

tinyint改成int可以，但int改成tinyint要仔細(xì)衡量一下。

varchar改成text可以，但text改成varchar要仔細(xì)衡量一下。

3.修改字段長(zhǎng)度

字段長(zhǎng)度建議改大，通常情況下，不建議改小。如果一定要改小，要先確認(rèn)該字段可能會(huì)出現(xiàn)的最大長(zhǎng)度，避免insert操作時(shí)出現(xiàn)字段太長(zhǎng)的異常。

此外，建議改大也需要設(shè)置一個(gè)合理的長(zhǎng)度，避免數(shù)據(jù)庫(kù)資源浪費(fèi)。

總結(jié)

本文分享了10種減少數(shù)據(jù)庫(kù)誤操作的方法，并非所有場(chǎng)景都適合你。特別是在一些高并發(fā)，或者單表數(shù)據(jù)量非常大的場(chǎng)景，你需要根據(jù)實(shí)際情況酌情選擇。但我敢肯定的是讀完這篇文章，你一定會(huì)有一些收獲，因?yàn)榇蟛糠址椒▽?duì)你來(lái)說(shuō)是適用的，可能會(huì)讓你少走很多彎路，強(qiáng)烈建議收藏。

責(zé)任編輯：haq