MySQL 預存程序事務回滾機制詳解與實戰代碼

核心邏輯

在預存程序中透過 DECLARE ... HANDLER FOR SQLEXCEPTION 捕獲異常，並在 Handler 中執行 ROLLBACK，即可實現事務級的自動回滾。

完整預存程序代碼

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `test_procedure`()
BEGIN
    DECLARE errno INT DEFAULT 0;
    -- 定義異常處理器：遇到任何 SQL 異常 → 回滾 + 標記錯誤碼
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET errno = 1;
    END;

    START TRANSACTION;
    SET errno = 0;

    -- 第一條：正常插入
    INSERT INTO test(name) VALUES ('kaka');

    -- 第二條：故意插入重複主鍵 id=1，觸發異常
    INSERT INTO test(id, name) VALUES (1, 'papa');

    COMMIT;
    SELECT errno AS error_code;  -- 0=成功, 1=回滾
END$$
DELIMITER ;

測試場景與驗證

1. 初始數據： 表中已存在 (1, 'baby')。
2. 呼叫程序： CALL test_procedure();
3. 預期行為：
   • 第一條 INSERT 成功；
   • 第二條 INSERT 因主鍵衝突拋出異常 → Handler 執行 ROLLBACK，errno=1；
   • 事務提交被跳過，最終表中仍只有 (1, 'baby')。

建表腳本

CREATE DATABASE IF NOT EXISTS `test`;
USE `test`;
DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

關鍵點總結

• DECLARE CONTINUE HANDLER 讓程序在異常後繼續執行（而非 EXIT 直接退出）。
• Handler 內必須先 ROLLBACK 再設標記，順序不可顛倒。
• 僅支援 InnoDB 等事務引擎；MyISAM 無效。
• 生產環境建議配合 GET DIAGNOSTICS 記錄具體錯誤碼/訊息，便於審計。