如何开始和结束mysqli的交易？(How to start and end transaction

据我了解，一旦我们调用事务开始$mysqli->autocommit(FALSE); 声明并呼吁结束后$mysqli->commit(); 命令像在下面的例子。

<?php
//Start transaction 
$mysqli->autocommit(FALSE);
$mysqli->query('UPDATE `table` SET `col`=2');
$mysqli->query('UPDATE `table1` SET `col1`=3;');
$mysqli->commit();
//End transaction

//Executing other queries without transaction control
$mysqli->query("Select * from table1");
$mysqli->query("Update table1 set col1=2");
//End of executing other queries without transaction control

//Start transaction 
$mysqli->autocommit(FALSE);
$mysqli->query('UPDATE `table` SET `col`=2');
$mysqli->query('UPDATE `table1` SET `col1`=3;');
$mysqli->commit();
//End transaction
?>

我理解正确的？如果不是请您指正，因为它实际上是使用交易在现实生活中我的第一次。

谢谢。

Answer 1:

那么根据php的文档，你说得对。

<?php
$mysqli = new mysqli("localhost", "my_user", "my_password", "world");

/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

$mysqli->query("CREATE TABLE Language LIKE CountryLanguage");

/* set autocommit to off */
$mysqli->autocommit(FALSE);

/* Insert some values */
$mysqli->query("INSERT INTO Language VALUES ('DEU', 'Bavarian', 'F', 11.2)");
$mysqli->query("INSERT INTO Language VALUES ('DEU', 'Swabian', 'F', 9.4)");

/* commit transaction */
$mysqli->commit();

/* drop table */
$mysqli->query("DROP TABLE Language");

/* close connection */
$mysqli->close();
?>

在上面的例子：

在CREATE TABLE被自动提交，因为它是默认的行为。
在INSERT INTO 不会自动提交，因为的autocommit(FALSE)
的DROP TABLE是自动致力于因为autocommit(FALSE)是由复位 ->commit(); 。

Answer 2:

j0k主要是正确的，除了删除表。

自动提交未与打开 - >提交（）

相反，DROP TABLE是一个DDL查询，DDL查询始终是隐含提交，并提交所有以前未提交的工作。

所以，如果你没有犯工作中，DDL查询将迫使这个承诺。

Answer 3:

准备SQL语句一次，然后多次执行它：

<?php
$Mysqli = new mysqli("host","user","pass","base");

// check connection
if(mysqli_connect_errno())
{
  printf("Connect failed: %s\n",mysqli_connect_error());
  exit();
}

// some data for db insertion
$countries=['Austria','Belgia','Croatia','Denmark','Estonia'];

// explicitly begin DB transaction
$Mysqli->begin_transaction();

// prepare statement (for multiple inserts) only once
$stmt=$Mysqli->prepare("INSERT INTO table(column) VALUES(?)");

// bind (by reference) prepared statement with variable $country
$stmt->bind_param('s',$country);

// load value from array into referenced variable $country
foreach($countries as $country)
{
  //execute prep stat more times with new values
  //$country is binded (referenced) by statement
  //each execute will get new $country value
  if(!$stmt->execute())
  {
    // rollback if prep stat execution fails
    $Mysqli->rollback();
    // exit or throw an exception
    exit();
  }
}

// close prepared statement
$stmt->close();

// commit transaction
$Mysqli->commit();

// close connection
$Mysqli->close();

?>