Как импортировать огромный файл CSV с 200,00 строк в MySQL (асинхронный и быстрый)?

Я должен написать PHP-скрипт, который будет импортировать данные из данного CSV-файла в базу данных MySQL. Данный CSV-файл может содержать до 200 000 строк. Я пробовал следующее, но возникают проблемы:

LOAD DATA LOCAL INFILE: Я не могу использовать инструкцию LOAD DATA LOCAL INFILE, потому что сначала я хотел сделать некоторые проверки, прежде чем загружать строки, также наш администратор базы данных не хочет, чтобы я использовал этот оператор, и я не знаю почему.
FOR LOOP: Вставка строки за строкой внутри цикла FOR займет слишком много времени, что приведет к таймауту соединения.

Теперь я думаю о решении, разбивая CSV-файл на более мелкие куски, а затем вставляя их асинхронно. Я уже закончил разделение CSV, но в настоящее время я понятия не имею, как асинхронно вставлять в мою базу данных для быстрого и безопасного использования. Но я слышал, что я буду использовать Ajax здесь.

Любое решение, которое вы можете порекомендовать? Заранее большое спасибо!

Спасибо всем, кто дал ответы на этот вопрос. Я нашел решение! Просто хотел поделиться им, в случае, если кто-то должен создать PHP-скрипт, который будет импортировать огромный файл CSV в базу данных MySQL (асинхронно и быстро!). Я проверил свой код с 400 000 строк, а импорт выполняется за считанные секунды. Я считаю, что это будет работать с большими файлами, вам просто нужно изменить максимальный размер загружаемого файла.

В этом примере я буду импортировать CSV-файл, содержащий два столбца (name, contact_number) в базу данных MySQL, содержащую те же столбцы.

Ваш CSV-файл должен выглядеть так:

Ana, 0906123489

John, 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); ?> в <?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)

Основная медлительность исходит от отправки каждой отдельной строки, поскольку это собственный запрос. Я бы предложил отправить запрос каждые 1000 или 500 строк в том же формате, что и mysqldump --opt , поэтому постройте длинную строку в пути

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

Вы должны проверить, как долго ваши строки разрешены или если MySQL-сервер находится в вашем контроле, вы можете увеличить максимальную длину запроса.

Если это еще слишком долго (я имею в виду, что 200K совсем не так), вы можете попытаться улучшить чтение csv.

Это небольшая работа, разделяющая эти фрагменты, но для этого вы можете написать небольшой класс chunk, поэтому добавление строк становится немного проще.

Использование этого класса выглядело

 $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 для проверки, фильтрации, очистки и т. Д. Со всеми данными в БД, а затем заполнял таблицу назначения готовыми к записи.

Вы можете использовать fgetcsv () с PHP.

Вот пример:

 // 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);