PHP + MySQL 事务示例

PHP + MySQL transactions examples

提问by good_evening

I really haven't found normal example of PHP file where MySQL transactions are being used. Can you show me simple example of that?

我真的没有找到使用 MySQL 事务的 PHP 文件的正常示例。你能告诉我一个简单的例子吗？

And one more question. I've already done a lot of programming and didn't use transactions. Can I put a PHP function or something in header.phpthat if one mysql_queryfails, then the others fail too?

还有一个问题。我已经做了很多编程并且没有使用事务。我可以在header.php其中放置一个 PHP 函数或其他东西，如果一个mysql_query失败，那么其他的也会失败吗？

---

---

I think I have figured it out, is it right?:

我想我已经想通了，对吗？：

mysql_query("SET AUTOCOMMIT=0");
mysql_query("START TRANSACTION");

$a1 = mysql_query("INSERT INTO rarara (l_id) VALUES('1')");
$a2 = mysql_query("INSERT INTO rarara (l_id) VALUES('2')");

if ($a1 and $a2) {
    mysql_query("COMMIT");
} else {        
    mysql_query("ROLLBACK");
}

回答by Pascal MARTIN

The idea I generally use when working with transactions looks like this (semi-pseudo-code):

我在处理事务时通常使用的想法是这样的（半伪代码）：

try {
    // First of all, let's begin a transaction
    $db->beginTransaction();

    // A set of queries; if one fails, an exception should be thrown
    $db->query('first query');
    $db->query('second query');
    $db->query('third query');

    // If we arrive here, it means that no exception was thrown
    // i.e. no query has failed, and we can commit the transaction
    $db->commit();
} catch (Exception $e) {
    // An exception has been thrown
    // We must rollback the transaction
    $db->rollback();
}

Note that, with this idea, if a query fails, an Exception must be thrown:

请注意，根据这个想法，如果查询失败，则必须抛出异常：

• PDO can do that, depending on how you configure it
  • See PDO::setAttribute
  • and PDO::ATTR_ERRMODEand PDO::ERRMODE_EXCEPTION
• else, with some other API, you might have to test the result of the function used to execute a query, and throw an exception yourself.

• PDO 可以做到这一点，这取决于您如何配置它
  • 看 PDO::setAttribute
  • 并PDO::ATTR_ERRMODE与PDO::ERRMODE_EXCEPTION
• 否则，使用其他一些 API，您可能必须测试用于执行查询的函数的结果，并自己抛出异常。

Unfortunately, there is no magic involved. You cannot just put an instruction somewhere and have transactions done automatically: you still have to specific which group of queries must be executed in a transaction.

不幸的是，这里没有魔法。您不能只是在某处放置一条指令并自动完成事务：您仍然必须指定必须在事务中执行哪些查询组。

For example, quite often you'll have a couple of queries before the transaction (before the begin)and another couple of queries after the transaction (after either commitor rollback)and you'll want those queries executed no matter what happened (or not)in the transaction.

例如，很多时候你有一对夫妇的查询交易前（前begin）和交易后，另一对夫妇的查询（后两种commit或rollback），你会想这些查询执行的，不管发生了什么（或不）在交易。

回答by good_evening

I think I have figured it out, is it right?:

我想我已经想通了，对吗？：

mysql_query("START TRANSACTION");

$a1 = mysql_query("INSERT INTO rarara (l_id) VALUES('1')");
$a2 = mysql_query("INSERT INTO rarara (l_id) VALUES('2')");

if ($a1 and $a2) {
    mysql_query("COMMIT");
} else {        
    mysql_query("ROLLBACK");
}

回答by Gedzberg Alex

<?php

// trans.php
function begin(){
    mysql_query("BEGIN");
}

function commit(){
    mysql_query("COMMIT");
}

function rollback(){
    mysql_query("ROLLBACK");
}

mysql_connect("localhost","Dude1", "SuperSecret") or die(mysql_error());

mysql_select_db("bedrock") or die(mysql_error());

$query = "INSERT INTO employee (ssn,name,phone) values ('123-45-6789','Matt','1-800-555-1212')";

begin(); // transaction begins

$result = mysql_query($query);

if(!$result){
    rollback(); // transaction rolls back
    echo "transaction rolled back";
    exit;
}else{
    commit(); // transaction is committed
    echo "Database transaction was successful";
}

?>

回答by EleventyOne

As this is the first result on google for "php mysql transaction", I thought I'd add an answer that explicitly demonstrates how to do this with mysqli (as the original author wanted examples). Here's a simplified example of transactions with PHP/mysqli:

由于这是 google 上“php mysql 事务”的第一个结果，我想我会添加一个答案，明确演示如何使用 mysqli 执行此操作（如原作者想要的示例）。这是使用 PHP/mysqli 进行事务的简化示例：

// let's pretend that a user wants to create a new "group". we will do so
// while at the same time creating a "membership" for the group which
// consists solely of the user themselves (at first). accordingly, the group
// and membership records should be created together, or not at all.
// this sounds like a job for: TRANSACTIONS! (*cue music*)

$group_name = "The Thursday Thumpers";
$member_name = "EleventyOne";
$conn = new mysqli($db_host,$db_user,$db_passwd,$db_name); // error-check this

// note: this is meant for InnoDB tables. won't work with MyISAM tables.

