MySQL存儲過程調試技巧

鄧 林

(四川職業技術學院計算機科學系，四川 遂寧 629000)

MySQL存儲過程調試技巧

鄧 林

(四川職業技術學院計算機科學系，四川 遂寧 629000)

由于高校對存儲過程方面的研究還比較少，大部分有這方面工作經驗的人員也在企業之中.所以本文歸納出MySQL存儲過程方面相對較全面的調試技巧.實踐結果表明，本文所研究實現的存儲過程調試技巧將大大提高開發人員的工作效率，同時也間接提高了存儲過程的運行性能.

MySQL；存儲過程；調試；技巧；效率

隨著國家大力提倡“互聯網+”，現在各行各業都在大量使用互聯網來重構本行業的數據系統，這種趨勢已經滲透到各個行業.同時，數據的重要性也越發明顯，每個企業都會越來越重視各企業所產生的數據，這也間接推動了數據庫的大量使用.作為全球最優秀的開源數據庫,MySQL數據庫的市場占用率也是顯著提高，但目前在高校中的數據庫課程中，大部分還是選擇界面更加優秀的SQL Server數據庫，這主要有兩方面原因：其一，延續歷史傳統，從 SQL Server 2000到SQL Server 2005，高校教材均是選用這一系列的數據庫教學，這樣任課老師也能延續自己之前所準備的課程；其二，Oracle、DB2、MySQL等主流數據庫均是命令行式的數據庫，均未有官方標準的圖形化界面數據庫，這讓任課老師去選擇哪一種非官方的圖形化界面來教學，均不利于學生對課程掌握.隨著阿里巴巴提倡“去I O E”之后，會有更多企業選擇免費并且性能全面的開源數據庫.存儲過程作為每個數據庫差異較大的一個知識點，對其的研究也就勢在必行，那么其具體的調試技巧也很明顯有所差異，所以需要針對具體數據庫來講解其調試技巧[1][2].

1 MySQL調試技巧

結合之前的MySQL數據庫開發工作，在寫調試技巧之前，有些代碼風格在這里先提一下，其實這些風格也對其調試有莫大的幫助.第一，所有關鍵字都最好大寫，這對于閱讀代碼的人更加直觀；第二，在創建存儲過程時學會使用DEFINER，這可以提高你代碼的權限控制，尤其是現在這個權限控制要求較高的行業來說；第三，定義變量時，保持固定風格，比如輸入變量都加上_i，輸出變量都加_o，其它臨時變量都以v_開頭，這些風格在代碼較多時，可以更加容易閱讀代碼.其實寫到這里，可以發現，調試技巧與代碼風格是分不開的，有些風格也是促進提高你的調試技巧的.下面就從各方面來分析存儲過程的調試技巧.

1.1 DECLARE申明

我們先來看看MySQL官方是如何解釋這個關鍵字的：

DECLARE is permitted only inside a BEGIN ...END compound statement and must be at its start,before any other statements.

從這句話也可以看出，DECLARE定義時必須放在所有語句之前，而不是像其它高級語言那樣，變量在使用之前定義即可，而不需要在最開始就定義好.而DECLARE語句之間的定義，則沒有先后順序，比如定義變量或者定義錯誤處理機制就沒有先后順序.

1.2 DECLARE ...HANDLER

DECLARE ...HANDLER主要是存儲過程的異常處理機制，這對程序的健壯性起了很大的作用，而且利用好這個機制對調試代碼也相當有幫助.這個容錯機制是在程序執行過程中發生一些條件來觸發的，處理的方式可以結束程序，也可以忽略這些錯誤繼續執行程序.觸發這個機制主要有三類：SQLWARNING,NOT FOUND, SQLEXCEPTION.即程序遇到這三類錯誤時，可以跳到DECLARE…HANDLER 代碼所定義之處，按照定義的規則來執行.下面以例子說明：

①DECLARE EXIT HANDLER FOR SQLWARNING,SQLEXCEPTION BEGIN

② SHOW WARNINGS;

③ SET returnCode_o = 400;

④ SET returnMsg_o = 'Get Events Error.';

⑤ CALL `sds`.`log.e` (user_i,CONCAT('User#',user_i,' devices request list failed.'));

⑥END;

