MySQL存储过程中的错误处理

MySQL存储过程中的错误处理

本文主要基于一篇 MySQL Tutorial的文章，同时补充了自己的几个实践。

概述

我们在执行普通的 MySQL SQL 语句的时候，都会在某些情况下遇到错误。比如，我们向一张表中插入一条已经存在的记录，导致了主键重复，会出现如下的错误：

上图中标记为红色的部分，就是 MySQL 返回的错误信息在 mysql 命令行客户端中的提示。里面有两个值得注意的部分：

MySQL 错误码：就是 ERROR 后面的 1062, MySQL 自定义的错误代码，跟其他数据库不通用。SQLSTATE 代码：就是 错误码后面的 (23000)。五位字符，从 ANSI SQL和 ODBC 来的标准化的错误代码，跟错误码之间并没有一一对应的关系。

MySQL 的错误码和 SQLSTATE 的具体信息可参见官方手册：Server Error Codes and Messages

今天我们要讲的是如果在存储过程中遇到错误应该如何处理。因为存储过程中是很多条 SQL 语句构成的 FUNCTION 或者PROCEDURE，发生的错误通常会影响代码的执行，因此妥善的处理错误是很重要的，比如继续或者退出执行当前代码块，并给出一个容易理解的错误信息。

MySQL 提供了一个简单的手段，即定义错误处理器(Handler)，来捕获从通用的警告或者异常，到更具体的错误码等各种错误条件。

DECLARE action HANDLER FOR condition_value statement;

如果一个错误条件的值符合 condition_value，MySQL 就会执行对应的 statement，并根据 action 指定关键字确定是 继续 还是退出 当前的代码块(译者注，当前代码块就是包含此错误处理器的最近的那对 BEGIN 和 END围出来的代码段)。

参数 action 可以取以下两个值：

CONTINUE : 当前代码段会从出错的地方继续执行。EXIT : 当前代码段从出错的地方终止执行。

condition_value 指定了会激活错误处理器的一个特定的条件或者一类错误条件。其取值可以是：

一个 MySQL 错误码一个标准的 SQLSTATE 值。或者可以是 SQLWARNING , SQLEXCEPTION 等条件，这些分别代表一组类似的 SQLSTATE值。NOTFOUND 条件则可用于游标或者 SELECT INTO variable_list 语句，表示没有找到匹配的数据行。一个与特定 MySQL 错误代码或者 SQLSTATE 值关联的命名条件，说白了就是个别名。

statement 则可以是个简单的语句或者被 BEGIN 和 END 围起来的多条语句。

MySQL 错误处理器示例

下面的处理器指出：如果发生了错误，就将 has_error 变量的值设为 1 并继续执行出错的语句所在的代码块。

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET has_error = 1;

DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; SELECT 'An error has occurred, operation rollbacked and the stored procedure was terminated'; END;

下面的错误处理器指出，如果有发生数据行不存在的错误，其实就是指在使用游标(Cursor)或者SELECT INTO语句的情况，就把no_row_found 变量设为1，并继续执行。

DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_row_found = 1;

译者注：如果只是为了检查 SELECT INTO 是否有行数据返回，MySQL 还有另外一个 FOUND_ROWS() 内置函数可以用。

下面的错误处理器指出，如果发生了主键重复的错误(MySQL的错误码为1062)，就将给出一条错误提示并继续代码块的执行：

DECLARE CONTINUE HANDLER FOR 1062 SELECT 'Error, duplicate key occurred';

存储过程中出错处理的示例

首先为了演示我们创建一张新表，表名是 article_tags：

CREATE TABLE article_tags(     article_id INT,     tag_id     INT,     PRIMARY KEY(article_id,tag_id) );

article_tags 表保存了 article 和 tag 之间的关系。每个 Article 可以对应到多个 Tag，反过来也是一样。为简单起见，我们就不创建 article 和 tag 表了，因此也就不用给 article_tags 加外键约束了。

接下来，我们创建一个存储过程来把 Article 的 id 和 Tag 的 id 插入到 article_tags 表中，注意这个存储过程***一条语句返回了表中的记录总数：

DELIMITER $$  CREATE PROCEDURE insert_article_tags(IN article_id INT, IN tag_id INT) BEGIN      DECLARE CONTINUE HANDLER FOR 1062     SELECT CONCAT('duplicate keys (',article_id,',',tag_id,') found') AS msg;      -- insert a new record into article_tags     INSERT INTO article_tags(article_id,tag_id)     VALUES(article_id,tag_id);      -- return tag count for the article     SELECT COUNT(*) FROM article_tags; END $$

