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 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;
下面是另一個錯誤處理器，指出如果發生任何錯誤就回滾之前的操作，給出一條錯誤信息，并退出當前代碼塊的執行。如果你是在聲明存儲過程的 BEGIN 和 END 語句之間聲明的這個錯誤處理器，那么出錯時會立即結束整個存儲過程的執行。
- 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);
我們得到了一條錯誤信息。不過因為我們聲明的是 CONTINUE 類型的錯誤處理器，存儲過程會繼續往后執行，所以結果我們也得到了 article 的 tag 的總數。
如果我們把 CONTINUE 類型的錯誤處理器聲明成 EXIT，我們就只得到一個錯誤提示了。讓我們再編寫另外一個存儲過程：
- 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位。
如果我們在 insert_article_tags_3 存儲過程里聲明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 這個錯誤碼到底什么意思?想象一下如果你有很多的存儲過程，里面散布者這種數字，對于代碼維護來說應該就是噩夢了。
幸運的是，MySQL 為我們提供了一個 DECLARE CONDITION 語句來聲明一個命名錯誤條件，可以將上面的數字關聯為一個有意義的名字。
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 中實用的輔助函數
實際應用中，存儲過程中的錯誤被我們的錯誤處理器捕獲了之后，你如果還想用類似 mysql 命令行那樣的格式返回對應的錯誤，可以聲明一個這樣的函數：
- 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;
那么在有錯誤發生的時候，就可以得到如下的錯誤提示了：
