如何提高php中csv数据在数据库中的插入速度？ [英] How to improve the speed of insertion of the csv data in a database in php?
问题描述
我写了这段代码，它允许读取一个文件夹，这取决于在不同表中插入的csv文件数据的文件名。处理完文件后，将其移动到另一个文件夹。
我的代码运行良好，但是第二个csv文件的长度超过8万行，并且需要花费几个小时才能集成到我的数据库中。
I wrote this code that allows to read a folder, depending on the file name is inserted in a different table the data of the csv file. Once the file is processed it is moved to another folder. My code works well but the second csv file is more than 80 000 lines long and it takes several hours to be integrated in my database.
如何提高以下文件的性能我的代码？我尝试了'LOAD DATA LOCAL'，但没有成功...
How can I improve the performance of my code? I tried 'LOAD DATA LOCAL' but without success...
<?php
include("../connexion.php");
ini_set('max_execution_time', 54000);
$timestamp= date("y-m-d H:i");
$dir = 'D:/xampp/htdocs/retail_BI/test/';
$allFiles = scandir($dir);
$dest = 'D:/xampp/htdocs/retail_BI/test/files/';
foreach($allFiles as $file) {
if (!in_array($file,array(".","..")))
{
$file = $dir.$file;
$filename = basename( $file );
if ( strpos( $filename, 'BI1_' ) === 0 ) {
if (($handle = fopen("$filename", "r")) !== false) {
//To remove BOM in the fist cell
fseek($handle, 3);
while (($data = fgetcsv($handle, 9000000, ";")) !== false) {
if (empty(array_filter($data))) {
echo "not good";
continue;
}
$date = DateTime::createFromFormat('d/m/Y H:i:s A', $data[2]);
if ($date === false) {
break;
}
$date1 = $date->format('Y-m-d'); // 2020-07-07
$date2 = $date->format('Hi A'); // 1247 AM
//database entry
$query = "insert into dbo.Y2_Sales (storenumber, storename, date, time, TransRef, stylecode, color, size, quantity, unit_price, SalesExGST, cost, currency)
values('$data[0]', '$data[1]','$date1','$date2','$data[3]','$data[4]','$data[5]','$data[6]','$data[7]', '$data[8]','$data[9]','$data[10]','$data[11]')";
$stmt = $conn->query( $query );
if (!$stmt) { echo $conn->error;}
}
}
fclose($handle);
//Moving the file to another folder
if(!rename($file, $dest . $filename)) {
echo "error copy";
}
} else if ( strpos( $filename, 'BI2_' ) === 0 ) {
if (($handle = fopen("$filename", "r")) !== false) {
//To remove BOM in the fist cell
fseek($handle, 3);
while (($data = fgetcsv($handle, 9000000, ";")) !== false) {
// print_r($data);
if (empty(array_filter($data))) {
continue;
}
//database entry
$query = "insert into dbo.Y2_Inventory (storenumber, stylecode, colour, size, units, timestamp)
values('$data[0]', '$data[1]','$data[2]','$data[3]','$data[4]', '$timestamp')";
$stmt = $conn->query( $query );
if (!$stmt) { echo $conn->error;}
// $query = "LOAD DATA LOCAL INFILE '$filename' INTO TABLE dbo.Y2_Inventory FIELDS TERMINATED BY ';' LINES TERMINATED BY '\n' (storenumber, stylecode, colour, size, units, timestamp)";
// $stmt = $conn->query( $query );
}
}
fclose($handle);
//Moving the file to another folder
if(!rename($file, $dest . $filename)) {
echo "error copy";
}
}
}
}
echo "good !";
?>
推荐答案
不是将数据插入数据库的每一行，而是批量插入。
Instead of inserting data into database for every row, try inserting in batches.
您总是可以进行批量插入，该批量插入可以使用n（使用1000）个条目并将其插入到表中。
You can always do a bulk insert, that can take n(use 1000) number of entries and insert it into the table.
https://www.mysqltutorial.org/mysql-insert-multiple-rows /
这将减少数据库调用，从而减少总时间。
This will result in reduction of the DB calls, thereby reducing the overall time.
对于8万个条目，有一个
And for 80k entries there is a possibility that you might exceed the memory limit too.
您可以使用php中的生成器来克服这种情况。
https：// medium .com / @ aashish.gaba097 / database-seeding-with-large-files-in-laravel-be5b2aceaa0b
You can overcome that using generators in php. https://medium.com/@aashish.gaba097/database-seeding-with-large-files-in-laravel-be5b2aceaa0b
尽管这是在Laravel中，但是读取的代码来自csv的代码是独立的（使用生成器的代码），可以在此处使用该逻辑。
Although, this is in Laravel, but the code that reads from csv is independent (the one that uses generator) and the logic can be used here.
这篇关于如何提高php中csv数据在数据库中的插入速度？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
