MySQL存儲過程調(diào)試技巧

鄧 林

(四川職業(yè)技術(shù)學(xué)院計算機(jī)科學(xué)系，四川 遂寧 629000)

MySQL存儲過程調(diào)試技巧

鄧 林

(四川職業(yè)技術(shù)學(xué)院計算機(jī)科學(xué)系，四川 遂寧 629000)

由于高校對存儲過程方面的研究還比較少，大部分有這方面工作經(jīng)驗(yàn)的人員也在企業(yè)之中.所以本文歸納出MySQL存儲過程方面相對較全面的調(diào)試技巧.實(shí)踐結(jié)果表明，本文所研究實(shí)現(xiàn)的存儲過程調(diào)試技巧將大大提高開發(fā)人員的工作效率，同時也間接提高了存儲過程的運(yùn)行性能.

MySQL；存儲過程；調(diào)試；技巧；效率

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

1 MySQL調(diào)試技巧

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

1.1 DECLARE申明

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

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

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

1.2 DECLARE ...HANDLER

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

①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兩類錯誤時，則執(zhí)行BEGIN…END里面的代碼，執(zhí)行完后結(jié)束程序，因?yàn)檫@里使用的是EXIT.

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

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

第五行調(diào)用日志存儲過程，關(guān)于日志存儲過程后面再述說.

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

1.3 日志存儲過程

日志存儲過程應(yīng)該在每個數(shù)據(jù)庫中存在一份，它的主要功能是把調(diào)用存儲過程的相關(guān)信息插入日志表中.包括調(diào)用存儲過程的用戶、輸入?yún)?shù)的實(shí)際值等信息以字符串的信息插入日志表中.在遇到錯誤信息時，開發(fā)人員如果不能根據(jù)返回的信息推斷出程序出錯點(diǎn)，則需要根據(jù)日志表中的輸入數(shù)據(jù)去調(diào)用存儲過程，因?yàn)檫@些數(shù)據(jù)是實(shí)際傳入的數(shù)據(jù)，開發(fā)人員根據(jù)這些數(shù)據(jù)去調(diào)試存儲過程，當(dāng)然就能更迅速地定位到錯誤點(diǎn).通常日志表可以按下表來設(shè)計：

?

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

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

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

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

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

1.5 單步調(diào)試

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

1.6 臨時表

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

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

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

第三，創(chuàng)建臨時表時，選擇的引擎最好選用Myisam，因?yàn)榕R時表在存儲過程中被使用最多是存儲臨時數(shù)據(jù)，那么主要就是插入數(shù)據(jù)，關(guān)于這一點(diǎn)，Myisam相較于Innodb更有優(yōu)勢.

1.7 IF EXISTS

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

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

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

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

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

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

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

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

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

2 結(jié)論

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

[1]沈黎.基于網(wǎng)絡(luò)數(shù)據(jù)庫的存儲過程和觸發(fā)器應(yīng)用研究[J].西南師范大學(xué)學(xué)報,2016,41(3):51-55.

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

責(zé)任編輯：張隆輝

TP 311.1

A

1672-2094（2017）04-0157-03

2017-04-11

鄧林（1985-），男，四川遂寧人，四川職業(yè)技術(shù)學(xué)院助教，碩士.研究方向：數(shù)據(jù)庫.