PHP + MySQL交易示例

Myth丶恋晨 2022-11-27 03:16 39阅读 0赞

本文翻译自：[PHP + MySQL transactions examples][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交易示例][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][mysqli_begin_transaction] .* **另外，请记住，PHP 5.5具有新方法[mysqli :: begin\_transaction][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]* **这里的想法： [http][http_www.phpknowhow.com_mysql_transactions] : [//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:* **我做了一个函数来获取查询向量并进行事务处理，也许有人会发现它有用：**

[PHP _ MySQL transactions examples]: https://oldbug.net/q/bmxf/PHP-MySQL-transactions-examples
[https_stackoom.com_question_bmxf_PHP-MySQL]: https://stackoom.com/question/bmxf/PHP-MySQL%E4%BA%A4%E6%98%93%E7%A4%BA%E4%BE%8B
[mysqli_begin_transaction]: http://www.php.net/manual/en/mysqli.begin-transaction.php
[http_www.phpknowhow.com_mysql_transactions]: http://www.phpknowhow.com/mysql/transactions/