MySQL 存储过程中的错误处理
RutAYDE
8年前
<h2><strong>概述</strong></h2> <p>我们在执行普通的 MySQL SQL 语句的时候，都会在某些情况下遇到错误。比如，我们向一张表中插入一条已经存在的记录，导致了主键重复，会出现如下的错误：</p> <p style="text-align: center;"><img src="https://simg.open-open.com/show/aaccdf56ce90360433d5fdff8289ae61.png"></p> <p>上图中标记为红色的部分，就是 MySQL 返回的错误信息在 mysql 命令行客户端中的提示。里面有两个值得注意的部分：</p> <ol> <li> <p>MySQL 错误码：就是 ERROR 后面的 1062</p> </li> <li> <p>SQLSTATE 代码：就是 错误码后面的 (23000)</p> </li> </ol> <p>MySQL 的错误码和 SQLSTATE 的具体信息可参见官方手册： <a href="/misc/goto?guid=4959714705487062953" rel="nofollow,noindex">Server Error Codes and Messages</a></p> <p>今天我们要讲的是如果在存储过程中遇到错误应该如何处理。因为存储过程中是很多条 SQL 语句构成的 FUNCTION 或者 PROCEDURE ，发生的错误通常会影响代码的执行，因此妥善的处理错误是很重要的，比如继续或者退出执行当前代码块，并给出一个容易理解的错误信息。</p> <p>MySQL 提供了一个简单的手段，即定义错误处理器（Handler），来捕获从 通用的警告或者异常 ，到 更具体的错误码 等各种错误条件。</p> <h2><strong>声明一个错误处理器</strong></h2> <p>声明一个错误处理器所需的 DECLARE HANDLER 语句格式如下：</p> <pre> <code class="language-sql">DECLARE action HANDLER FOR condition_value statement;</code></pre> <p>如果一个错误条件的值符合 condition_value ，MySQL 就会执行对应的 statement ，并根据 action 指定关键字确定是 <strong>继续</strong> 还是 <strong>退出</strong> 当前的代码块（译者注，当前代码块就是包含此错误处理器的最近的那对 BEGIN 和 END 围出来的代码段）。</p> <p>参数 action 可以取以下两个值：</p> <ul> <li> <p>CONTINUE : 当前代码段会从出错的地方继续执行。</p> </li> <li> <p>EXIT : 当前代码段从出错的地方终止执行。</p> </li> </ul> <p>condition_value 指定了会激活错误处理器的一个特定的条件或者一类错误条件。其取值可以是：</p> <ul> <li> <p>一个 MySQL 错误码</p> </li> <li> <p>一个标准的 SQLSTATE 值。或者可以是 SQLWARNING , SQLEXCEPTION 等条件，这些分别代表一组类似的 SQLSTATE 值。 NOTFOUND 条件则可用于游标或者 SELECT INTO variable_list 语句，表示没有找到匹配的数据行。</p> </li> <li> <p>一个与特定 MySQL 错误代码或者 SQLSTATE 值关联的 <strong>命名条件</strong> ，说白了就是个别名。</p> </li> </ul> <p>statement 则可以是个简单的语句或者被 BEGIN 和 END 围起来的多条语句。</p> <h2><strong>MySQL 错误处理器示例</strong></h2> <p>我们先看几个声明错误处理器的例子：</p> <p>下面的处理器指出：如果发生了错误，就将 has_error 变量的值设为 1 并继续执行出错的语句所在的代码块。</p> <pre> <code class="language-sql">DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET has_error = 1;</code></pre> <p>下面是另一个错误处理器，指出如果发生任何错误就回滚之前的操作，给出一条错误信息，并退出当前代码块的执行。如果你是在声明存储过程的 BEGIN 和 END 语句之间声明的这个错误处理器，那么出错时会立即结束整个存储过程的执行。</p> <pre> <code class="language-sql">DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; SELECT 'An error has occurred, operation rollbacked and the stored procedure was terminated'; END;</code></pre> <p>下面的错误处理器指出，如果有发生数据行不存在的错误，其实就是指在使用游标（Cursor）或者 SELECT INTO 语句的情况，就把 no_row_found 变量设为1，并继续执行。</p> <pre> <code class="language-sql">DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_row_found = 1;</code></pre> <p>译者注：如果只是为了检查 SELECT INTO 是否有行数据返回，MySQL 还有另外一个 FOUND_ROWS() 内置函数可以用。</p> <p>下面的错误处理器指出，如果发生了主键重复的错误(MySQL的错误码为1062)，就将给出一条错误提示并继续代码块的执行：</p> <pre> <code class="language-sql">DECLARE CONTINUE HANDLER FOR 1062 SELECT 'Error, duplicate key occurred';</code></pre> <h2><strong>存储过程中出错处理的示例</strong></h2> <p>首先为了演示我们创建一张新表，表名是 article_tags ：</p> <pre> <code class="language-sql">CREATE TABLE article_tags( article_id INT, tag_id INT, PRIMARY KEY(article_id,tag_id) );</code></pre> <p>article_tags 表保存了 article 和 tag 之间的关系。每个 Article 可以对应到多个 Tag，反过来也是一样。为简单起见，我们就不创建 article 和 tag 表了，因此也就不用给 article_tags 加外键约束了。</p> <p>接下来，我们创建一个存储过程来把 Article 的 id 和 Tag 的 id 插入到 article_tags 表中，注意这个存储过程最后一条语句返回了表中的记录总数：</p> <pre> <code class="language-sql">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 $$</code></pre> <p>然后，通过下面的命令，我们给 Id=1 的 Article 新增 Id 为1，2，3 的 Tag:</p> <pre> <code class="language-sql">CALL insert_article_tags(1,1); CALL insert_article_tags(1,2); CALL insert_article_tags(1,3);</code></pre> <p>这之后，我们就要尝试插入一条重复的记录来看看错误处理器是否真的会被执行到。</p> <pre> <code class="language-sql">CALL insert_article_tags(1,3);</code></pre> <p style="text-align: center;"><img src="https://simg.open-open.com/show/1f280c51919e8777bcb80083abdaaf34.png"></p> <p>我们得到了一条错误信息。不过因为我们声明的是 CONTINUE 类型的错误处理器，存储过程会继续往后执行，所以结果我们也得到了 article 的 tag 的总数。</p> <p>如果我们把 CONTINUE 类型的错误处理器声明成 EXIT ，我们就只得到一个错误提示了。让我们再编写另外一个存储过程：</p> <pre> <code class="language-sql">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 $$</code></pre> <p>最后，我们可以尝试新增一条重复主键的记录看看效果：</p> <pre> <code class="language-sql">CALL insert_article_tags_2(1,3);</code></pre> <p style="text-align: center;"><img src="https://simg.open-open.com/show/b64c70de20764d511af371890d2eac6f.png"></p> <p>可以看到这次只输出了错误信息就没有继续向下执行了。</p> <h2><strong>错误处理器的优先级</strong></h2> <p>当有多个错误处理器都满足特定错误条件的时候，MySQL将按 <strong>更明确者优先</strong> 的原则决定优先级。</p> <p>MySQL中的每个错误都会映射到一个特定的错误码，因此错误码是最明确的。一个 SQLSTATE 可以对应到多个 MySQL 错误码，所以没那么明确。 SQLEXCEPTION 和 SQLWARNING 分别指代的是 SQLSTATES 中类型相近的一组值，所以它的明确性最低。</p> <p>基于错误处理器的优先级规则， MySQL 错误码处理器 ， SQLSTATE 错误处理器 和 SQLEXCEPTION 错误处理器顺序上分别排在1、2、3位。</p> <p>如果我们在 insert_article_tags_3 存储过程里声明3个错误处理器，像下面一样：</p> <pre> <code class="language-sql">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 $$</code></pre> <p>当我们通过下面的命令，试图调用上面的存储过程插入一条主键重复的记录到 article_tags 表时：</p> <pre> <code class="language-sql">CALL insert_article_tags_3(1,3);</code></pre> <p style="text-align: center;"><img src="https://simg.open-open.com/show/e9a80412c389cc64500db15f9d0baa2e.png"> 你会看到， MySQL错误码绑定的那个处理器被调用了。</p> <h2><strong>使用命名错误条件</strong></h2> <p>我们先看一个错误处理器的声明：</p> <pre> <code class="language-sql">DECLARE EXIT HANDLER FOR 1051 SELECT 'Please create table abc first'; SELECT * FROM abc;</code></pre> <p>1051 这个错误码到底什么意思？想象一下如果你有很多的存储过程，里面散布者这种数字，对于代码维护来说应该就是噩梦了。</p> <p>幸运的是，MySQL 为我们提供了一个 DECLARE CONDITION 语句来声明一个命名错误条件，可以将上面的数字关联为一个有意义的名字。</p> <p>DECLARE CONDITION 语句的语法如下所示：</p> <pre> <code class="language-sql">DECLARE condition_name CONDITION FOR condition_value;</code></pre> <p>condition_value 可以是一个类似 1051 的 MySQL 错误码，或者一个 SQLSTATE 值，然后 condition_name 就可以代表 condition_value 来使用了。</p> <p>所以之前的代码我们就可以改写成下面的样子：</p> <pre> <code class="language-sql">DECLARE table_not_found CONDITION for 1051; DECLARE EXIT HANDLER FOR table_not_found SELECT 'Please create table abc first'; SELECT * FROM abc;</code></pre> <p>这样代码的可读性比之前明显好多了。需要注意的是，要在存储过程声明一个命名条件的语句，则该语句必须出现在错误处理器或者游标声明的前面。</p> <h2><strong>一个在 handler 中实用的辅助函数</strong></h2> <p>实际应用中，存储过程中的错误被我们的错误处理器捕获了之后，你如果还想用类似 mysql 命令行那样的格式返回对应的错误，可以声明一个这样的函数：</p> <pre> <code class="language-sql">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$$</code></pre> <p>那么在实际使用时，就可以在错误处理器中这样用：</p> <pre> <code class="language-sql">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;</code></pre> <p>那么在有错误发生的时候，就可以得到如下的错误提示了：</p> <p style="text-align: center;"><img src="https://simg.open-open.com/show/bc5fd186032b91a0477a0cfe52e7d593.png"></p> <p> </p> <p>来自：https://segmentfault.com/a/1190000006834132</p> <p> </p>