PHP + MySQL交易示例

本文翻译自：PHP + MySQL transactions examples

I really haven't found normal example of PHP file where MySQL transactions are being used. 我真的还没有找到使用MySQL事务的PHP文件的正常示例。 Can you show me simple example of that? 你能给我简单的例子吗？

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.php that if one mysql_query fails, then the others fail too? 我可以将PHP函数或其他内容放入header.php ，如果一个mysql_query失败，那么其他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");
}

---

#1楼

参考：https://stackoom.com/question/bmxf/PHP-MySQL交易示例

---

#2楼

<?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";
}

?>

---

#3楼

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支持事务。

---

#4楼

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). 由于这是Google上针对“ php mysql transaction”的第一个结果，我想我将添加一个答案，该答案明确说明如何使用mysqli进行此操作（作为原始作者的示例）。 Here's a simplified example of transactions with PHP/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 . 另外，请记住，PHP 5.5具有新方法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团队尚未对此进行记录，并且我仍然停留在PHP 5.3中，因此无法对此发表评论。

---

#5楼

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/

---

#6楼

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;
        }
    }