试图通过 PHP 将 15k 行 CSV 导入 SQL，但我每次只导入 600-900 行

Question

这是我的第一篇文章，我为我的错误提前道歉......

我正在尝试通过 PHP 将一个 15k 行的 csv 文件导入到 SQL，但我每次只导入 600-900 行。

这是我的代码：

<?php

    if(isset($_POST["Import"]))

    {

     $filename=$_FILES["file"]["tmp_name"];    

     if($_FILES["file"]["size"] > 0)

      { 

        $file = fopen($filename, "r");

          while (($getData = fgetcsv($file, 0, ',', '"')) !== FALSE)

           {

             $sql = "INSERT into testimport() 

             values ('".$getData[0]."','".$getData[1]."','".$getData[2]."')";

              $result = mysqli_query($conn, $sql);

              if(!isset($result))

              {

                echo "<script type=\"text/javascript\">

                alert(\"Invalid File:Please Upload CSV File.\");

                window.location = \"readRecords.php\"

                </script>";    

              }

              else 

              {

                echo "<script type=\"text/javascript\">

                alert(\"CSV File has been successfully Imported.\");

                window.location = \"readRecords.php\"

                </script>";

              }

           }

        fclose($file);  

      }

    }

?>

非常感谢你的帮助！！乔治奥

Answer 1

在while循环开始之前，添加以下代码行：set_time_limit (300)它将执行查询的时间限制设置为 5 分钟（300 秒）

Answer 2

我结合了你的建议，现在这个过程完美无缺！我可以在 28 秒内导入 41 列的 15k 条记录，在 60 秒内导入 30k 条记录！惊人的！现在，我需要让它自动化，但那是另一个遗憾:-)这是我的最终代码：include('dbconnect.php');if(isset($_POST["Import"])){&nbsp; &nbsp; &nbsp;$filename=$_FILES["file"]["tmp_name"];&nbsp; &nbsp;&nbsp;&nbsp; &nbsp; &nbsp;if($_FILES["file"]["size"] > 0)&nbsp; &nbsp; &nbsp;{&nbsp; &nbsp; &nbsp; &nbsp; ini_set('auto_detect_line_endings',TRUE);&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; $file = fopen($filename, "r");&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; set_time_limit (45);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; while ($getData = fgetcsv($file, 0, ',', '"'))&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;{&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $sql = "INSERT INTO testimport&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; (&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; id_record,panel_number,machine_number,id_internal_code,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; id_number,tot_production,waiting_time,operation_length,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; avg_flow,max_flow,flow_15,flow_30,flow_60,flow_120,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; avg_temp,max_temp,avg_cond,max_cond,m_detach,m_input,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; m_quantity,m_input_nc,max_time,m_operation,m_sep_,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; a_no_flow,a_conductivity,a_low_prod,a_kick_off,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; a_temperature,prod_2min,low_flow_time,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 50char_graph_data,data_enter,data_start,data_end,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; data_exit,preparation_time,nr_session,perc_2min,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; perc_low_flow&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; )&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; VALUES&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;)";&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;$stmt = mysqli_prepare($conn, $sql);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;$stmt->bind_param&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;(&nbsp; &nbsp; "sssssssssssssssssssssssssssssssssssssssss",&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $getData[0],&nbsp; $getData[1],&nbsp; $getData[2],&nbsp; $getData[3],&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $getData[4],&nbsp; $getData[5],&nbsp; $getData[6],&nbsp; $getData[7],&nbsp;&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $getData[8],&nbsp; $getData[9],&nbsp; $getData[10], $getData[11],&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $getData[12], $getData[13], $getData[14], $getData[15],&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $getData[16], $getData[17], $getData[18], $getData[19],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $getData[20], $getData[21], $getData[22], $getData[23],&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $getData[24], $getData[25], $getData[26], $getData[27],&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $getData[28], $getData[29], $getData[30], $getData[31],&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $getData[32], $getData[33], $getData[34], $getData[35],&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $getData[36], $getData[37], $getData[38], $getData[39],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $getData[40]&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;$sql = "INSERT into testimport()&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;values&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; )";&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;$stmt->execute();&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;}&nbsp; &nbsp; &nbsp; &nbsp; fclose($file);&nbsp;&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; mysqli_close($conn);&nbsp; &nbsp; &nbsp;}}

Answer 3

在调用之前添加以下行fopenini_set('auto_detect_line_endings',TRUE);请参阅 gmail.com 上的 james dot ellis 对https://www.php.net/manual/en/function.fgetcsv.php的评论您的行尾可能由不同的操作系统以不同的方式编码。例如，Windows 将是\r\n，而基于 Unix 的系统将使用\n这可能导致一些行被连接在一起。例如，Say,Something,Im<NEW_LINE_CHAR>Giving,Up,On<NEW_LINE_CHAR>如果 fgetcsv 正确捕获结束行字符，它将生成 $getData 的单独迭代，如下所示：迭代 1：[0] => Say[1] => Something[2] => Im迭代 2：[0] => Giving[1] => Up[2] => On而如果它没有正确检测到结束字符，您最终会把两行连接在一起。然后该行将被解析为包含 6 个元素的 $getData 数组。最后三个将被脚本忽略，该脚本只将位置 0,1 和 2 写入 SQL 插入脚本。迭代 1：[0] => Say[1] => Something[2] => Im<NEW_LINE_CHAR>[3] => Giving (Ignored)[4] => Up (Ignored)[5] => On<NEW_LINE_CHAR>(Ignored)最后，您可能希望避免编写将未经验证的字符串附加到插入代码的 SQL 代码。它很危险，而且很容易受到注入攻击。强烈建议您考虑使用准备好的语句，它看起来像这样：&nbsp; &nbsp; $stmt = mysqli_prepare($sql);&nbsp; &nbsp; $stmt->bind_param($getData[0], $getData[1],$getData[2]);&nbsp; &nbsp; $sql = "INSERT into testimport() values (?,?,?)";有关详细信息，请参阅PHP：将表单中的值插入 MySQL。