本文主要基于一篇 MySQL Tutorial的文章，同時(shí)補(bǔ)充了自己的幾個(gè)實(shí)踐。

概述

我們?cè)趫?zhí)行普通的 MySQL SQL 語(yǔ)句的時(shí)候，都會(huì)在某些情況下遇到錯(cuò)誤。比如，我們向一張表中插入一條已經(jīng)存在的記錄，導(dǎo)致了主鍵重復(fù)，會(huì)出現(xiàn)如下的錯(cuò)誤：

 上圖中標(biāo)記為紅色的部分，就是 MySQL 返回的錯(cuò)誤信息在 mysql 命令行客戶端中的提示。里面有兩個(gè)值得注意的部分：

1. MySQL 錯(cuò)誤碼：就是 ERROR 后面的 1062, MySQL 自定義的錯(cuò)誤代碼，跟其他數(shù)據(jù)庫(kù)不通用。
2. SQLSTATE 代碼：就是 錯(cuò)誤碼后面的 (23000)。五位字符，從 ANSI SQL和 ODBC 來(lái)的標(biāo)準(zhǔn)化的錯(cuò)誤代碼，跟錯(cuò)誤碼之間并沒(méi)有一一對(duì)應(yīng)的關(guān)系。

MySQL 的錯(cuò)誤碼和 SQLSTATE 的具體信息可參見(jiàn)官方手冊(cè)：Server Error Codes and Messages

今天我們要講的是如果在存儲(chǔ)過(guò)程中遇到錯(cuò)誤應(yīng)該如何處理。因?yàn)榇鎯?chǔ)過(guò)程中是很多條 SQL 語(yǔ)句構(gòu)成的 FUNCTION 或者PROCEDURE，發(fā)生的錯(cuò)誤通常會(huì)影響代碼的執(zhí)行，因此妥善的處理錯(cuò)誤是很重要的，比如繼續(xù)或者退出執(zhí)行當(dāng)前代碼塊，并給出一個(gè)容易理解的錯(cuò)誤信息。

MySQL 提供了一個(gè)簡(jiǎn)單的手段，即定義錯(cuò)誤處理器(Handler)，來(lái)捕獲從通用的警告或者異常，到更具體的錯(cuò)誤碼等各種錯(cuò)誤條件。

聲明一個(gè)錯(cuò)誤處理器

聲明一個(gè)錯(cuò)誤處理器所需的 DECLARE HANDLER 語(yǔ)句格式如下：

DECLARE action HANDLER FOR condition_value statement; 

如果一個(gè)錯(cuò)誤條件的值符合 condition_value，MySQL 就會(huì)執(zhí)行對(duì)應(yīng)的 statement，并根據(jù) action 指定關(guān)鍵字確定是 繼續(xù) 還是退出 當(dāng)前的代碼塊(譯者注，當(dāng)前代碼塊就是包含此錯(cuò)誤處理器的最近的那對(duì) BEGIN 和 END圍出來(lái)的代碼段)。

參數(shù) action 可以取以下兩個(gè)值：

• CONTINUE : 當(dāng)前代碼段會(huì)從出錯(cuò)的地方繼續(xù)執(zhí)行。
• EXIT : 當(dāng)前代碼段從出錯(cuò)的地方終止執(zhí)行。

condition_value 指定了會(huì)激活錯(cuò)誤處理器的一個(gè)特定的條件或者一類錯(cuò)誤條件。其取值可以是：

• 一個(gè) MySQL 錯(cuò)誤碼
• 一個(gè)標(biāo)準(zhǔn)的 SQLSTATE 值?；蛘呖梢允?SQLWARNING , SQLEXCEPTION 等條件，這些分別代表一組類似的 SQLSTATE值。NOTFOUND 條件則可用于游標(biāo)或者 SELECT INTO variable_list 語(yǔ)句，表示沒(méi)有找到匹配的數(shù)據(jù)行。
• 一個(gè)與特定 MySQL 錯(cuò)誤代碼或者 SQLSTATE 值關(guān)聯(lián)的命名條件，說(shuō)白了就是個(gè)別名。

