本文主要基于一篇MySQL教程文章，同时补充了自己的一些实践。概述当我们执行普通的MySQLSQL语句时，在某些情况下会遇到错误。例如，如果我们向表中插入一条已有的记录，导致主键重复，就会出现如下错误：上图中红色标记的部分是MySQL在mysql命令行客户端返回错误信息的提示.其中有两个值得注意的地方：MySQL错误码：ERROR后面是1062，是MySQL自定义的错误码，其他数据库不通用。SQLSTATE代码：在错误代码之后是(23000)。来自ANSISQL和ODBC的五字符标准化错误代码与错误代码没有一对一的对应关系。MySQL错误码和SQLSTATE具体信息可以参考官方手册：ServerErrorCodesandMessages今天我们就来说说存储过程中遇到的错误如何处理。由于存储过程是由很多SQL语句组成的FUNCTION或PROCEDURE，错误通常会影响代码的执行，所以正确处理错误非常重要，比如继续或退出当前代码块，给出一个易于理解的语句理解错误信息。MySQL提供了一种简单的方法来定义错误处理程序(Handler)，以捕获从一般警告或异常到更具体的错误代码的各种错误情况。声明错误处理程序声明错误处理程序所需的DECLAREHANDLER语句格式如下：DECLAREactionHANDLERFORcondition_valuestatement;如果一个错误条件的值满足condition_value，MySQL将执行相应的语句，并判断是继续还是退出当前（译者注，当前代码块是最近的一对BEGIN和END封闭的包含这个错误处理程序的代码段).参数action可以取以下两个值：CONTINUE：当前代码段将从错误发生的地方继续执行。EXIT：当前代码段的执行在错误点终止。condition_value指定将激活错误处理程序的特定条件或错误条件类别。它的值可以是：一个MySQL错误代码一个标准的SQLSTATE值。也可以是SQLWARNING、SQLEXCEPTION等条件，分别代表一组类似的SQLSTATE值。NOTFOUND条件可以用在游标或SELECTINTOvariable_list语句中，表示没有找到匹配的数据行。命名条件，字面意思是别名，与特定的MySQL错误代码或SQLSTATE值相关联。statement可以是一个简单的语句，也可以是被BEGIN和END包围的多条语句。MySQLerrorhandlerexample我们来看几个声明errorhandler的例子：下面的handler表示如果发生错误，将has_error变量的值设置为1，并继续执行错误语句所在的代码块。DECLARECONTINUEHANDLERFORSQLEXCEPTIONSEThas_error=1;下面是另一个错误处理程序，如果发生任何错误，它会回滚之前的操作，给出错误消息，并退出当前代码块的执行。如果在存储过程的BEGIN和END语句之间声明此错误处理程序，当发生错误时，整个存储过程的执行将立即结束。DECLAREEXITHANDLERFORSQLEXCEPTIONBEGINROLLBACK;SELECT'Anerrorhasoccurred,operationrollbackedandthestoredprocedurewasterminated';END;下面的错误处理程序指出，如果出现数据行不存在的错误，其实说明使用了游标（Cursor）或SELECTINTO语句，设置no_row_found变量为1，继续执行。DECLARECONTINUEHANDLERFORNOTFOUNDSETno_row_found=1;译者注：如果只是检查SELECTINTO是否有返回行数据，MySQL还有另外一个内建函数FOUND_ROWS()可以使用。下面的错误处理程序指出，如果发生主键重复错误（MySQL错误代码为1062），将给出错误信息并继续执行代码块：DECLARECONTINUEHANDLERFOR1062SELECT'Error,duplicatekeyoccurred';存储过程中的错误处理示例首先，我们新建一个表进行演示，表名为article_tags：CREATETABLEarticle_tags(article_idINT,tag_idINT,PRIMARYKEY(article_id,tag_id));article_tags表保存了文章和标签的关系。每个文章可以对应多个标签，反之亦然。为了简单起见，我们不创建article和tag表，所以我们不需要为article_tags添加外键约束。接下来，我们创建一个存储过程，将Article的id和Tag的id插入到article_tags表中。请注意，此存储过程的最后一条语句返回表中记录的总数：DELIMITER$$CREATEPROCEDUREinsert_article_tags(INarticle_idINT,INtag_idINT)BEGINDECLARECONTINUEHANDLERFOR1062SELECTCONCAT('duplicatekeys(',article_id,',',tag_id,')found')ASmsg;--insertanewrecordintoarticle_tagsINSERTINTOarticle_tags(article_id,tag_id)VALUES(article_id,tag_id);--returntagcountforthearticleSELECTCOUNT(*)FROMarticle_$tags;在以下命令中，我们将ID为1、2和3的标签添加到ID=1的文章中：CALLinsert_article_tags(1,1);CALLinsert_article_tags(1,2);CALLinsert_article_tags(1,3);之后，我们将尝试插入一条重复记录以查看错误处理程序是否真的被执行。CALLinsert_article_tags(1,3);我们收到一条错误消息。但是因为我们声明了一个CONTINUE类型的错误处理器，存储过程后面还会继续执行，所以我们也得到了文章标签的总数。如果我们将CONTINUE类型的错误处理程序声明为EXIT，我们只会得到一条错误消息。让我们再编写另外一个存储过程：DELIMITER$$CREATEPROCEDUREinsert_article_tags_2(INarticle_idINT,INtag_idINT)BEGINDECLAREEXITHANDLERFORSQLEXCEPTIONSELECT'SQLExceptioninvoked';DECLAREEXITHANDLERFOR1062SELECT'MySQLerrorcode1062invoked';DECLAREEXITHANDLERFORSQLSTATE'23000'SELECT'SQLSTATE23000invoked';--insertanewrecordintoarticle_tagsINSERTINTOarticle_tags(article_id,tag_id)VALUES(article_id,tag_id);--returntagcountforthearticleSELECTCOUNT(*)FROMarticle_tags;END$$***，我们可以尝试添加一条主键重复的记录看看效果：CALLinsert_article_tags_2(1,3);可以看到这次只输出了错误信息，并没有继续往下执行。errorhandler的优先级当有多个errorhandler满足特定的错误条件时，MySQL会按照优先级越明确的原则来决定优先级。MySQL中的每个错误都映射到特定的错误代码，因此错误代码是最具体的。一个SQLSTATE可以对应多个MySQL错误码，所以不是很清楚。SQLEXCEPTION和SQLWARNING分别引用了SQLSTATES中一组相似类型的值，所以其清晰度是有保证的。根据错误处理程序的优先级规则，MySQL错误代码处理程序、SQLSTATE错误处理程序和SQLEXCEPTION错误处理程序分别排名1、2和3。如果我们在insert_article_tags_3存储过程里声明3个错误处理器，像下面一样：DELIMITER$$CREATEPROCEDUREinsert_article_tags_3(INarticle_idINT,INtag_idINT)BEGINDECLAREEXITHANDLERFOR1062SELECT'Duplicatekeyserrorencountered';DECLAREEXITHANDLERFORSQLEXCEPTIONSELECT'SQLExceptionencountered';DECLAREEXITHANDLERFORSQLSTATE'23000'SELECT'SQLSTATE23000';--insertanewrecordintoarticle_tagsINSERTINTOarticle_tags(article_id,tag_id)VALUES(article_id,tag_id);--returntagcountforthearticleSELECTCOUNT(*)FROMarticle_tags;END$$当我们尝试通过以下命令调用上述存储过程向article_tags表中插入主键重复记录时：CALLinsert_article_tags_3(1,3);您将看到绑定到MySQL错误代码的处理程序被调用。使用命名的错误条件，让我们首先看一下错误处理程序的声明：DECLAREEXITHANDLERFOR1051SELECT'Pleasecreatetableabcfirst';SELECT*FROMabc;1051这个错误代码是什么意思？试想一下，如果你有很多存储过程，其中散落着这样的数字，对于代码维护来说应该是一场噩梦。幸运的是，MySQL为我们提供了一个DECLARECONDITION语句来声明一个命名的错误条件，它可以将上述数字与一个有意义的名称相关联。DECLARECONDITION语句的语法如下：DECLAREcondition_nameCONDITIONFORcondition_value;condition_value可以是1051这样的MySQL错误码，也可以是SQLSTATE值，然后用condition_name代替condition_value。所以我们可以将之前的代码改写如下：DECLAREtable_not_foundCONDITIONfor1051;DECLAREEXITHANDLERFORTABLE_not_foundSELECT'Pleasecreatetableabcfirst';SELECT*FROMabc;这段代码的可读性比之前好很多。需要注意的是，要在存储过程中声明命名条件语句，该语句必须出现在错误处理程序或游标语句之前。handler中一个实用的辅助函数在实际应用中，存储过程中的错误被我们的errorhandler捕捉到后，如果想以类似mysql命令行的格式返回相应的错误，可以声明这样一个函数：DELIMITER$$CREATEFUNCTIONfn_get_error()RETURNSVARCHAR(250)BEGINDECLAREcodeCHAR(5)DEFAULT'00000';DECLAREmsgTEXT;DECLAREerrnoINT;GETDIAGNOSTICSCONDITION1code=RETURNED_SQLSTATE,errno=MYSQL_ERRNO,msg=MESSAGE_TEXT;RETUR",rCAT("CON",ORNCOALESCE(code,""):",msg),'-');END$$那么在实际使用中，可以在errorhandler中使用：DECLAREEXITHANDLERFORSQLEXCEPTIONBEGINROLLBACK;SETret=-9;SELECTretAS'ret',fn_get_error()AS'err';END;DECLAREEXITHANDLERFOR1062BEGINROLLBACK;SETret=-1;SELECTretAS'ret',fn_get_error()AS'err';END;那么当出现错误时，可以得到如下错误信息：
