MySQL 多查询 InnoDB

Question

我是 InnoDB 事务的新手。我正在学习，但我对此有疑问。<?phpinclude_once("../../../../../wp-config.php");global $wpdb;$cats_table     = $wpdb->prefix . "jb_menu_groups";$relation       = $wpdb->prefix . "jb_relations";$mysqli = new mysqli($wpdb->dbhost, $wpdb->dbuser, $wpdb->dbpassword, $wpdb->dbname);if ($mysqli -> connect_errno) {    echo "Failed to connect to MySQL: " . $mysqli -> connect_error;    exit();}// Turn autocommit off$mysqli -> autocommit(FALSE);$mysqli -> query("DELETE FROM $cats_table WHERE id=6");$mysqli -> query("DELETE FROM $relation WHERE groupid=3");// Commit transactionif (!$mysqli -> commit()) {  echo "Commit transaction failed";  exit();}$mysqli -> rollback();$mysqli -> close();?>如果我创建一个错误的查询来测试提交和回滚，则代码会成功运行另一个查询。如何在一个事务中执行多个查询，如果一个查询有错误，请回滚并取消事务？

Answer 1

我个人只使用 PDO 和事务，但这就是它应该如何使用 mysqli：

// start the transaction

$mysqli->autocommit(false);

$catQuery = $mysqli->query("DELETE FROM $cats_table WHERE id=6");

$relationQuery = $mysqli->query("DELETE FROM $relation WHERE groupid=3");

if ($catQuery && $relationQuery) {

    // in case the db server confirms the correctness of the queries, commit the transaction

    $mysqli->commit();

} else {

    // something went wrong

    $mysqli->rollback();

}

// don't forget to reset the transaction mode again, in case your app isn't ending here

$mysqli->autocommit(true);

请确保不要CREATE, ALTER or DROP在事务中包含语句，因为这将立即提交更改。