MySQL存儲過程異常處理最佳實踐

MySQL存儲過程異常處理最佳實踐

生產環境中，存儲過程不應直接拋出錯誤中斷調用方，而應通過返回碼或自定義異常處理機制優雅地處理錯誤。本文詳細講解MySQL中DECLARE HANDLER的用法、異常類型分類及事務回滾模式。

一、異常處理核心語法

DECLARE handler_action HANDLER FOR condition_value statement;

• handler_action：CONTINUE（繼續執行後續語句）或 EXIT（退出當前BEGIN...END塊）
• condition_value：
  • SQLSTATE '错误代码'（如'23000'主鍵衝突）
  • SQLWARNING（所有警告）
  • NOT FOUND（游標無數據、SELECT...INTO無結果）
  • SQLEXCEPTION（所有異常，相當於Oracle的OTHERS）
  • 可組合：FOR SQLWARNING, NOT FOUND, SQLEXCEPTION
• statement：單條語句或BEGIN...END塊

二、基本異常捕獲與返回碼模式

無異常處理：直接拋錯

CREATE PROCEDURE test_proc_ins1(IN i_id INT, IN i_name VARCHAR(100))
BEGIN
    INSERT INTO testproc VALUES (i_id, i_name);
    INSERT INTO testproc VALUES (i_id, i_name); -- 重複主鍵拋出ERROR 1062 (23000)
END;

有異常處理：返回狀態碼

CREATE PROCEDURE test_proc_ins1(
    IN i_id INT, 
    IN i_name VARCHAR(100), 
    OUT o_ret INT
)
BEGIN
    -- 捕獲主鍵衝突(23000)，設置返回碼-1
    DECLARE EXIT HANDLER FOR SQLSTATE '23000' SET o_ret = -1;
    -- 或捕獲所有異常
    -- DECLARE EXIT HANDLER FOR SQLWARNING, NOT FOUND, SQLEXCEPTION SET o_ret = -1;

    INSERT INTO testproc VALUES (i_id, i_name);
    INSERT INTO testproc VALUES (i_id, i_name);
    SET o_ret = 1; -- 成功返回1
END;

調用方（如Java）只需檢查o_ret即可判斷成敗，無需捕獲SQLException。

三、針對性異常處理：事務回滾模式

對於關鍵業務，需在特定錯誤發生時回滾事務並記錄錯誤：

DELIMITER //
CREATE PROCEDURE TEST()
BEGIN
    -- 定義異常處理器：遇到任何異常/警告/未找到，回滾並記錄
    DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING, NOT FOUND
    BEGIN
        ROLLBACK;
        INSERT INTO bb VALUES ('error');
    END;

    START TRANSACTION;
    INSERT INTO aa VALUES (1);
    INSERT INTO aa VALUES (2); -- 假設此處拋異常
    COMMIT;
END;
//
DELIMITER ;
CALL TEST();

此模式保證事務原子性：要麼全成功，要麼全回滾並留痕。

四、實戰建議與注意事項

1. 精確捕獲：優先使用SQLSTATE捕獲特定錯誤（如23000完整性約束），避免誤吞業務異常。
2. 層級選擇：
   • CONTINUE：適合日誌記錄、設置標誌位後繼續流程
   • EXIT：適合嚴重錯誤、事務回滾、直接退出
3. 事務邊界清晰：START TRANSACTION與COMMIT/ROLLBACK成對出現，異常處理器中必須顯式ROLLBACK。
4. 避免嵌套陷阱：異常處理器內若再發生異常，MySQL不再觸發外層處理器，需謹慎編寫處理邏輯。
5. 測試覆蓋：單元測試必須包含主鍵衝突、外鍵失敗、數據截斷、死鎖等場景。

五、總結

MySQL存儲過程異常處理的核心是「預判風險、分類處理、狀態返回、事務保護」。合理使用DECLARE HANDLER，讓存儲過程成為健壯、可維護的業務邏輯封裝層，而非脆弱的SQL腳本集合。