MySQL 存儲過程支援類似 TRY...CATCH 的結構化異常處理機制。本文透過完整可執行範例，詳細講解 DECLARE ... HANDLER 三種條件類型、CONTINUE/EXIT/UNDO 三種動作模式、搭配事務控制實現「部分提交、部分回滾、錯誤記錄」的工程級模式。
DECLARE handler_action HANDLER
FOR condition_value [, condition_value] ...
statement
-- handler_action: CONTINUE | EXIT | UNDO (MySQL 8.0+ 支援 UNDO)
-- condition_value:
-- SQLSTATE 'HY000' -- 具體錯誤碼
-- SQLEXCEPTION -- 所有錯誤 (SQLSTATE 以 00、01、02 開頭以外)
-- SQLWARNING -- 所有警告 (SQLSTATE 01 開頭)
-- NOT FOUND -- 游標無資料、SELECT ... INTO 無結果 (SQLSTATE 02 開頭)
-- 'custom_condition' -- 自訂條件 (需 DECLARE CONDITION)
DROP PROCEDURE IF EXISTS sp_batch_process_orders;
DELIMITER $$
CREATE PROCEDURE sp_batch_process_orders(IN p_batch_size INT)
BEGIN
-- 變數宣告
DECLARE v_processed INT DEFAULT 0;
DECLARE v_failed INT DEFAULT 0;
DECLARE v_done INT DEFAULT 0;
DECLARE v_order_id BIGINT;
DECLARE v_amount DECIMAL(12,2);
DECLARE v_err_msg VARCHAR(500);
-- 游標：待處理訂單
DECLARE cur_orders CURSOR FOR
SELECT order_id, amount FROM orders WHERE status = 'PENDING' LIMIT p_batch_size;
-- 異常處理器
DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = 1;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
BEGIN
GET DIAGNOSTICS CONDITION 1
v_err_msg = MESSAGE_TEXT;
INSERT INTO proc_error_log (proc_name, error_msg, created_at)
VALUES ('sp_batch_process_orders', v_err_msg, NOW());
SET v_failed = v_failed + 1;
END;
OPEN cur_orders;
read_loop: LOOP
FETCH cur_orders INTO v_order_id, v_amount;
IF v_done THEN LEAVE read_loop; END IF;
-- 單筆訂單獨立事務 (部分成功、部分失敗互不影響)
START TRANSACTION;
UPDATE orders SET status = 'PROCESSING' WHERE order_id = v_order_id;
-- 模擬業務邏輯：扣款、寫流水、發通知
INSERT INTO payment_flow (order_id, amount, type) VALUES (v_order_id, v_amount, 'CHARGE');
UPDATE accounts SET balance = balance - v_amount WHERE user_id = (SELECT user_id FROM orders WHERE order_id = v_order_id);
UPDATE orders SET status = 'COMPLETED' WHERE order_id = v_order_id;
COMMIT;
SET v_processed = v_processed + 1;
END LOOP;
CLOSE cur_orders;
-- 回傳統計結果
SELECT v_processed AS processed_count, v_failed AS failed_count;
END$$
DELIMITER ;
| 技術點 | 說明 | |
|---|---|---|
CONTINUE HANDLER FOR NOT FOUND | 游標 FETCH 結束時觸發，設定旗標跳出迴圈，避免「未處理 NOT FOUND」導致過程終止 | |
CONTINUE HANDLER FOR SQLEXCEPTION | 捕獲所有 SQL 錯誤 (死鎖、鍵衝突、資料截斷等)，記錄錯誤日誌、計數，不中斷主流程 | |
GET DIAGNOSTICS | MySQL 5.6+ 取得詳細錯誤資訊 (錯誤碼、訊息、SQLSTATE)，替代舊版 @@error_count | |
| 單筆獨立事務 | 每筆訂單 START TRANSACTION ... COMMIT，失敗只回滾該筆，不影響其他筆 — 業務隔離性 | |
| 錯誤日誌表 | proc_error_log 記錄過程名、錯誤訊息、時間，便事後追蹤 |
| Action | 行為 | 適用場景 |
|---|---|---|
CONTINUE | 執行 handler statement，然後繼續執行後續語句 | 批次處理、日誌記錄、計數統計、游標結束控制 |
EXIT | 執行 handler statement，然後離開當前 BEGIN...END 區塊 (若在外層則結束過程) | 嚴重錯誤需立即停止、驗證前置條件失敗 |
UNDO | 執行 handler statement，然後回滾當前事務，再離開區塊 (MySQL 8.0+) | 需自動回滾並退出的業務邏輯 |
-- 宣告自訂條件
DECLARE insufficient_balance CONDITION FOR SQLSTATE '45000';
-- 觸發自訂錯誤
IF new_balance < 0 THEN
SIGNAL insufficient_balance
SET MESSAGE_TEXT = '帳戶餘額不足', MYSQL_ERRNO = 45000;
END IF;
-- 捕獲自訂條件
DECLARE EXIT HANDLER FOR insufficient_balance
BEGIN
INSERT INTO biz_error_log (code, msg) VALUES (45000, '帳戶餘額不足');
ROLLBACK;
END;
BEGIN...END 區塊內有效；巢狀區塊內可覆蓋外層 Handler。FETCH、SELECT ... INTO、RETURNING (8.0) 無結果時觸發；INSERT ... SELECT 影響 0 行不觸發。CONTINUE HANDLER 不會自動回滾事務，需在 Handler 內顯式 ROLLBACK 或依賴外層邏輯。SQLEXCEPTION，建議在 Handler 內 ROLLBACK 後 SLEEP(RAND()*0.1) 重試 (需迴圈邏輯支援)。
CREATE PROCEDURE sp_upsert_account_balance(IN p_user_id BIGINT, IN p_delta DECIMAL(12,2))
BEGIN
DECLARE v_retry INT DEFAULT 0;
DECLARE v_deadlock BOOLEAN DEFAULT FALSE;
REPEAT
SET v_deadlock = FALSE;
START TRANSACTION;
UPDATE accounts SET balance = balance + p_delta, version = version + 1
WHERE user_id = p_user_id AND balance + p_delta >= 0;
IF ROW_COUNT() = 0 THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '餘額不足或用戶不存在';
END IF;
COMMIT;
UNTIL v_retry >= 3 OR NOT v_deadlock END REPEAT;
-- 死鎖重試邏輯 (簡化版，實際需在 HANDLER 內設置 v_deadlock=TRUE)
END;
掌握 DECLARE HANDLER、GET DIAGNOSTICS、SIGNAL/RESIGNAL 三大核心，配合細粒度事務控制，即可編寫健壯、可觀測、可維運的 MySQL 存儲過程。下一篇我們將探討 MySQL Audit 審計插件開發與部署全流程，敬請期待。
