Импорт больших csv в базу данных mysql

Вы изобретаете колесо. Просмотрите инструмент mysqlimport , который поставляется с MySQL. Это эффективный инструмент для импорта файлов данных CSV.

mysqlimport – это интерфейс командной строки для оператора LOAD DATA LOCAL INFILE SQL.

Либо должен работать на 10-20x быстрее, чем делать INSERT подряд за строкой.

Вероятно, ваша проблема заключается в том, что у вас есть autocommit (по умолчанию), поэтому MySQL выполняет новую транзакцию для каждой вставки. Вы должны отключить автокоммутацию с помощью SET autocommit=0; , Если вы можете переключиться на использование библиотеки mysqli (и вы должны, если это возможно), вы можете использовать mysqli::autocommit(false) чтобы отключить автообновление.

 $mysqli = new mysqli('localhost','db_user','my_password','mysql'); $mysqli->autocommit(false); $stmt=$mysqli->prepare("insert into cities (`postcode`, `city_name`, `city_slug`, `prov_name`, `prov_slug`, `prov_abbr`, `lat`, `lng`) values (?, ?, ?, ?, ?, ?, ?, ?);") $row = 1; if (($handle = fopen("postal_codes.csv", "r")) !== FALSE) { while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) { $num = count($data); $row++; for ($c=0; $c < $num; $c++) { $arr = explode('|', $data[$c]); $stmt->bind_param('ssssssdd', $arr[1], $arr[2], toAscii(arr[2]), $arr[3], toAscii($arr[3]), $arr[4], $arr[6], $arr[7]); $stmt->execute(); } } } $mysqli->commit(); fclose($handle); 

Будет намного быстрее использовать LOAD DATA, если вы можете

попробуйте сделать это в одном запросе.

Это может быть ограничено вашей конфигурацией my.cnf (mysql), хотя

 <?php $row = 1; $query = ("insert into cities "); if (($handle = fopen("postal_codes.csv", "r")) !== FALSE) { while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) { $num = count($data); $row++; for ($c=0; $c < $num; $c++) { $arr = explode('|', $data[$c]); $postcode = mysql_real_escape_string($arr[1]); $city_name = mysql_real_escape_string($arr[2]); $city_slug = mysql_real_escape_string(toAscii($city_name)); $prov_name = mysql_real_escape_string($arr[3]); $prov_slug = mysql_real_escape_string(toAscii($prov_name)); $prov_abbr = mysql_real_escape_string($arr[4]); $lat = mysql_real_escape_string($arr[6]); $lng = mysql_real_escape_string($arr[7]); $query .= "(`postcode`, `city_name`, `city_slug`, `prov_name`, `prov_slug`, `prov_abbr`, `lat`, `lng`) values ('$postcode', '$city_name', '$city_slug', '$prov_name', '$prov_slug', '$prov_abbr', '$lat', '$lng'),"; } } fclose($handle); } mysql_query(rtrim($query, ",")); 

если это не сработает, вы можете попробовать это (отключить автоматическую фиксацию)

 mysql_query("SET autocommit = 0"); $row = 1; if (($handle = fopen("postal_codes.csv", "r")) !== FALSE) { while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) { $num = count($data); $row++; for ($c=0; $c < $num; $c++) { $arr = explode('|', $data[$c]); $postcode = mysql_real_escape_string($arr[1]); $city_name = mysql_real_escape_string($arr[2]); $city_slug = mysql_real_escape_string(toAscii($city_name)); $prov_name = mysql_real_escape_string($arr[3]); $prov_slug = mysql_real_escape_string(toAscii($prov_name)); $prov_abbr = mysql_real_escape_string($arr[4]); $lat = mysql_real_escape_string($arr[6]); $lng = mysql_real_escape_string($arr[7]); mysql_query("insert into cities (`postcode`, `city_name`, `city_slug`, `prov_name`, `prov_slug`, `prov_abbr`, `lat`, `lng`) values ('$postcode', '$city_name', '$city_slug', '$prov_name', '$prov_slug', '$prov_abbr', '$lat', '$lng')") or die(mysql_error()); } } fclose($handle); } 

Я сделал это с SQL-сервером:

• Я использовал команду SQL Bulkinsert в сочетании с таблицами данных.
• Таблицы данных хранятся в памяти и построены из строк чтения внутри файла.
• Каждая таблица данных построена из куска строк, а не всего файла.
• Следите за обработкой фрагмента, сохраняя указатели от последней строки и максимальный размер фрагмента.
• Когда вы читаете файл. выйдите из цикла, когда id строки> последняя строка + размер блока.
• Продолжайте цикл и продолжайте вставлять.

Также иногда, когда вы используете данные Load, если есть предупреждения, импорт остановится. Вы можете использовать ключевое слово ignore.

 LOAD DATA INFILE 'file Path' IGNORE INTO TABLE YOUR_Table 

У меня была аналогичная ситуация, когда невозможно использовать LOAD DATA. Иногда транзакции были неприемлемыми, так как данные необходимо проверять на наличие дубликатов. Тем не менее, следующее значительно улучшило время процесса для некоторых из моих файлов данных импорта.

Прежде чем ваш цикл while (CSV Lines) установит автосообщение в 0 и начнет транзакцию (только InnoDB):

 mysql_query('SET autocommit=0;'); mysql_query('START TRANSACTION;'); 

После вашего цикла, фиксация и сброс autocommit обратно до 1 (по умолчанию):

 mysql_query('COMMIT;'); mysql_query('SET autocommit=1;'); 

Замените mysql_query () любым объектом базы данных, который использует ваш код. Надеюсь, это поможет другим.