MySQL存储过程调试技巧

2017-09-29邓林

四川职业技术学院学报 2017年4期

关键词：字符串日志语句

邓林

(四川职业技术学院计算机科学系，四川遂宁 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