如何导入巨大的CSV文件与200,00行到MySQL（异步和快速）？ [英] How to import huge CSV file with 200,00 rows to MySQL (asynchronous and fast)?
问题描述
我必须写一个PHP脚本，它将从给定的CSV文件导入数据到MySQL数据库。给定的CSV文件最多可包含200,000行。
我尝试以下但是出现问题：
I have to write a PHP script that will import data from a given CSV file into MySQL database. The given CSV file can contain up to 200,000 rows. I tried the following but problems arise :
- LOAD DATA LOCAL INFILE：我不能使用LOAD DATA LOCAL INFILE语句，在上传行之前先做一些验证，我们的DB管理员不希望我使用该语句，我不知道为什么。
- FOR LOOP：逐行插入内部FOR循环将花费太多时间导致连接超时。
现在，我想通过将CSV文件拆分为较小的块，然后异步插入它们。我已经完成了CSV的拆分，但我目前不知道如何异步插入到我的数据库，以快速和安全的方式。但我听说我将在这里使用Ajax。
Now, I am thinking of a solution by splitting the CSV file into smaller chunks, then inserting them asynchronously. I am already done with the splitting of CSV, but I currently have no idea how to asynchronously insert into my database for quick and safe way. But I heard that I will be using Ajax here.
您可以推荐任何解决方案？非常感谢！
Any solution you can recommend? Thanks a lot in advance!
推荐答案
感谢解答这个问题的每个人。我发现了一个解决方案！
只是想分享它，万一有人需要创建一个PHP脚本，将一个巨大的CSV文件导入到MySQL数据库（异步，快速！）我已经测试了我的代码与400,000行，导入完成在几秒钟内。
我相信它会处理较大的文件，你只需要修改最大上传文件大小。
Thanks to everyone who gave answers to this question. I have discovered a solution! Just wanted to share it, in case someone needs to create a PHP script that will import a huge CSV file into MySQL database (asynchronously and fast!) I have tested my code with 400,000 rows and the importing is done in seconds. I believe it would work with larger files, you just have to modify maximum upload file size.
在这个例子中，我将导入一个CSV文件
In this example, I will be importing a CSV file that contains two columns (name, contact_number) into a MySQL DB that contains the same columns.
您的CSV文件应如下所示：
Your CSV file should look like this :
Ana，0906123489
Ana, 0906123489
John，0908989199
John, 0908989199
Peter，0908298392
Peter, 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文件如下：
Second, I have 4 PHP files. All you have to do is place this into a single folder. PHP files are as follows :
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的想法）
That's it! You already have a pure PHP script that can import multiple number of rows in seconds! :) (Thanks to my partner who taught and gave me an idea on how to use ajax)
这篇关于如何导入巨大的CSV文件与200,00行到MySQL（异步和快速）？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
