業務操作常涉及多張表:用戶註冊需寫入 `users`、`profiles`、`wallets`;訂單創建需扣減庫存、寫入訂單、記錄流水。若中間某條 SQL 失敗,已執行的 SQL 不能生效,否則產生「髒數據」(如用戶註冊了但無錢包)。事務保證 **原子性**:要麼全成功,要麼全回滾。
<?php
class DbTransaction {
private $mysqli;
public function __construct($host, $user, $pass, $dbname, $port = 3306, $charset = 'utf8mb4') {
$this->mysqli = new mysqli($host, $user, $pass, $dbname, $port);
if ($this->mysqli->connect_errno) {
throw new Exception("Connect failed: " . $this->mysqli->connect_error);
}
$this->mysqli->set_charset($charset);
}
/**
* 批量執行 SQL 事務
* @param array $sqls SQL 語句數組
* @return bool true=全部成功並提交, false=有失敗已回滾
*/
public function executeTransaction(array $sqls) {
$mysqli = $this->mysqli;
$mysqli->autocommit(FALSE); // 關閉自動提交
$mysqli->begin_transaction(); // 顯式開啟事務 (MySQLi 5.5+)
$results = array();
try {
foreach ($sqls as $sql) {
$result = $mysqli->query($sql);
$results[] = $result;
if ($result === false) {
throw new Exception("SQL Error: " . $mysqli->error . " | SQL: $sql");
}
}
$mysqli->commit(); // 全部成功提交
return true;
} catch (Exception $e) {
$mysqli->rollback(); // 任何失敗回滾
error_log("Transaction rolled back: " . $e->getMessage());
return false;
} finally {
$mysqli->autocommit(TRUE); // 恢復自動提交
}
}
public function close() { $this->mysqli->close(); }
}
// 使用示例
$db = new DbTransaction('localhost', 'user', 'pass', 'mydb');
$sqls = [
"INSERT INTO users (username, email) VALUES ('john', 'john@example.com')",
"INSERT INTO profiles (user_id, real_name) VALUES (LAST_INSERT_ID(), 'John Doe')",
"INSERT INTO wallets (user_id, balance) VALUES (LAST_INSERT_ID(), 0.00)"
];
if ($db->executeTransaction($sqls)) {
echo "註冊成功,事務已提交";
} else {
echo "註冊失敗,事務已回滾";
}
$db->close();
<?php
class PdoTransaction {
private $pdo;
public function __construct($dsn, $user, $pass) {
$this->pdo = new PDO($dsn, $user, $pass, [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
PDO::ATTR_EMULATE_PREPARES => false,
]);
}
public function executeTransaction(array $sqls) {
$this->pdo->beginTransaction();
try {
foreach ($sqls as $sql) {
$this->pdo->exec($sql); // 或 prepare/execute 參數綁定
}
$this->pdo->commit();
return true;
} catch (Exception $e) {
$this->pdo->rollBack();
error_log("Transaction failed: " . $e->getMessage());
return false;
}
}
}
CREATE TABLE、ALTER TABLE、TRUNCATE 會導致隱式提交,不能放在事務中。innodb_lock_wait_timeout。大事務中可設置保存點,回滾到部分點:
$mysqli->query("SAVEPOINT sp1");
// ... 一些操作
if ($error) $mysqli->query("ROLLBACK TO SAVEPOINT sp1"); // 僅回滾到 sp1
$mysqli->query("RELEASE SAVEPOINT sp1"); // 釋放保存點