使用autoCommit的Php

Question

我已经写了一段代码来关闭AutoCommit：

<?php 
$con = mysqli_connect("localhost","root","","databases");
if (mysqli_connect_errno($con))
{
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
if(mysqli_query($con,"SET autocommit=0")){//tried using mysqli_autocommit($con,FALSE);

// Insert some values 
$a=mysqli_query($con,"INSERT INTO member VALUES (2,'Peter','Peter')");
$b=mysqli_query($con,"INSERT INTO member VALUES (3,honey,'honey')");
echo "a:";
echo $a;
echo "b:";
echo $b;
if($a&&$b){
mysqli_query($con,"COMMIT");//tried using mysqli_commit($con);
echo "in if";
}
else{
mysqli_query($con,"ROLLBACK");//tried using mysqli_rollback($con);
echo "in else";
}
}
mysqli_close($con);
?>

此代码的输出为: a:1 b: in else

但是在表中，第一行是插入的。我用wamp来做这件事。请让我知道我哪里错了

Answer 1

我使用这个函数来启动、提交和回滚事务：

function transactionStart($dbConnection){
    return $dbConnection->autocommit(false);
}
function transactionCommit($dbConnection) {
    $dbConnection->commit();
    return  $dbConnection->autocommit(true);
}
function transactionRollback($dbConnection) {
     $dbConnection->rollback();
     return $dbConnection->autocommit(true);
}

为您的特定情况编写一点伪代码：

//TODO: Build up connection, results in $con variable
transactionStart($con);
//TODO: Insert data
if(*all data inserted correctly*){
  transactionCommit($con);
} else{
  transactionRollback($con);
}

Answer 2

在发出一系列构成事务的sql命令之前，您需要启动一个事务。

 START TRANSACTION

将在SQL中执行此操作。或者您可以使用

$con->autocommit ( false );
$con->begin_transaction(); 
... transactional SQL statements ...
$con->commit();

干净利落地做这件事。

PHP + MySQL transactions examples

@Sylvain Leroux是对的！如果您使用的是MyISAM，那么您可以发出您想要的所有事务命令，但它们仍然不能工作。事务支持在InnoDB中。

Answer 3

我认为您的第二列是一个varchar。您的第二个insert语句缺少第二列的引号。请替换插入查询，如下所示

$a = mysqli_query($con,"INSERT INTO member VALUES (2,'Peter','Peter')");
$b = mysqli_query($con,"INSERT INTO member VALUES (3,'honey','honey')");