MySQL 儲存過程中使用事務：完整實例與錯誤處理模板

事務在儲存過程中的關鍵要素

1. 宣告異常處理器：DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET @err = 1;
2. 標記變數：DECLARE err INT DEFAULT 0;
3. 顯式開啟/提交/回滾：START TRANSACTION; / COMMIT; / ROLLBACK;
4. 根據標記決定提交或回滾

完整範本

DROP PROCEDURE IF EXISTS `createBusiness`;
CREATE PROCEDURE `createBusiness`(IN `p_count` INT)
BEGIN
    -- 1. 變數宣告
    DECLARE `v_flag` INT DEFAULT `p_count`;
    DECLARE `v_uuid` CHAR(32);
    DECLARE `v_now` TIMESTAMP;
    DECLARE `v_err` INT DEFAULT 0;

    -- 2. 異常處理器：捕捉任何 SQL 異常，設定錯誤標記
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET `v_err` = 1;

    -- 3. 開啟事務
    START TRANSACTION;

    -- 4. 業務迴圈
    WHILE `v_flag` > 0 DO
        -- 生成不帶連字號的 UUID
        SET `v_uuid` = REPLACE(UUID(), '-', '');
        -- 當前時間戳
        SET `v_now` = CURRENT_TIMESTAMP();

        -- 核心插入邏輯
        INSERT INTO `business_table` (
            `id`, `title`, `keyword`, `has_image`, `is_today_head`,
            `is_show_in_home`, `is_bigness`, `publish_time`,
            `origin_id`, `modify_time`, `is_analysis`
        ) VALUES (
            `v_uuid`,
            CONCAT('事件標題', `v_flag`),
            CONCAT('關鍵字', `v_flag`),
            1, 1, 0, 0,
            `v_now`,
            CONCAT('xxxxxxx', `v_flag`),
            `v_now`,
            1
        );

        -- 迴圈計數器遞減
        SET `v_flag` = `v_flag` - 1;

        -- 測試用：模擬第 7 次失敗
        -- IF `v_flag` = 7 THEN SET `v_err` = 1; END IF;
    END WHILE;

    -- 5. 根據錯誤標記提交或回滾
    IF `v_err` = 0 THEN
        COMMIT;
        SELECT 'OK' AS `result`;
    ELSE
        ROLLBACK;
        SELECT 'ERR' AS `result`;
    END IF;
END;

關鍵點解析

進階：嵌套事務與 Savepoint

START TRANSACTION;
SAVEPOINT sp1;
-- 子操作
IF error THEN ROLLBACK TO SAVEPOINT sp1; END IF;
-- 繼續其他操作
COMMIT;

注意：MySQL 不支援真正的嵌套事務，SAVEPOINT 是替代方案。

最佳實踐清單

• 儲存過程只做資料邏輯，不做複雜計算/外部呼叫。
• 事務範圍越小越好，避免長事務阻塞鎖、膨脹 Undo Log。
• 異常處理器只設標記，不在處理器中執行 ROLLBACK（會導致隱式提交）。
• 測試時故意製造錯誤（重複鍵、NULL 違反）驗證回滾邏輯。
• 生產環境監控長事務：SELECT * FROM information_schema.INNODB_TRX WHERE TIME_TO_SEC(TIMEDIFF(NOW(), trx_started)) > 60;

結語

在儲存過程中正確使用事務，關鍵在於異常處理器 + 錯誤標記 + 顯式 COMMIT/ROLLBACK。掌握此模板，能確保批次業務操作的原子性，避免「半成功」導致資料不一致。