MySQL 存儲過程異常處理進階：HANDLER 優先級、命名條件與實戰模式

MySQL 存儲過程異常處理進階：HANDLER 優先級、命名條件與實戰模式

延續基礎篇，本文聚焦 DECLARE HANDLER 的優先級機制、DECLARE CONDITION 命名條件最佳實踐，以及生產級錯誤處理模式：統一錯誤碼、事務保護、日誌記錄、應用層協作。

一、HANDLER 優先級實戰驗證

當多個 HANDLER 匹配同一錯誤，MySQL 按 具體 > 泛化 順序選擇：

1. MySQL 錯誤碼 (如 1062)
2. SQLSTATE (如 '23000')
3. SQLEXCEPTION / SQLWARNING / NOT FOUND

CREATE PROCEDURE sp_handler_priority()
BEGIN
  DECLARE EXIT HANDLER FOR 1062 SELECT 'Level 1: MySQL 1062';
  DECLARE EXIT HANDLER FOR SQLSTATE '23000' SELECT 'Level 2: SQLSTATE 23000';
  DECLARE EXIT HANDLER FOR SQLEXCEPTION SELECT 'Level 3: SQLEXCEPTION';

  INSERT INTO t_pk (id) VALUES (1);  -- ok
  INSERT INTO t_pk (id) VALUES (1);  -- 1062
END;

-- 輸出: Level 1: MySQL 1062

二、命名條件：消除魔術數字

-- 聲明 (必須在 HANDLER/CURSOR 之前)
DECLARE duplicate_key CONDITION FOR 1062;
DECLARE fk_violation CONDITION FOR 1216;
DECLARE data_truncated CONDITION FOR SQLSTATE '22001';

-- 使用
DECLARE EXIT HANDLER FOR duplicate_key
BEGIN
  SET @ret_code = -1001;
  SET @ret_msg = '唯一約束衝突';
END;

DECLARE EXIT HANDLER FOR fk_violation
  SET @ret_code = -1002;

三、生產級錯誤處理模板

DELIMITER $$
CREATE PROCEDURE sp_biz_template(
  IN p_param INT,
  OUT p_code INT,
  OUT p_msg VARCHAR(200)
)
MODIFIES SQL DATA
BEGIN
  -- 1. 業務自定義條件
  DECLARE biz_rule_violation CONDITION FOR SQLSTATE '45000';

  -- 2. 系統異常兜底 (最外層)
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    ROLLBACK;
    CALL sp_log_error('sp_biz_template', 'SQLEXCEPTION', @sqlstate, @msg);
    SET p_code = -999;
    SET p_msg = '系統內部錯誤，請聯繫管理員';
  END;

  -- 3. 具體錯誤碼處理 (優先級高於 SQLEXCEPTION)
  DECLARE EXIT HANDLER FOR 1062  -- 重複鍵
  BEGIN
    ROLLBACK;
    SET p_code = -1001;
    SET p_msg = '數據已存在，請勿重複提交';
  END;

  DECLARE EXIT HANDLER FOR 1216  -- 外鍵違反
  BEGIN
    ROLLBACK;
    SET p_code = -1002;
    SET p_msg = '關聯數據不存在';
  END;

  -- 4. 業務規則違反 (自定義 SQLSTATE 45000)
  DECLARE EXIT HANDLER FOR biz_rule_violation
  BEGIN
    ROLLBACK;
    SET p_code = -2001;
    SET p_msg = '業務規則校驗失敗';
  END;

  -- 5. 正常業務流程
  START TRANSACTION;

  -- 校驗邏輯
  IF p_param < 0 THEN
    SIGNAL biz_rule_violation SET MESSAGE_TEXT = '參數不能為負';
  END IF;

  -- 核心寫操作
  INSERT INTO biz_table (col1) VALUES (p_param);
  UPDATE account SET balance = balance - 100 WHERE id = 1;

  COMMIT;
  SET p_code = 0;
  SET p_msg = 'SUCCESS';
END$$
DELIMITER ;

四、錯誤日誌記錄輔助過程

CREATE TABLE proc_error_log (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  proc_name VARCHAR(64),
  error_type VARCHAR(32),  -- 'MYSQL_ERRNO' | 'SQLSTATE' | 'CUSTOM'
  error_code VARCHAR(20),
  error_msg TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE PROCEDURE sp_log_error(
  IN p_proc VARCHAR(64),
  IN p_type VARCHAR(32),
  IN p_code VARCHAR(20),
  IN p_msg TEXT
)
BEGIN
  INSERT INTO proc_error_log (proc_name, error_type, error_code, error_msg)
  VALUES (p_proc, p_type, p_code, p_msg);
END;

五、應用層協作規約

六、避坑清單

1. HANDLER 順序：具體錯誤碼/ SQLSTATE 寫前面，SQLEXCEPTION 兜底寫最後。
2. ROLLBACK 必須在 HANDLER 內：EXIT HANDLER 退出區塊前唯一機會回滾。
3. 不要在 HANDLER 中再次 SIGNAL 同類異常：會導致無限循環或遞歸深度超限。
4. NOT FOUND 僅用於游標/SELECT INTO：不用於捕獲「查無數據」的業務邏輯，應用 IF v_cnt = 0 THEN ...。
5. UNDO 不支援：文檔雖列出但 MySQL 至今未實現，勿使用。

將異常處理標準化為「感知→分類→記錄→轉換→回滾→上報」流水線，能讓資料庫層輸出確定性、可觀測、可治理的錯誤語義，大幅降低系統故障 MTTR。