小心駛得萬年船--SQL指令保險栓

手動對資料庫下指令是一件恐怖的事,稍一操作不慎,就有可能把整個系統給毁了。

理論上,吾人應該極力避免手工更動資料這等可恥行徑。只要系統考慮得夠周詳,預先料想到所有可能出現的詭異狀況,一一提供相關的介面,經過程式邏輯檢查後才對資料進行處置或修正,不可能出現需要手動改資料的狀況。這是一個好的系統應有的嚴謹度!!

好,官冕堂皇的屁話說完了,現在來聊聊怎麼做好這件"可恥的事"? (道德感強烈者或軟體工程基本教義派請略過本文)

當我們萬不得已,必須使用T-SQL指令直接對資料庫進行操作時,由於指令不受到任何既有程式邏輯的保護,條件設定稍有不當,很有可能本來只要更新一筆,變成更新1000筆;想刪除某個人的資料卻把整個表都清除。因此,除了操作前要反覆檢查指令外,再三提醒自己小心謹慎外,還有一些小技巧:

  1. 請同事幫忙檢核指令
    每個人都有盲點,在緊急狀況下尤其更會因情緒、壓力而失誤。因此,針對重大更動指令,可仿效核武啟用程序,艦長的發射指令需要經過副艦長、武器官的檢核後才允許被執行。以減少個人疏忽可能引發的災難。
  2. 用begin tran保留反悔機會
    在SQL Sever Management Studio/Query Analyzer上的指令一經執行,就像射出去的箭,想抓都抓不回來。利用begin tran宣告成Transaction,最後的commit tran加上Remark,可以防止手滑按下執行時發生不測。
  3. 分段執行並觀察更動筆數
    如果更動指令有多段,可分段選取及執行, 並留意每段指令更動筆數是否如預期,若有不吻合的狀況,請立刻用rollback tran取消。
  4. UPDATE/DELETE前的範圍檢查
    利用以下技巧,可以在UPDATE, DELETE前先檢查WHERE條件的範圍是否符合預期。另外,用Remark將有殺傷力的部分包裝起來,必須要明確選取才能執行,可防止誤跑傷人。
  5. WHERE條件不嫌多
    在更新刪除資料時,若WHERE條件不是Primay Key,不妨多加幾項比對條件。例如: 除了比對姓名外,順便檢查生日、加入會員日期等,很多時候,某些欄位的唯一性並不如我們想像。
  6. 設定手動commit
    SQL Server Management Studio可以預設成每次執行都自動包成Transaction,且不自動Commit。設定後,每次跑完指令都要多下commit tran才算數,較為麻煩,但本著流汗總比流血好的哲學,也是可以考慮。
  7. 快速表格備份
    忘了說,還有一招: SELECT * INTO BackupTableName FROM TableName可以快速把表格內容複製一份保留下來,更新後可用來比對或修復資料,非常好用。

PS: 以上技巧,部分習自小熊子的獨門心法,特此鳴謝!

歡迎推文分享:
Published 26 July 2009 07:47 AM 由 Jeffrey
Filed under: , , ,
Views: 56,376



意見

# Wilson Shen said on 26 July, 2009 03:44 AM

設定手動 Commit 應該是最保險的做法 !!

# steve said on 26 July, 2009 09:03 AM

按下執行之後突然想到一件事,然後整個人從背脊到腳底發涼

同時口中會不自主冒出e04

只要有體驗過這種事的人,絕對會舉雙手雙腳贊同這篇文章

個人最常用的是4

然後執行前一定一個字一個字再把指令看一遍

就算是再簡單的SQL也一樣,絕對不能讓自己有"這SQL很簡單,不會錯的"這種鬆懈的心態

# DAVE said on 26 July, 2009 06:08 PM

用SQL , UPDATE & DELETE 時,我會先寫成 SELECT .... 驗證條件是否正確。正確之後把SELECT 置換成 UPDATE&DELETE 寫法。

人恍惚常常會做一些笨事。 XD

# zerome said on 26 July, 2009 08:50 PM

為保護公司與同仁身家安全

這種事我們都推給DBA執行...

XD...

作業面就補上個異動程序文件請DBA照著做就對了...

# Ark said on 27 July, 2009 05:12 AM

to zerome

這樣會不會太"嚴謹"?

很多上位者只用嘴吩咐

也不管你是不是DBA

更不用管你有沒領DBA的薪水

不要逼人truncate啊

沒drop還只是一念之仁

# dyco said on 27 July, 2009 09:49 PM

我們那就曾發生過~~DBA搞錯

把Test資料整個copy paste到Prod

全部Prod 資料庫資料被蓋掉~~後來救不回來~~~只能還原到上次的備份點 囧

# zerome said on 27 July, 2009 10:08 PM

Ark 兄

或許你不相信

但我們老闆真的要求到這種地步

production的DB 只有DBA能碰

要改資料 請洽DBA 但有憑有據

所以大到改帳 小到一筆的一個欄位異動 都要寫紀錄

要異動前資料看範圍來備份

這樣 異動前後就有軌跡可循

雖然麻煩 但實施久了 還真的挺放心的

不過 DBA可就累了

況且我認為 這只適合中小型的企業

大企業這個方式應該行不通....要換個方式做才行

# steve said on 28 July, 2009 02:56 AM

to Ark

其實這樣要求的公司也不算少,至少我個人待過的公司中就有兩家有這樣的要求, 所有東西一定要由DBA執行

不過,上有政策,下就會有對策,只是這對策會讓update出錯的風險變得更高....XD

# 小賤健 said on 04 August, 2009 10:02 PM

重新回頭來看這文。

我發現我在操作 Update / Delete 的指令後,嘴裡噴 e04 的機會大大降低了,這算是一種進步了嗎XD

# keng said on 11 August, 2009 01:22 AM

我會在測試資料庫先模擬,異動比數是否符合預期,在三檢視無誤在減下指令貼到正式資料庫

千萬不能發生e04事件~~這可不是e04能解決的了

即使每天備份,一天的資料可要好幾萬薪水阿,還不知道全不全

# tomexou said on 17 March, 2013 01:55 AM

我覺得第一件事,應該就是把db整個作個備份,再來下t-sql才對。這習慣一定得培養,尤其是正式的資料庫操作時,一定要常常備份。(可透過備份sql指令一鍵完成為

你的看法呢?

(必要的) 
(必要的) 
(選擇性的)
(必要的) 
(提醒: 因快取機制,您的留言幾分鐘後才會顯示在網站,請耐心稍候)

5 + 3 =

搜尋

Go

<July 2009>
SunMonTueWedThuFriSat
2829301234
567891011
12131415161718
19202122232425
2627282930311
2345678
 
RSS
創用 CC 授權條款
【廣告】
twMVC
最新回應

Tags 分類檢視
關於作者

一個醉心技術又酷愛分享的Coding魔人,十年的IT職場生涯,寫過系統、管過專案, 也帶過團隊,最後還是無怨無悔地選擇了技術鑽研這條路,近年來則以做一個"有為的中年人"自許。

文章典藏
其他功能

這個部落格


Syndication