MySQL 存儲過程中 SAVEPOINT 與 ROLLBACK TO 實戰示例

代碼示例

DELIMITER $$

DROP PROCEDURE IF EXISTS `test`.`handlerdemo`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `handlerdemo`()
BEGIN
    DECLARE X2 INT;
    DECLARE CONTINUE HANDLER FOR SQLWARNING SET X2 = 1;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET X2 = 2;
    DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET X2 = 3;
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET X2 = 4;

    START TRANSACTION;
    SAVEPOINT savepoint_1;

    BEGIN
        INSERT INTO eugene VALUES (1);
        INSERT INTO eugene VALUES (2);
        SELECT X2;
    END;

    ROLLBACK TO SAVEPOINT savepoint_1;
    INSERT INTO eugene VALUES (3);
END$$

DELIMITER ;

-- 測試表
CREATE TABLE `eugene` (
    `t1` int(4) DEFAULT NULL
);

關鍵點解析

1. 聲明處理器：捕獲 SQLWARNING、NOT FOUND、特定 SQLSTATE、SQLEXCEPTION，設置變量 X2 區分錯誤類型。
2. SAVEPOINT：在事務中創建保存點 savepoint_1。
3. 內層 BEGIN...END：執行兩條 INSERT，若拋出異常（如主鍵衝突），處理器捕獲並設置 X2，事務不會自動回滾（因為是 CONTINUE HANDLER）。
4. ROLLBACK TO SAVEPOINT：回滾到保存點，撤銷內層兩條 INSERT，但事務仍開啟。
5. 後續 INSERT：插入值 3，最終提交時表中只有 3。

運行驗證

CALL handlerdemo();
SELECT * FROM eugene; -- 僅返回 3

應用場景

• 存儲過程中分步驗證業務邏輯，局部失敗回滾而不影響整體事務。
• 批量導入數據時，單條錯誤回滾到檢查點，繼續處理後續數據。
• 嵌套事務模擬（MySQL 不支持真正嵌套事務，SAVEPOINT 是替代方案）。

注意事項

• CONTINUE HANDLER 不會自動回滾，需顯式 ROLLBACK TO SAVEPOINT。
• EXIT HANDLER 會退出當前 BEGIN...END 塊。
• 保存點名稱在存儲過程內唯一。
• MySQL 5.0+ 支持，建議 5.7+ 使用。