在PHP中后台在数据库中插入批量数据 [英] Insert Bulk Data in the DB in Background in PHP

问题描述

我有一个向用户发送批量消息的表单，单击提交按钮后，它将在发送之前将消息保存在数据库中，但是插入过程大约需要2分钟才能插入3000条记录，我如何减少插入时间或我如何在后台处理数据，以避免用户等待过程完成。我已经尝试了几个关于堆栈溢出的选项，但没有成功。我在共享主机上。
这是我的代码

I have a form which sends bulk messages to customers, on clicking submit button, it saves the messages in the DB before sending, but the inserting process takes about 2 mins to insert 3000 records, How can I reduce the insertion time or how can I process the data in the background to avoid the user waiting for the process to complete. I have tried several options on stack overflow with no success. I am on a shared hosting. here is my code

<?php
if(isset($_POST['submit'])){
 $date = date("D, F d, Y h:i:sa");
 $phones = $_POST['recipient_phones']; //get phone numbers from textarea
 $phones = trim($phones,",\r\n\t\r\n/\s+/\0\x0B]/"); //do some regex
 $phones = multiexplode(array(","," ","\n","r\n",".","|",":"),$phones);//reformat and convert to array
 $sender = $_POST['sender_id']; //Get Sender ID input field
 $message = $_POST['message']; //Get Message input field
 $time = $_POST['sc_time']; //Get time input field



 ob_end_clean();
 ignore_user_abort();
 ob_start();
 header("Connection: close");
 // echo json_encode($out);
 header("Content-Length: " . ob_get_length());
 ob_end_flush();
 flush();



foreach($phones as $phone){

      $data = array("sender" => "$sender","phone" => "$phone", "message" => "$message", "user_id" => "$user_id","time_submitted" => "$date");
   
         $qry = Insert('crbsms_queue',$data);
  
   
   $_SESSION['msg']="10";

  echo "<script>location.href='$url?success=yes';</script>";
   exit
}



     # Insert Data 
    function Insert($table, $data){
    global $mysqli;
    //print_r($data);

    $fields = array_keys( $data );  
    $values = array_map( array($mysqli, 'real_escape_string'), array_values( $data ) );
    
   //echo "INSERT INTO $table(".implode(",",$fields).") VALUES ('".implode("','", $values )."');";
   //exit;  
    mysqli_query($mysqli, "INSERT INTO $table(".implode(",",$fields).") VALUES ('".implode("','", $values )."');") or die( mysqli_error($mysqli) );

}

推荐答案

插入3000行数不多，如果操作正确，应该不会花费太多时间。您必须记住，您应该始终使用准备好的语句。您可以使用不同的数据多次执行同一条语句。当您将整个内容包装在事务中时，它应该会很快执行。

Inserting 3000 rows is not a lot and it should not take too much time if you do it properly. You must remember that you should always use prepared statements. You can execute the same statement multiple times with different data. When you wrap the whole thing in a transaction it should be executed really fast.

// Start transaction
$mysqli->begin_transaction();

// prepared statement prepared once and executed multiple times
$insertStatement = $mysqli->prepare('INSERT INTO crbsms_queue(sender, phone, message, user_id, time_submitted) VALUES(?,?,?,?,?)');
$insertStatement->bind_param('sssss', $sender, $phone, $message, $user_id, $date);
foreach ($phones as $phone) {
    $insertStatement->execute();
}

// Save and end transaction
$mysqli->commit();

如果这不能改善性能，则说明您在其他地方遇到了问题。您需要分析和调试应用程序以查找问题的根源。

If this doesn't improve the performance then it means you have a problem somewhere else. You need to profile and debug your application to find where the issue comes from.

侧面说明：记住要启用mysqli错误报告，否则您的事务可能无法正常运行。

Side note: Remember to enable mysqli error reporting, otherwise your transaction might not behave properly.

这篇关于在PHP中后台在数据库中插入批量数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！