MySQL 多查询 InnoDB

Question

我是 InnoDB 事务的新手。我正在学习，但我对此有疑问。

<?php

include_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 transaction

if (!$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) {&nbsp; &nbsp; // in case the db server confirms the correctness of the queries, commit the transaction&nbsp; &nbsp; $mysqli->commit();} else {&nbsp; &nbsp; // something went wrong&nbsp; &nbsp; $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在事务中包含语句，因为这将立即提交更改。