上面代碼中第一行是定義一個HANDLER，如果程序遇到SQLWARNING,

SQLEXCEPTIONG兩類錯誤時，則執行BEGIN…END里面的代碼，執行完后結束程序，因為這里使用的是EXIT.

第二行，顯示代碼中遇到錯誤的警告，正常情況下，存儲過程遇到警告是會中止代碼的，這點跟其它高級語言不一樣.這里會顯示警告的詳細信息，這樣也方便你調試程序，如果沒有這一行，那么在程序遇到警告時，它不會顯示警告信息，因為這里已經異常處理了.那么是不是永遠加上這一句呢，那我們試想一下，高級語言調用存儲過程時，出了錯，那么SHOW WARNING就會起作用，會把警告信息返回給高級語言，也即會返回一大串英文單詞，這是高級語言想要的東西嗎，肯定不是，所以SHOW WARNING在調試的時候可以很方便的使用，但在你的存儲過程交付后就應該注釋它，如果后面發現錯誤需要調試時，再把注釋去掉進行調試.

第三、四行是設置輸出函數的代碼以及返回的簡單錯誤信息.這個錯誤信息是開發人員自己定義的，即開發人員想返回給高級語言的錯誤信息，它比數據庫的錯誤信息更加簡潔明了.

第五行調用日志存儲過程，關于日志存儲過程后面再述說.

總之，DECLARE…HANDLER模塊可以在程序遇到錯誤即將退出存儲過程之前必須執行的一段程序，把遇到的錯誤信息返回給調用它的代碼，讓接受到錯誤信息的代碼知道從哪個方面入手去解決這個錯誤，大大提高了程序的容錯處理能力.

1.3 日志存儲過程

日志存儲過程應該在每個數據庫中存在一份，它的主要功能是把調用存儲過程的相關信息插入日志表中.包括調用存儲過程的用戶、輸入參數的實際值等信息以字符串的信息插入日志表中.在遇到錯誤信息時，開發人員如果不能根據返回的信息推斷出程序出錯點，則需要根據日志表中的輸入數據去調用存儲過程，因為這些數據是實際傳入的數據，開發人員根據這些數據去調試存儲過程，當然就能更迅速地定位到錯誤點.通常日志表可以按下表來設計：

?

1.4 S E L E C T...N T O語句

在存儲過程中經常會遇到從數據庫中查詢一個值，并將其值賦值給一個變量，那么一般使用的語句即是SELECT…INTO語句，如下面這條語句：

select a1.end_time into @v_end_t1 from analysis2.trip a1 where a1.id = 76

這條語句即是從數據庫中獲取結束時間并將其值賦值給@v_end_t 1，正常情況這條語句沒有問題，但是如果查詢出的值是NULL值，將NULL賦值給@v_end_t 1時，程序會出錯的，尤其是像在游標這些地方，如果這種錯誤出現，游標也會提前中止.這種錯誤還不容易發現，因為開發人員調試時一般都是有值的，而不會是NULL，那么這種隱含的錯誤解決辦法是，將查詢的字段放在一個聚合函數M A X()M I N()之內的.像上面這段代碼可改為

select max (a1.end_time) into @v_end_t1from analysis2.trip a1 where a1.id = 76

1.5 單步調試

眾所周知，存儲過程因為其特性，沒有支持像高級語言那樣的單步調試功能，那么在調試復雜的存儲過程我們應該怎么辦呢.在高級語言中，可以利用輸出語句將代碼過程中的一些變量輸出，來查看其值，但在My SQL存儲過程中沒有輸出函數.所以唯一的辦法是合理利用select語句.如果需要輸出變量值時，直接“select變量名”即可，如果要查看某張表當前數據，則使用“select*from table”即可，甚至可以通過“select“aaaaa””這樣的無意義的語句來查看存儲過程是執行到哪一行出錯的.還有一個地方也要善于使用這種方法來查看其值，當調用子存儲過程時，在調試時，一定要將子存儲過程返回值查詢出來看看，其值是否是正確的.

1.6 臨時表

