如何导入巨大的CSV文件与200000行MySQL(异步和快速) - How to import huge CSV file with 200,00 rows to MySQL (asynchronous and fast)?

我必须写一个PHP脚本，将数据从给定的CSV文件导入MySQL数据库。给定的CSV文件最多可以包含200,000行。我尝试了以下操作，但出现了问题:

LOAD DATA LOCAL INFILE:我不能使用LOAD DATA LOCAL INFILE语句，因为我想在上传行之前先做一些验证，同时，我们的DB管理员不希望我使用该语句，我不知道为什么。
FOR循环:在FOR循环中逐行插入将花费太多时间，导致连接超时。

现在，我正在考虑将CSV文件分割成更小的块，然后异步插入它们的解决方案。我已经完成了分割CSV，但我目前不知道如何异步插入到我的数据库快速和安全的方式。但是我听说我将在这里使用Ajax。

你有什么建议吗?提前感谢!

感谢所有回答这个问题的人。我发现了一个解决办法!只是想分享它，以防有人需要创建一个PHP脚本，将一个巨大的CSV文件导入MySQL数据库(异步和快速!)我用40万行测试了我的代码，导入在几秒钟内完成。我相信它可以工作在更大的文件，你只需要修改最大上传文件大小。

在本例中，我将把包含两列(name, contact_number)的CSV文件导入到包含相同列的MySQL DB中。

你的CSV文件应该是这样的:

安娜,0906123489

约翰,0908989199

彼得,0908298392

…

那么，这就是解决方案。

首先，创建表

CREATE TABLE `testdb`.`table_test`
( `id` INT NOT NULL AUTO_INCREMENT ,
`name` VARCHAR(100) NOT NULL ,
`contact_number` VARCHAR(100) NOT NULL ,
PRIMARY KEY (`id`)) ENGINE = InnoDB;

第二，我有4个PHP文件。你所要做的就是把它放到一个文件夹里。PHP文件如下:

index . php

<form action="upload.php" method="post" enctype="multipart/form-data">
<input type="file" name="csv" value="" />
<input type="submit" name="submit" value="Save" /></form>

connect.php

<?php
//modify your connections here
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "testDB";
$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 
?>

senddata.php

<?php
include('connect.php');
$data = $_POST['file'];
$handle = fopen($data, "r");
$test = file_get_contents($data);
if ($handle) {
    $counter = 0;
    //instead of executing query one by one,
    //let us prepare 1 SQL query that will insert all values from the batch
    $sql ="INSERT INTO table_test(name,contact_number) VALUES ";
    while (($line = fgets($handle)) !== false) {
      $sql .= "($line),";
      $counter++;
    }
    $sql = substr($sql, 0, strlen($sql) - 1);
     if ($conn->query($sql) === TRUE) {
    } else {
     }
    fclose($handle);
} else {  
} 
//unlink CSV file once already imported to DB to clear directory
unlink($data);
?>

upload.php

<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/1.11.1/jquery.js"></script>
<script>
//Declaration of function that will insert data into database
 function senddata(filename){
        var file = filename;
        $.ajax({
            type: "POST",
            url: "senddata.php",
            data: {file},
            async: true,
            success: function(html){
                $("#result").html(html);
            }
        })
        }
 </script>
<?php
$csv = array();
$batchsize = 1000; //split huge CSV file by 1,000, you can modify this based on your needs
if($_FILES['csv']['error'] == 0){
    $name = $_FILES['csv']['name'];
    $ext = strtolower(end(explode('.', $_FILES['csv']['name'])));
    $tmpName = $_FILES['csv']['tmp_name'];
    if($ext === 'csv'){ //check if uploaded file is of CSV format
        if(($handle = fopen($tmpName, 'r')) !== FALSE) {
            set_time_limit(0);
            $row = 0;
            while(($data = fgetcsv($handle)) !== FALSE) {
                $col_count = count($data);
                //splitting of CSV file :
                if ($row % $batchsize == 0):
                    $file = fopen("minpoints$row.csv","w");
                endif;
                $csv[$row]['col1'] = $data[0];
                $csv[$row]['col2'] = $data[1];
                $min = $data[0];
                $points = $data[1];
                $json = "'$min', '$points'";
                fwrite($file,$json.PHP_EOL);
                //sending the splitted CSV files, batch by batch...
                if ($row % $batchsize == 0):
                    echo "<script> senddata('minpoints$row.csv'); </script>";
                endif;
                $row++; 
            }
            fclose($file);
            fclose($handle);
        }
    }
    else
    {
        echo "Only CSV files are allowed.";
    }
    //alert once done.
    echo "<script> alert('CSV imported!') </script>";
}
?>

就是这样!您已经有了一个纯PHP脚本，可以在几秒钟内导入多个行!：）(感谢我的合作伙伴，他教我如何使用ajax并给了我一个想法)

主要的慢速来自于将每一行作为它自己的请求发送。我建议以mysqldump --opt使用的相同格式发送每1000或500行查询，因此以

的方式构建一个长字符串

 insert into datatable (name, prename, commen) 
   values ('wurst', 'hans', 'someone')
   , ('bush', 'george', 'otherone')
   , ...
   ;

你应该检查你的行允许有多长，或者如果MySQL- Server在你的控制下，你可以扩展最大查询长度。

如果这仍然太长(我的意思是200K根本不算多)，那么您可以尝试改进csv-reading。

将这些数据块拆分需要一些工作，但是您可以为此编写一个小块类，因此添加行会变得更容易一些。

这个类的用法如下

$chunk->prepare("insert into datatable (name, prename, comment) values");
$chunk->setSize(1000);
foreach ($row...){
   if($query = $chunk->addRow(...)){
       callUpdate($query);
   }
}
if($query = $chunk->clear()){
  callUpdate($query);
}

我仍然会在临时表中使用LOAD DATA LOCAL INFILE，并使用MySQL对DB中的所有数据进行验证，过滤，清理等，然后用准备好的记录填充目标表

可以在PHP中使用fgetcsv()。

下面是一个例子:

// Open the file with PHP
$oFile = fopen('PATH_TO_FILE', 'w');
// Get the csv content
$aCsvContent = fgetcsv($oFile);
// Browse your csv line per line
foreach($aCsvContent as $aRow){
    $sReqInsertData = ' INSERT
                        INTO
                            TABLENAME
                        SET
                            FIELD1 = "'.$aRow[0].'",
                            FIELD2 = "'.$aRow[1].'",
                            FIELD3 = "'.$aRow[2].'",
                            FIELD4 = "'.$aRow[3].'",
                            FIELD5 = "'.$aRow[4].'",
                            FIELD6 = "'.$aRow[5].'",
                            FIELD7 = "'.$aRow[6].'",
                            FIELD8 = "'.$aRow[7].'"';
    // Execute your sql with mysqli_query or something like this
    mysqli_query($sReqInsertData);
}
// Close you file
fclose($oFile);