MySQL 存儲過程異常處理實戰：DECLARE HANDLER 與 SIGNAL/RESIGNAL

MySQL 存儲過程異常處理實戰：DECLARE HANDLER 與 SIGNAL/RESIGNAL

存儲過程中妥善處理異常，能避免業務中斷、保證數據一致性，並向應用層返回可控的錯誤碼而非拋出原始 SQL 錯誤。本文系統講解 MySQL 異常處理機制，含 DECLARE HANDLER、SIGNAL/RESIGNAL 及最佳實踐。

一、異常處理核心概念

二、DECLARE HANDLER 語法

DECLARE {CONTINUE | EXIT | UNDO} HANDLER FOR condition_value statement;

-- condition_value 可為：
-- 1. MySQL 錯誤碼 (如 1062 重複鍵)
-- 2. SQLSTATE '23000' (標準狀態碼)
-- 3. 條件名稱 (DECLARE ... CONDITION FOR ...)
-- 4. SQLWARNING / NOT FOUND / SQLEXCEPTION (類別捕獲)

三、實戰範例：重複鍵優雅處理

DELIMITER $$
CREATE PROCEDURE sp_add_user(
  IN p_username VARCHAR(30),
  IN p_email VARCHAR(100),
  OUT p_result INT,
  OUT p_msg VARCHAR(100)
)
BEGIN
  DECLARE EXIT HANDLER FOR 1062  -- 捕獲重複鍵錯誤
  BEGIN
    SET p_result = -1;
    SET p_msg = '用戶名或郵箱已存在';
  END;

  DECLARE EXIT HANDLER FOR SQLEXCEPTION  -- 捕獲其他異常
  BEGIN
    SET p_result = -2;
    SET p_msg = '系統錯誤，請稍後重試';
    ROLLBACK;  -- 確保事務回滾
  END;

  START TRANSACTION;
  INSERT INTO users (username, email) VALUES (p_username, p_email);
  SET p_result = 1;
  SET p_msg = '註冊成功';
  COMMIT;
END$$
DELIMITER ;

四、命名條件：提升可讀性

DECLARE duplicate_key CONDITION FOR 1062;
DECLARE fk_violation CONDITION FOR 1216;

DECLARE EXIT HANDLER FOR duplicate_key SET @ret = -1;
DECLARE EXIT HANDLER FOR fk_violation SET @ret = -2;

五、SIGNAL / RESIGNAL：主動拋出/重拋異常 (MySQL 5.5+)

SIGNAL：任意位置主動拋出錯誤，可自定義 SQLSTATE 與訊息。

CREATE PROCEDURE divide(IN a INT, IN b INT, OUT result DECIMAL(10,2))
BEGIN
  IF b = 0 THEN
    SIGNAL SQLSTATE '45000'  -- 用戶自定義異常
      SET MESSAGE_TEXT = '除數不能為零', MYSQL_ERRNO = 1001;
  ELSE
    SET result = a / b;
  END IF;
END;

RESIGNAL：僅能在 HANDLER 內使用，重拋或修改當前異常。

CREATE PROCEDURE safe_divide(IN a INT, IN b INT, OUT result DECIMAL(10,2))
BEGIN
  DECLARE division_by_zero CONDITION FOR SQLSTATE '22012';
  DECLARE CONTINUE HANDLER FOR division_by_zero
    RESIGNAL SET MESSAGE_TEXT = '自定義：除數不能為零';

  IF b = 0 THEN
    SIGNAL division_by_zero;
  ELSE
    SET result = a / b;
  END IF;
END;

六、最佳實踐清單

1. 事務邊界清晰：異常處理塊中執行 ROLLBACK，避免髒數據提交。
2. 錯誤碼標準化：定義業務錯誤碼表，應用層統一處理，勿直接暴露 SQLSTATE。
3. 捕獲順序：具體錯誤碼/ SQLSTATE 先於泛類 (SQLEXCEPTION)，MySQL 按優先級匹配。
4. 避免 UNDO：MySQL 暫不支援 UNDO HANDLER，使用 EXIT + ROLLBACK 替代。
5. 日誌記錄：HANDLER 中插入錯誤日誌表，便於事後追蹤。
6. 測試覆蓋：單元測試需包含正常、邊界、異常三類場景。

善用異常處理機制，能讓存儲過程成為健壯、可維護的業務邏輯載體，而非脆弱的 SQL 腳本集合。