statement 則可以是個(gè)簡(jiǎn)單的語(yǔ)句或者被 BEGIN 和 END 圍起來(lái)的多條語(yǔ)句。

MySQL 錯(cuò)誤處理器示例

我們先看幾個(gè)聲明錯(cuò)誤處理器的例子：

下面的處理器指出：如果發(fā)生了錯(cuò)誤，就將 has_error 變量的值設(shè)為 1 并繼續(xù)執(zhí)行出錯(cuò)的語(yǔ)句所在的代碼塊。

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET has_error = 1; 

下面是另一個(gè)錯(cuò)誤處理器，指出如果發(fā)生任何錯(cuò)誤就回滾之前的操作，給出一條錯(cuò)誤信息，并退出當(dāng)前代碼塊的執(zhí)行。如果你是在聲明存儲(chǔ)過(guò)程的 BEGIN 和 END 語(yǔ)句之間聲明的這個(gè)錯(cuò)誤處理器，那么出錯(cuò)時(shí)會(huì)立即結(jié)束整個(gè)存儲(chǔ)過(guò)程的執(zhí)行。

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

下面的錯(cuò)誤處理器指出，如果有發(fā)生數(shù)據(jù)行不存在的錯(cuò)誤，其實(shí)就是指在使用游標(biāo)(Cursor)或者SELECT INTO語(yǔ)句的情況，就把no_row_found 變量設(shè)為1，并繼續(xù)執(zhí)行。

DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_row_found = 1; 

譯者注：如果只是為了檢查 SELECT INTO 是否有行數(shù)據(jù)返回，MySQL 還有另外一個(gè) FOUND_ROWS() 內(nèi)置函數(shù)可以用。

下面的錯(cuò)誤處理器指出，如果發(fā)生了主鍵重復(fù)的錯(cuò)誤(MySQL的錯(cuò)誤碼為1062)，就將給出一條錯(cuò)誤提示并繼續(xù)代碼塊的執(zhí)行：

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

存儲(chǔ)過(guò)程中出錯(cuò)處理的示例

首先為了演示我們創(chuàng)建一張新表，表名是 article_tags：

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

article_tags 表保存了 article 和 tag 之間的關(guān)系。每個(gè) Article 可以對(duì)應(yīng)到多個(gè) Tag，反過(guò)來(lái)也是一樣。為簡(jiǎn)單起見(jiàn)，我們就不創(chuàng)建 article 和 tag 表了，因此也就不用給 article_tags 加外鍵約束了。

接下來(lái)，我們創(chuàng)建一個(gè)存儲(chǔ)過(guò)程來(lái)把 Article 的 id 和 Tag 的 id 插入到 article_tags 表中，注意這個(gè)存儲(chǔ)過(guò)程***一條語(yǔ)句返回了表中的記錄總數(shù)：

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 $$  

然后，通過(guò)下面的命令，我們給 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);  

這之后，我們就要嘗試插入一條重復(fù)的記錄來(lái)看看錯(cuò)誤處理器是否真的會(huì)被執(zhí)行到。

CALL insert_article_tags(1,3); 

 我們得到了一條錯(cuò)誤信息。不過(guò)因?yàn)槲覀兟暶鞯氖?CONTINUE 類型的錯(cuò)誤處理器，存儲(chǔ)過(guò)程會(huì)繼續(xù)往后執(zhí)行，所以結(jié)果我們也得到了 article 的 tag 的總數(shù)。

如果我們把 CONTINUE 類型的錯(cuò)誤處理器聲明成 EXIT，我們就只得到一個(gè)錯(cuò)誤提示了。讓我們?cè)倬帉?xiě)另外一個(gè)存儲(chǔ)過(guò)程：

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 $$  