不管是存儲過程還是函數，能傳遞的值都有限，那如果需要傳遞多條記錄的數據時，應該怎么做呢.或者當子存儲過程執行的結果是多行數據的結果集，主存儲過程如何才能使用這個結果集呢.這個時候就是臨時表派上用場了，在整個存儲過程運行期間，臨時表都會一直存在的，所以可以將子存儲過程的結果存儲臨時表，然后在主存儲過程中將值取出來參與運算.那么在這其中我們需要注意以下幾點：

第一，臨時表的字段類型必須和最終結果表一致，包括類型長度也要一致，這是防止臨時表中的字段類型長度大于結果表的長度，如果插入的數據的長度剛好等于臨時表中的長度但卻大于結果表的長度，這樣的話，插入數據到結果表時很容易就出錯了；如果臨時表的長度小于結果表的長度，這樣雖然插入時不會出錯，但很有可能隱藏一些潛在的錯誤，這在筆者之前的工作中，因為沒有完全保持一致，就出現過一些錯誤，而且這些錯誤也是很不容易發現的.

第二，在臨時表使用完畢后，一定要記得刪除臨時表，因為不刪除，程序如果頻繁啟動時，很容易出現第二次啟動了，原來的臨時表還存在.這個刪除不僅僅是程序正常結束時要刪除，而且程序異常結束時也一定要刪除，所以在DECLARE…HANDLER模塊里面一定也要把臨時表刪除掉.

第三，創建臨時表時，選擇的引擎最好選用Myisam，因為臨時表在存儲過程中被使用最多是存儲臨時數據，那么主要就是插入數據，關于這一點，Myisam相較于Innodb更有優勢.

1.7 IF EXISTS

在存儲過程中，創建任何元素，都要加上I F NOTEXISTS語句，這樣可以防止創建已經存在的元素時，二次創建的錯誤，比如創建一個臨時表時可以這樣創建：

CREATE TEMPORARY TABLE IF NOT EXISTS entity.temp_map_al (tn VARCHAR (129), al VARCHAR(12)) ENGINE = MYISAM;

對應的，刪除元素時則要加上IF EXISTS語句.如刪除表時加上IF EXISTS，這樣可以保證即使臨時表被刪除掉了，再次刪除不會報表不存在的錯誤.如：

DROP TABLE IF EXISTS entity.temp_map_al;1.8CONCAT&TRIM

在拼接字符串時，大家可能經常使用到CONCAT函數，但如果拼接的字符串中有一個為NULL值時，則拼接的整個字符串都為NULL值，所以應該對每個可能為空的值都加IF NULL.如

SET @ex_att_column = CONCAT ('''',IFNULL(direction_i,''),'''');

這條語句就是防止direction_i 為空時，導致@ex_att_column ＝NULL， 這樣就會導致使用@ex_att_column 作為拼接字符串時整個字符串為NULL 值.

這里提到拼接字符串用CONCAT函數，如果拼接的字符串過長時，很容易發生錯誤，那么在調試的時候一定要把拼接的字符串先輸出來看看，再去執行拼接的字符串.

經常有I F語句判定某個值是否為空，如果只是判斷IS NULL，這樣可能還不夠，因為有時候即使為空字符值也沒有意義，所以會同時要求判斷=’’，但如果是加了空格的字符串，這樣也是沒有意義的，所以必須用T R I M函數去掉空格，可參考下面的寫法，這樣包括得就十分全面了：IF etn IS NOT NULL AND TRIM(etn) 〈〉'' THEN

2 結論

本論文分別從八個方面介紹了如何去調試數據庫存儲過程.根據這八個方面基本上可以解決MySQL數據庫存儲過程開發過程中遇到的所有問題，這對沒有真正從事企業數據庫開發工作的人相當適用.在下一步研究工作中，將重點研究大數據下數據庫開發工作的調試方法，因此還需做更多的研究.

[1]沈黎.基于網絡數據庫的存儲過程和觸發器應用研究[J].西南師范大學學報,2016,41(3):51-55.

[2]趙旭輝.使用Mysql模擬列車運行完成車底數據庫統計任務初探[J].石家莊鐵路職業技術學院學報,2016,15(1):71-75.

責任編輯：張隆輝

TP 311.1

A

1672-2094（2017）04-0157-03

2017-04-11

鄧林（1985-），男，四川遂寧人，四川職業技術學院助教，碩士.研究方向：數據庫.