然后，通过下面的命令，我们给 Id=1 的 Article 新增 Id 为1，2，3 的 Tag:

CALL insert_article_tags(1,1); CALL insert_article_tags(1,2); CALL insert_article_tags(1,3);

这之后，我们就要尝试插入一条重复的记录来看看错误处理器是否真的会被执行到。

CALL insert_article_tags(1,3);

DELIMITER $$  CREATE PROCEDURE insert_article_tags_2(IN article_id INT, IN tag_id INT) BEGIN      DECLARE EXIT HANDLER FOR SQLEXCEPTION      SELECT 'SQLException invoked';      DECLARE EXIT HANDLER FOR 1062          SELECT 'MySQL error code 1062 invoked';      DECLARE EXIT HANDLER FOR SQLSTATE '23000'     SELECT 'SQLSTATE 23000 invoked';      -- insert a new record into article_tags     INSERT INTO article_tags(article_id,tag_id)        VALUES(article_id,tag_id);      -- return tag count for the article     SELECT COUNT(*) FROM article_tags; END $$

***，我们可以尝试新增一条重复主键的记录看看效果：

CALL insert_article_tags_2(1,3);

可以看到这次只输出了错误信息就没有继续向下执行了。

错误处理器的优先级

当有多个错误处理器都满足特定错误条件的时候，MySQL将按更明确者优先的原则决定优先级。

MySQL中的每个错误都会映射到一个特定的错误码，因此错误码是最明确的。一个 SQLSTATE 可以对应到多个 MySQL 错误码，所以没那么明确。SQLEXCEPTION 和 SQLWARNING 分别指代的是 SQLSTATES 中类型相近的一组值，所以它的明确性***。

基于错误处理器的优先级规则，MySQL 错误码处理器，SQLSTATE 错误处理器 和 SQLEXCEPTION错误处理器顺序上分别排在1、2、3位。

DELIMITER $$  CREATE PROCEDURE insert_article_tags_3(IN article_id INT, IN tag_id INT) BEGIN      DECLARE EXIT HANDLER FOR 1062 SELECT 'Duplicate keys error encountered';     DECLARE EXIT HANDLER FOR SQLEXCEPTION SELECT 'SQLException encountered';     DECLARE EXIT HANDLER FOR SQLSTATE '23000' SELECT 'SQLSTATE 23000';      -- insert a new record into article_tags     INSERT INTO article_tags(article_id,tag_id)     VALUES(article_id,tag_id);      -- return tag count for the article     SELECT COUNT(*) FROM article_tags; END $$

当我们通过下面的命令，试图调用上面的存储过程插入一条主键重复的记录到article_tags表时：

CALL insert_article_tags_3(1,3);

你会看到， MySQL错误码绑定的那个处理器被调用了。

使用命名错误条件

DECLARE EXIT HANDLER FOR 1051 SELECT 'Please create table abc first'; SELECT * FROM abc;

1051 这个错误码到底什么意思?想象一下如果你有很多的存储过程，里面散布者这种数字，对于代码维护来说应该就是噩梦了。

DECLARE CONDITION 语句的语法如下所示：

DECLARE condition_name CONDITION FOR condition_value;

condition_value 可以是一个类似 1051 的 MySQL 错误码，或者一个 SQLSTATE 值，然后 condition_name 就可以代表condition_value 来使用了。

所以之前的代码我们就可以改写成下面的样子：

DECLARE table_not_found CONDITION for 1051; DECLARE EXIT HANDLER FOR  table_not_found SELECT 'Please create table abc first'; SELECT * FROM abc;

一个在 handler 中实用的辅助函数

DELIMITER $$ CREATE FUNCTION fn_get_error() RETURNS VARCHAR(250) BEGIN     DECLARE code CHAR(5) DEFAULT '00000';     DECLARE msg TEXT;     DECLARE errno INT;          GET DIAGNOSTICS CONDITION 1 code = RETURNED_SQLSTATE,          errno = MYSQL_ERRNO, msg = MESSAGE_TEXT;          RETURN COALESCE(CONCAT("ERROR ", errno, " (", code, "): ", msg), '-'); END$$

那么在实际使用时，就可以在错误处理器中这样用：

DECLARE EXIT HANDLER FOR SQLEXCEPTION       BEGIN           ROLLBACK;           SET ret = -9;           SELECT ret AS 'ret', fn_get_error() AS 'err';      END;                  DECLARE EXIT HANDLER FOR 1062     BEGIN         ROLLBACK;           SET ret = -1;           SELECT ret AS 'ret', fn_get_error() AS 'err';     END;

那么在有错误发生的时候，就可以得到如下的错误提示了：

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。