doucu5836
2016-04-13 14:04
浏览 69
已采纳

如何使用PHP的预处理语句与MySQL事务执行多个查询?

I need to execute 2 or more not identical queries (INSERT's in this example.) using PHP's prepared statements and MySQL's transactions. So if I have a 2 INSERT statements, I want both of them executed, or none of them.

I want to traslate this example MySQL transaction to a PHP code:

START TRANSACTION;
     INSERT INTO `file` (`name`, `mime_type`, `size`, `comment`) VALUES ('file.png', 'image/png', '1024', 'My comment');
     INSERT INTO `complementary_file` (`file_id`, `user_id`) VALUES (LAST_INSERT_ID(), '1');
COMMIT;  

PHP code I'm working on:

mysqli_autocommit($connection, FALSE);
if ($stmtFile = mysqli_prepare($connection, "INSERT INTO `file` (`name`, `mime_type`, `size`, `comment`) VALUES (?, ?, ?, ?)")) {
    mysqli_stmt_bind_param($stmtFile, 'ssis', $name, $mime_type, $size, $comment);
    if (mysqli_stmt_execute($stmtFile)) {
        if ($stmtComplementaryFile = mysqli_prepare($connection, "INSERT INTO `complementary_file` (`file_id`, `user_id`) VALUES (?, ?)")) {
            mysqli_stmt_bind_param($stmtComplementaryFile, 'ii', mysqli_insert_id($connection), $user_id);
            mysqli_stmt_execute($stmtComplementaryFile);
        }
    } else {
        mysqli_rollback($connection);
    }
}
mysqli_commit($connection);  

PHP code above works but what if I have more critical statements to execute? Is there are good way to execute statements with PHP's prepared statemens and transactions at the same time?

Please note that for $stmtComplementaryFile I must have a mysqli_insert_id() value. Also please note that I am not using PDO with this code — I appreciate if suggestions will be MySQLi. Thanks.

图片转代码服务由CSDN问答提供 功能建议

我需要执行2个或更多不完全相同的查询(本例中为 INSERT ) 。)使用PHP的预处理语句和MySQL的事务。 所以,如果我有一个2 INSERT 语句,我希望它们都被执行,或者都不执行。

我想将这个示例MySQL事务转换为PHP 代码:

  START TRANSACTION; 
 INSERT INTO`file`(`name`,`mime_type`,`size`,`comment`)VALUES('file.png'  ,'image / png','1024','我的评论'); 
 INSERT INTO`increper_file`(`file_id`,`user_id`)VALUES(LAST_INSERT_ID(),'1'); 
COMMIT;  
   
 
 

我正在处理的PHP代码:

  mysqli_autocommit($ connection,FALSE); 
if(  $ stmtFile = mysqli_prepare($ connection,“INSERT INTO`file`(`name`,`mime_type`,`size`,`comment`)VALUES(?,?,?,?)”)){
 mysqli_stmt_bind_param($  stmtFile,'ssis',$ name,$ mime_type,$ size,$ comment); 
 if(mysqli_stmt_execute($ stmtFile)){
 if($ stmtComplementaryFile = mysqli_prepare($ connection,“INSERT INTO`inmplementary_file`(`  file_id`,`user_id`)VALUES(?,?)“)){
 mysqli_stmt_bind_param($ stmtComplementaryFile,'ii',mysqli_insert_id($ connection),$ user_id); 
 \ mysqli_stmt_execute($ stmtComplementaryFile); 
} \  n} else {
 mysqli_rollback($ connection); 
} 
} 
mysqli_commit($ connection);  
   
 
 

上面的PHP代码可以工作,但如果我有更多的关键语句要执行呢? 是否有很好的方法可以同时使用PHP准备好的statemens 事务来执行语句?

请注意,对于 $ stmtComplementaryFile ,我必须有 mysqli_insert_id()值。 另请注意,我没有在此代码中使用PDO - 如果建议是MySQLi,我将不胜感激。 谢谢。

  • 写回答
  • 好问题 提建议
  • 关注问题
  • 收藏
  • 邀请回答

1条回答 默认 最新

  • dongweihuai5601 2016-04-13 15:06
    已采纳

    The following SQL:

    START TRANSACTION;
         INSERT INTO `file` (`name`, `mime_type`, `size`, `comment`) VALUES ('file.png', 'image/png', '1024', 'My comment');
         INSERT INTO `complementary_file` (`file_id`, `user_id`) VALUES (LAST_INSERT_ID(), '1');
    COMMIT;  
    

    Can be converted to PHP (with prepared statements) as follows:

    mysqli_begin_transaction($connection, MYSQLI_TRANS_START_READ_WRITE);
    if ($stmtFile = mysqli_prepare($connection, "INSERT INTO `file` (`name`, `mime_type`, `size`, `comment`) VALUES (?, ?, ?, ?)")) {
        mysqli_stmt_bind_param($stmtFile, 'ssis', $name, $mime_type, $size, $comment);
        if (mysqli_stmt_execute($stmtFile)) {
            if ($stmtComplementaryFile = mysqli_prepare($connection, "INSERT INTO `complementary_file` (`file_id`, `user_id`) VALUES (?, ?)")) {
                mysqli_stmt_bind_param($stmtComplementaryFile, 'ii', mysqli_insert_id($connection), $user_id);
                mysqli_stmt_execute($stmtComplementaryFile);
            }
        } else {
            mysqli_rollback($connection);
        }
    }
    mysqli_commit($connection);  
    

    In general these two statements are equivalent, however it's a good idea to know what transactions are and what transactions are not.

    Transactions are a mechanism to ensure that bulk operations are executed atomically and their results are reflected in the database only if ALL of them succeed. It ensures atomicity of operations.

    Transactions are not a mechanism to implement a mutual exclusion lock on the tables. If a table needs to be locked then the LOCK TABLES [READ|WRITE] needs to be used. Equivalently in PHP this is achieved by doing a:

    mysqli_query($connection, "LOCK TABLES tableName as TableAlias [read|write]");
    

    followed by

    mysqli_query($connection, "UNLOCK TABLES"); 
    
    已采纳该答案
    评论
    解决 无用
    打赏 举报

相关推荐 更多相似问题