PHP在后台向DB中插入批量数据 [英] 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,",
	
/s+/x0B]/"); //do some regex
 $phones = multiexplode(array(","," ","
","r
",".","|",":"),$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在后台向DB中插入批量数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！