php 如何在mysqli中开始和结束事务？

How to start and end transaction in mysqli?

提问by Bakhtiyor

As far as I understood transaction starts once we call $mysqli->autocommit(FALSE);statement and ends after calling $mysqli->commit();command like in the example below.

据我所知，事务在我们调用$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
?>

Have I understood correctly? If not could you please correct me, because it is actually my first time using transactions in real life.

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

Thank you.

谢谢你。

采纳答案by j0k

Well according to the php doc, you're right.

那么根据php doc，你是对的。

<?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();
?>

In the example above:

在上面的例子中：

• the CREATE TABLEis auto committed because it's the default behaviour.
• the INSERT INTOaren'tauto committed because of the autocommit(FALSE).
• the DROP TABLEis auto committed because the autocommit(FALSE)was resetby the ->commit();.

• 这CREATE TABLE是自动提交的，因为它是默认行为。
• 在INSERT INTO没有自动致力于因autocommit(FALSE)。
• 将DROP TABLE被自动提交，因为autocommit(FALSE)是复位通过->commit();。

回答by Bolovsky

j0k is mainly right, except in the drop table.

j0k 主要是对的，除了在 drop 表中。

The auto commit is not turned on with the ->commit()

自动提交未通过 ->commit() 打开

Instead, the DROP TABLE is a DDL query, and DDL queries are always implicitly committed and will commit all your previously non committed work.

相反，DROP TABLE 是一个 DDL 查询，并且 DDL 查询总是隐式提交，并将提交您以前未提交的所有工作。

So, if you did not commit the work, the DDL query would force this commit.

因此，如果您没有提交工作，DDL 查询将强制执行此提交。

回答by sbrbot

Prepare SQL statement ONCE, and then execute it SEVERAL times:

准备 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();

?>

回答by Patec

You think that commitautomatically switches autocommitback to true? A comment in the PHP Docsays NO!

你认为commit自动切换autocommit回true? PHP Doc中的一条评论说不！