try {

    $conn->autocommit(FALSE); // i.e., start transaction

    // assume that the TABLE groups has an auto_increment id field
    $query = "INSERT INTO groups (name) ";
    $query .= "VALUES ('$group_name')";
    $result = $conn->query($query);
    if ( !$result ) {
        $result->free();
        throw new Exception($conn->error);
    }

    $group_id = $conn->insert_id; // last auto_inc id from *this* connection

    $query = "INSERT INTO group_membership (group_id,name) ";
    $query .= "VALUES ('$group_id','$member_name')";
    $result = $conn->query($query);
    if ( !$result ) {
        $result->free();
        throw new Exception($conn->error);
    }

    // our SQL queries have been successful. commit them
    // and go back to non-transaction mode.

    $conn->commit();
    $conn->autocommit(TRUE); // i.e., end transaction
}
catch ( Exception $e ) {

    // before rolling back the transaction, you'd want
    // to make sure that the exception was db-related
    $conn->rollback(); 
    $conn->autocommit(TRUE); // i.e., end transaction   
}

Also, keep in mind that PHP 5.5 has a new method mysqli::begin_transaction. However, this has not been documented yet by the PHP team, and I'm still stuck in PHP 5.3, so I can't comment on it.

另外，请记住 PHP 5.5 有一个新方法mysqli::begin_transaction。但是，PHP 团队尚未对此进行记录，而且我仍停留在 PHP 5.3 中，因此无法对此发表评论。

回答by dinesh

Please check which storage engine you are using. If it is MyISAM, then Transaction('COMMIT','ROLLBACK')will not be supported because only the InnoDB storage engine, not MyISAM, supports transactions.

请检查您使用的是哪个存储引擎。如果是 MyISAM，Transaction('COMMIT','ROLLBACK')则不支持，因为只有 InnoDB 存储引擎支持事务，而不是 MyISAM。

回答by Danila Piatov

When using PDO connection:

使用 PDO 连接时：

$pdo = new PDO('mysql:host=localhost;dbname=mydb;charset=utf8', $user, $pass, [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, // this is important
]);

I often use the following code for transaction management:

我经常使用以下代码进行事务管理：

function transaction(Closure $callback)
{
    global $pdo; // let's assume our PDO connection is in a global var

    // start the transaction outside of the try block, because
    // you don't want to rollback a transaction that failed to start
    $pdo->beginTransaction(); 
    try
    {
        $callback();
        $pdo->commit(); 
    }
    catch (Exception $e) // it's better to replace this with Throwable on PHP 7+
    {
        $pdo->rollBack();
        throw $e; // we still have to complain about the exception
    }
}

Usage example:

用法示例：

transaction(function()
{
    global $pdo;

    $pdo->query('first query');
    $pdo->query('second query');
    $pdo->query('third query');
});

This way the transaction-management code is not duplicated across the project. Which is a good thing, because, judging from other PDO-ralated answers in this thread, it's easy to make mistakes in it. The most common ones being forgetting to rethrow the exception and starting the transaction inside the tryblock.

这样，事务管理代码就不会在整个项目中重复。这是一件好事，因为从该线程中其他与 PDO 相关的答案来看，很容易出错。最常见的是忘记重新抛出异常并在try块内启动事务。

回答by Marco

I made a function to get a vector of queries and do a transaction, maybe someone will find out it useful:

我做了一个函数来获取查询向量并进行交易，也许有人会发现它很有用：

function transaction ($con, $Q){
        mysqli_query($con, "START TRANSACTION");

        for ($i = 0; $i < count ($Q); $i++){
            if (!mysqli_query ($con, $Q[$i])){
                echo 'Error! Info: <' . mysqli_error ($con) . '> Query: <' . $Q[$i] . '>';
                break;
            }   
        }

        if ($i == count ($Q)){
            mysqli_query($con, "COMMIT");
            return 1;
        }
        else {
            mysqli_query($con, "ROLLBACK");
            return 0;
        }
    }

回答by nodeffect

I had this, but not sure if this is correct. Could try this out also.

我有这个，但不确定这是否正确。也可以试试这个。

mysql_query("START TRANSACTION");
$flag = true;
$query = "INSERT INTO testing (myid) VALUES ('test')";

$query2 = "INSERT INTO testing2 (myid2) VALUES ('test2')";

$result = mysql_query($query) or trigger_error(mysql_error(), E_USER_ERROR);
if (!$result) {
$flag = false;
}

$result = mysql_query($query2) or trigger_error(mysql_error(), E_USER_ERROR);
if (!$result) {
$flag = false;
}

if ($flag) {
mysql_query("COMMIT");
} else {        
mysql_query("ROLLBACK");
}

Idea from here: http://www.phpknowhow.com/mysql/transactions/

来自这里的想法：http: //www.phpknowhow.com/mysql/transactions/

回答by nodeffect

One more procedural style example with mysqli_multi_query, assumes $queryis filled with semicolon-separated statements.

另一个带有mysqli_multi_query, 的程序样式示例假设$query填充了以分号分隔的语句。

mysqli_begin_transaction ($link);

for (mysqli_multi_query ($link, $query);
    mysqli_more_results ($link);
    mysqli_next_result ($link) );

! mysqli_errno ($link) ?
    mysqli_commit ($link) : mysqli_rollback ($link);