***，我們可以嘗試新增一條重復(fù)主鍵的記錄看看效果：

CALL insert_article_tags_2(1,3); 

 可以看到這次只輸出了錯(cuò)誤信息就沒(méi)有繼續(xù)向下執(zhí)行了。

錯(cuò)誤處理器的優(yōu)先級(jí)

當(dāng)有多個(gè)錯(cuò)誤處理器都滿足特定錯(cuò)誤條件的時(shí)候，MySQL將按更明確者優(yōu)先的原則決定優(yōu)先級(jí)。

MySQL中的每個(gè)錯(cuò)誤都會(huì)映射到一個(gè)特定的錯(cuò)誤碼，因此錯(cuò)誤碼是最明確的。一個(gè) SQLSTATE 可以對(duì)應(yīng)到多個(gè) MySQL 錯(cuò)誤碼，所以沒(méi)那么明確。SQLEXCEPTION 和 SQLWARNING 分別指代的是 SQLSTATES 中類型相近的一組值，所以它的明確性***。

基于錯(cuò)誤處理器的優(yōu)先級(jí)規(guī)則，MySQL 錯(cuò)誤碼處理器，SQLSTATE 錯(cuò)誤處理器 和 SQLEXCEPTION錯(cuò)誤處理器順序上分別排在1、2、3位。

如果我們?cè)?insert_article_tags_3 存儲(chǔ)過(guò)程里聲明3個(gè)錯(cuò)誤處理器，像下面一樣：

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 $$  

當(dāng)我們通過(guò)下面的命令，試圖調(diào)用上面的存儲(chǔ)過(guò)程插入一條主鍵重復(fù)的記錄到article_tags表時(shí)：

CALL insert_article_tags_3(1,3); 

 你會(huì)看到， MySQL錯(cuò)誤碼綁定的那個(gè)處理器被調(diào)用了。

使用命名錯(cuò)誤條件

我們先看一個(gè)錯(cuò)誤處理器的聲明：

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

1051 這個(gè)錯(cuò)誤碼到底什么意思?想象一下如果你有很多的存儲(chǔ)過(guò)程，里面散布者這種數(shù)字，對(duì)于代碼維護(hù)來(lái)說(shuō)應(yīng)該就是噩夢(mèng)了。

幸運(yùn)的是，MySQL 為我們提供了一個(gè) DECLARE CONDITION 語(yǔ)句來(lái)聲明一個(gè)命名錯(cuò)誤條件，可以將上面的數(shù)字關(guān)聯(lián)為一個(gè)有意義的名字。

DECLARE CONDITION 語(yǔ)句的語(yǔ)法如下所示：

DECLARE condition_name CONDITION FOR condition_value; 

condition_value 可以是一個(gè)類似 1051 的 MySQL 錯(cuò)誤碼，或者一個(gè) SQLSTATE 值，然后 condition_name 就可以代表condition_value 來(lái)使用了。

所以之前的代碼我們就可以改寫(xiě)成下面的樣子：

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

這樣代碼的可讀性比之前明顯好多了。需要注意的是，要在存儲(chǔ)過(guò)程聲明一個(gè)命名條件的語(yǔ)句，則該語(yǔ)句必須出現(xiàn)在錯(cuò)誤處理器或者游標(biāo)聲明的前面。

一個(gè)在 handler 中實(shí)用的輔助函數(shù)

實(shí)際應(yīng)用中，存儲(chǔ)過(guò)程中的錯(cuò)誤被我們的錯(cuò)誤處理器捕獲了之后，你如果還想用類似 mysql 命令行那樣的格式返回對(duì)應(yīng)的錯(cuò)誤，可以聲明一個(gè)這樣的函數(shù)：

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$$  

那么在實(shí)際使用時(shí)，就可以在錯(cuò)誤處理器中這樣用：

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;  

那么在有錯(cuò)誤發(fā)生的時(shí)候，就可以得到如下的錯(cuò)誤提示了：