MySQL 事務處理與回滾錯誤完整解決方案

MySQL 事務是保證資料一致性的核心機制，但許多開發者因引擎不支援、語法錯誤或自動提交模式誤解而導致回滾失效。本文系統梳理兩種事務模式、引擎啟用步驟與實戰代碼範本。

兩種事務控制模式

模式一：顯式控制（推薦）

BEGIN;          -- 或 START TRANSACTION;
-- SQL 操作...
COMMIT;         -- 確認提交
ROLLBACK;       -- 錯誤時回滾

特點：僅包裹當前批次操作，結束後自動恢復自動提交模式，邏輯清晰不易出錯。

模式二：關閉自動提交

SET autocommit=0; -- 關閉自動提交
-- 後續所有 SQL 均屬同一事務
COMMIT;           -- 提交並隱式開啟新事務
ROLLBACK;         -- 回滾並隱式開啟新事務
SET autocommit=1; -- 記得重新開啟！

⚠️ 陷阱：COMMIT/ROLLBACK 後會**隱式開啟新事務**，若忘記重新開啟 autocommit=1，後續查詢將意外進入事務模式。

核心前提：InnoDB 引擎

**只有 InnoDB 和 BDB 支援事務**，預設 MyISAM **不支援**。啟用 InnoDB 步驟：

1. 編輯 my.ini，註解 skip-InnoDB（前加 #）並儲存。
2. 重啟 MySQL 服務（services.msc → MySQL → 重新啟動）。
3. 驗證：SHOW ENGINES; 或 SHOW VARIABLES LIKE 'have_%'; 確認 InnoDB = YES。
4. 建表指定引擎：ENGINE=InnoDB；既有表轉換：ALTER TABLE t ENGINE=InnoDB;。

PHP 實戰代碼範本（PDO 推薦，此處示範 mysqli 風格）

範本一：顯式事務（推薦）

$conn = mysqli_connect('localhost','root','root','test');
mysqli_set_charset($conn, 'utf8mb4');

mysqli_begin_transaction($conn); // 或 mysqli_query($conn, "START TRANSACTION");

$sql1 = "INSERT INTO `user` (`username`, `sex`) VALUES ('test1', '0')";
$sql2 = "INSERT INTO `user` (`username`, `sex`) VALUES ('test2', '1')"; // 故意寫錯欄位名測試回滾

$res1 = mysqli_query($conn, $sql1);
$res2 = mysqli_query($conn, $sql2);

if ($res1 && $res2) {
    mysqli_commit($conn);
    echo '提交成功。';
} else {
    mysqli_rollback($conn);
    echo '資料回滾：' . mysqli_error($conn);
}
mysqli_close($conn);

範本二：關閉自動提交

mysqli_query($conn, "SET AUTOCOMMIT=0");
// ... 同樣執行 SQL ...
if ($res1 && $res2) {
    mysqli_commit($conn);
} else {
    mysqli_rollback($conn);
}
mysqli_query($conn, "SET AUTOCOMMIT=1"); // 必須恢復！

常見錯誤排查清單

• ❌ 表引擎為 MyISAM → 改為 InnoDB
• ❌ 混用 BEGIN 與 autocommit=0 → 擇一使用
• ❌ 回滾後未恢復 autocommit=1 → 導致後續查詢卡住
• ❌ 異常未捕獲導致隱式提交 → 配合 try-catch 使用 PDO 事務更安全

掌握事務本質與引擎依賴，配合規範的代碼模式，才能真正守護資料完整性。