鄧 林
(四川職業(yè)技術(shù)學(xué)院計算機科學(xué)系,四川 遂寧 629000)
MySQL存儲過程調(diào)試技巧
鄧 林
(四川職業(yè)技術(shù)學(xué)院計算機科學(xué)系,四川 遂寧 629000)
由于高校對存儲過程方面的研究還比較少,大部分有這方面工作經(jīng)驗的人員也在企業(yè)之中.所以本文歸納出MySQL存儲過程方面相對較全面的調(diào)試技巧.實踐結(jié)果表明,本文所研究實現(xiàn)的存儲過程調(diào)試技巧將大大提高開發(fā)人員的工作效率,同時也間接提高了存儲過程的運行性能.
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è)選擇免費并且性能全面的開源數(shù)據(jù)庫.存儲過程作為每個數(shù)據(jù)庫差異較大的一個知識點,對其的研究也就勢在必行,那么其具體的調(diào)試技巧也很明顯有所差異,所以需要針對具體數(shù)據(jù)庫來講解其調(diào)試技巧[1][2].
結(jié)合之前的MySQL數(shù)據(jù)庫開發(fā)工作,在寫調(diào)試技巧之前,有些代碼風(fēng)格在這里先提一下,其實這些風(fēng)格也對其調(diào)試有莫大的幫助.第一,所有關(guān)鍵字都最好大寫,這對于閱讀代碼的人更加直觀;第二,在創(chuàng)建存儲過程時學(xué)會使用DEFINER,這可以提高你代碼的權(quán)限控制,尤其是現(xiàn)在這個權(quán)限控制要求較高的行業(yè)來說;第三,定義變量時,保持固定風(fēng)格,比如輸入變量都加上_i,輸出變量都加_o,其它臨時變量都以v_開頭,這些風(fēng)格在代碼較多時,可以更加容易閱讀代碼.其實寫到這里,可以發(fā)現(xiàn),調(diào)試技巧與代碼風(fēng)格是分不開的,有些風(fēng)格也是促進提高你的調(diào)試技巧的.下面就從各方面來分析存儲過程的調(diào)試技巧.
我們先來看看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語句之間的定義,則沒有先后順序,比如定義變量或者定義錯誤處理機制就沒有先后順序.
DECLARE ...HANDLER主要是存儲過程的異常處理機制,這對程序的健壯性起了很大的作用,而且利用好這個機制對調(diào)試代碼也相當(dāng)有幫助.這個容錯機制是在程序執(zhí)行過程中發(fā)生一些條件來觸發(fā)的,處理的方式可以結(jié)束程序,也可以忽略這些錯誤繼續(xù)執(zhí)行程序.觸發(fā)這個機制主要有三類: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é)束程序,因為這里使用的是EXIT.
第二行,顯示代碼中遇到錯誤的警告,正常情況下,存儲過程遇到警告是會中止代碼的,這點跟其它高級語言不一樣.這里會顯示警告的詳細信息,這樣也方便你調(diào)試程序,如果沒有這一行,那么在程序遇到警告時,它不會顯示警告信息,因為這里已經(jīng)異常處理了.那么是不是永遠加上這一句呢,那我們試想一下,高級語言調(diào)用存儲過程時,出了錯,那么SHOW WARNING就會起作用,會把警告信息返回給高級語言,也即會返回一大串英文單詞,這是高級語言想要的東西嗎,肯定不是,所以SHOW WARNING在調(diào)試的時候可以很方便的使用,但在你的存儲過程交付后就應(yīng)該注釋它,如果后面發(fā)現(xiàn)錯誤需要調(diào)試時,再把注釋去掉進行調(diào)試.
第三、四行是設(shè)置輸出函數(shù)的代碼以及返回的簡單錯誤信息.這個錯誤信息是開發(fā)人員自己定義的,即開發(fā)人員想返回給高級語言的錯誤信息,它比數(shù)據(jù)庫的錯誤信息更加簡潔明了.
第五行調(diào)用日志存儲過程,關(guān)于日志存儲過程后面再述說.
總之,DECLARE…HANDLER模塊可以在程序遇到錯誤即將退出存儲過程之前必須執(zhí)行的一段程序,把遇到的錯誤信息返回給調(diào)用它的代碼,讓接受到錯誤信息的代碼知道從哪個方面入手去解決這個錯誤,大大提高了程序的容錯處理能力.
日志存儲過程應(yīng)該在每個數(shù)據(jù)庫中存在一份,它的主要功能是把調(diào)用存儲過程的相關(guān)信息插入日志表中.包括調(diào)用存儲過程的用戶、輸入?yún)?shù)的實際值等信息以字符串的信息插入日志表中.在遇到錯誤信息時,開發(fā)人員如果不能根據(jù)返回的信息推斷出程序出錯點,則需要根據(jù)日志表中的輸入數(shù)據(jù)去調(diào)用存儲過程,因為這些數(shù)據(jù)是實際傳入的數(shù)據(jù),開發(fā)人員根據(jù)這些數(shù)據(jù)去調(diào)試存儲過程,當(dāng)然就能更迅速地定位到錯誤點.通常日志表可以按下表來設(shè)計:
?
在存儲過程中經(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),因為開發(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
眾所周知,存儲過程因為其特性,沒有支持像高級語言那樣的單步調(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)試時,一定要將子存儲過程返回值查詢出來看看,其值是否是正確的.
不管是存儲過程還是函數(shù),能傳遞的值都有限,那如果需要傳遞多條記錄的數(shù)據(jù)時,應(yīng)該怎么做呢.或者當(dāng)子存儲過程執(zhí)行的結(jié)果是多行數(shù)據(jù)的結(jié)果集,主存儲過程如何才能使用這個結(jié)果集呢.這個時候就是臨時表派上用場了,在整個存儲過程運行期間,臨時表都會一直存在的,所以可以將子存儲過程的結(jié)果存儲臨時表,然后在主存儲過程中將值取出來參與運算.那么在這其中我們需要注意以下幾點:
第一,臨時表的字段類型必須和最終結(jié)果表一致,包括類型長度也要一致,這是防止臨時表中的字段類型長度大于結(jié)果表的長度,如果插入的數(shù)據(jù)的長度剛好等于臨時表中的長度但卻大于結(jié)果表的長度,這樣的話,插入數(shù)據(jù)到結(jié)果表時很容易就出錯了;如果臨時表的長度小于結(jié)果表的長度,這樣雖然插入時不會出錯,但很有可能隱藏一些潛在的錯誤,這在筆者之前的工作中,因為沒有完全保持一致,就出現(xiàn)過一些錯誤,而且這些錯誤也是很不容易發(fā)現(xiàn)的.
第二,在臨時表使用完畢后,一定要記得刪除臨時表,因為不刪除,程序如果頻繁啟動時,很容易出現(xiàn)第二次啟動了,原來的臨時表還存在.這個刪除不僅僅是程序正常結(jié)束時要刪除,而且程序異常結(jié)束時也一定要刪除,所以在DECLARE…HANDLER模塊里面一定也要把臨時表刪除掉.
第三,創(chuàng)建臨時表時,選擇的引擎最好選用Myisam,因為臨時表在存儲過程中被使用最多是存儲臨時數(shù)據(jù),那么主要就是插入數(shù)據(jù),關(guān)于這一點,Myisam相較于Innodb更有優(yōu)勢.
在存儲過程中,創(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,這樣可能還不夠,因為有時候即使為空字符值也沒有意義,所以會同時要求判斷=’’,但如果是加了空格的字符串,這樣也是沒有意義的,所以必須用T R I M函數(shù)去掉空格,可參考下面的寫法,這樣包括得就十分全面了:IF etn IS NOT NULL AND TRIM(etn) 〈〉'' THEN
本論文分別從八個方面介紹了如何去調(diào)試數(shù)據(jù)庫存儲過程.根據(jù)這八個方面基本上可以解決MySQL數(shù)據(jù)庫存儲過程開發(fā)過程中遇到的所有問題,這對沒有真正從事企業(yè)數(shù)據(jù)庫開發(fā)工作的人相當(dāng)適用.在下一步研究工作中,將重點研究大數(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模擬列車運行完成車底數(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ù)庫.