Лучшая практика: импортируйте файл mySQL в PHP; разделенные запросы

Вот удобная для памяти функция, которая должна иметь возможность разбивать большой файл в отдельных запросах без необходимости одновременного открытия всего файла :

 function SplitSQL($file, $delimiter = ';') { set_time_limit(0); if (is_file($file) === true) { $file = fopen($file, 'r'); if (is_resource($file) === true) { $query = array(); while (feof($file) === false) { $query[] = fgets($file); if (preg_match('~' . preg_quote($delimiter, '~') . '\s*$~iS', end($query)) === 1) { $query = trim(implode('', $query)); if (mysql_query($query) === false) { echo '<h3>ERROR: ' . $query . '</h3>' . "\n"; } else { echo '<h3>SUCCESS: ' . $query . '</h3>' . "\n"; } while (ob_get_level() > 0) { ob_end_flush(); } flush(); } if (is_string($query) === true) { $query = array(); } } return fclose($file); } } return false; } 

Я тестировал его на большом дампе phpMyAdmin SQL, и он работал нормально.

Некоторые тестовые данные:

 CREATE TABLE IF NOT EXISTS "test" ( "id" INTEGER PRIMARY KEY AUTOINCREMENT, "name" TEXT, "description" TEXT ); BEGIN; INSERT INTO "test" ("name", "description") VALUES (";;;", "something for you mind; body; soul"); COMMIT; UPDATE "test" SET "name" = "; " WHERE "id" = 1; 

И соответствующий выход:

 SUCCESS: CREATE TABLE IF NOT EXISTS "test" ( "id" INTEGER PRIMARY KEY AUTOINCREMENT, "name" TEXT, "description" TEXT ); SUCCESS: BEGIN; SUCCESS: INSERT INTO "test" ("name", "description") VALUES (";;;", "something for you mind; body; soul"); SUCCESS: COMMIT; SUCCESS: UPDATE "test" SET "name" = "; " WHERE "id" = 1; 

Одна страница PHPMyAdmin – Adminer – всего один файл скрипта PHP. проверить: http://www.adminer.org/en/

Когда StackOverflow выпустил свой ежемесячный дамп данных в формате XML, я написал PHP-скрипты для загрузки его в базу данных MySQL. За несколько минут я импортировал около 2,2 гигабайта XML.

Моя методика состоит в том, чтобы prepare() INSERT с записями параметров для значений столбцов. Затем используйте XMLReader для циклического XMLReader по элементам XML и execute() мой подготовленный запрос, включив значения параметров. Я выбрал XMLReader, потому что это потоковый XML-ридер; он читает ввод XML постепенно, а не требует загрузки всего файла в память.

Вы также можете прочитать CSV-файл по одной строке за раз с помощью fgetcsv() .

Если вы вставляете в таблицы InnoDB, я рекомендую запускать и совершать транзакции явно, чтобы уменьшить накладные расходы на автосообщение. Я фиксирую каждую 1000 строк, но это произвольно.

Я не буду публиковать код здесь (из-за политики лицензирования StackOverflow), но в псевдокоде:

 connect to database open data file PREPARE parameterizes INSERT statement begin first transaction loop, reading lines from data file: { parse line into individual fields EXECUTE prepared query, passing data fields as parameters if ++counter % 1000 == 0, commit transaction and begin new transaction } commit final transaction 

Написание этого кода на PHP не является наукой о ракетах, и оно выполняется довольно быстро, когда вы используете подготовленные операторы и явные транзакции. Эти функции недоступны в устаревшем расширении PHP mysql , но вы можете использовать их, если используете mysqli или PDO_MySQL .

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

Я написал свой код в abstract классе PHP, который я подкласса для каждой таблицы, которую мне нужно загрузить. Каждый подкласс объявляет столбцы, которые он хочет загрузить, и сопоставляет их с полями в файле данных XML по имени (или положением, если файл данных CSV).

экспорт

Первым шагом является получение ввода в разумном формате для синтаксического анализа при его экспорте. Из вашего вопроса выясняется, что вы контролируете экспорт этих данных, но не импорт.

 ~: mysqldump test --opt --skip-extended-insert | grep -v '^--' | grep . > test.sql 

Это выгружает тестовую базу данных, исключая все строки комментариев и пустые строки в test.sql. Он также отключает расширенные вставки, то есть есть один оператор INSERT для каждой строки. Это поможет ограничить использование памяти во время импорта, но с учетом скорости импорта.

Импортировать

Сценарий импорта прост:

 <?php $mysqli = new mysqli('localhost', 'hobodave', 'p4ssw3rd', 'test'); $handle = fopen('test.sql', 'rb'); if ($handle) { while (!feof($handle)) { // This assumes you don't have a row that is > 1MB (1000000) // which is unlikely given the size of your DB // Note that it has a DIRECT effect on your scripts memory // usage. $buffer = stream_get_line($handle, 1000000, ";\n"); $mysqli->query($buffer); } } echo "Peak MB: ",memory_get_peak_usage(true)/1024/1024; 

Это будет использовать абсурдно низкий объем памяти, как показано ниже:

 daves-macbookpro:~ hobodave$ du -hs test.sql 15M test.sql daves-macbookpro:~ hobodave$ time php import.php Peak MB: 1.75 real 2m55.619s user 0m4.998s sys 0m4.588s 

Что вы говорите, вы обработали 15 МБ mysqldump с максимальным объемом использования памяти 1,75 МБ всего за 3 минуты.

Альтернативный экспорт

Если у вас достаточно высокий memory_limit, и это слишком медленно, вы можете попробовать это, используя следующий экспорт:

 ~: mysqldump test --opt | grep -v '^--' | grep . > test.sql 

Это позволит использовать расширенные вставки, которые вставляют несколько строк в один запрос. Вот статистика для одной и той же базы данных:

 daves-macbookpro:~ hobodave$ du -hs test.sql 11M test.sql daves-macbookpro:~ hobodave$ time php import.php Peak MB: 3.75 real 0m23.878s user 0m0.110s sys 0m0.101s 

Обратите внимание, что он использует более 2-х ОЗУ на 3,75 МБ, но занимает примерно 1/6. Я предлагаю попробовать оба метода и посмотреть, что подходит вашим потребностям.

Редактировать:

Мне не удалось получить символ новой строки, чтобы отображаться буквально в любом выводе mysqldump, используя любые типы полей CHAR, VARCHAR, BINARY, VARBINARY и BLOB. Если у вас есть поля BLOB / BINARY, тогда используйте, пожалуйста, следующее:

 ~: mysqldump5 test --hex-blob --opt | grep -v '^--' | grep . > test.sql 

Не можете ли вы установить phpMyAdmin, gzip файл (который должен сделать его намного меньше) и импортировать его с помощью phpMyAdmin?

EDIT: Ну, если вы не можете использовать phpMyAdmin, вы можете использовать код из phpMyAdmin. Я не уверен в этой конкретной части, но она очень хорошо структурирована.

Уже ответил: Загрузка .sql-файлов из PHP. Также:

• http://webxadmin.free.fr/article/import-huge-mysql-dumps-using-php-only-342.php
• http://www.phpbuilder.com/board/showthread.php?t=10323180
• http://forums.tizag.com/archive/index.php?t-3581.html

Разделение запроса не может быть надежно выполнено без разбора. Здесь действительный SQL, который невозможно было бы правильно разделить с регулярным выражением.

 SELECT ";"; SELECT ";\"; a;"; SELECT "; abc"; 

Я написал небольшой класс SqlFormatter в PHP, который включает в себя токенизатор запросов. Я добавил к нему метод splitQuery, который надежно разбивает все запросы (включая приведенный выше пример).

https://github.com/jdorn/sql-formatter/blob/master/SqlFormatter.php

Вы можете удалить формат и выделить методы, если они вам не нужны.

Один недостаток заключается в том, что он требует, чтобы вся строка sql была в памяти, что может быть проблемой, если вы работаете с огромными sql-файлами. Я уверен, что немного потрудился, вы можете заставить метод getNextToken работать с указателем файла.

Во-первых, спасибо за эту тему. Это сэкономило много времени для меня 🙂 И позвольте мне немного исправить ваш код. Иногда, если TRIGGERS или PROCEDURES находятся в файле дампа, недостаточно проверить; разделители. В этом случае может быть DELIMITER [что-то] в sql-коде, чтобы сказать, что инструкция не закончится; но [что-то]. Например, раздел в xxx.sql:

  DELIMITER // CREATE TRIGGER `mytrigger` BEFORE INSERT ON `mytable` FOR EACH ROW BEGIN SET NEW.`create_time` = NOW(); END // DELIMITER ; 

Поэтому сначала нужно иметь falg, чтобы обнаружить, что запрос не заканчивается; И удалите ненужные куски запросов, потому что mysql_query не нуждается в разделителе (разделитель – это конец строки), поэтому mysql_query нужно примерно так:

  CREATE TRIGGER `mytrigger` BEFORE INSERT ON `mytable` FOR EACH ROW BEGIN SET NEW.`create_time` = NOW(); END; 

Так что небольшая работа и вот фиксированный код:

  function SplitSQL($file, $delimiter = ';') { set_time_limit(0); $matches = array(); $otherDelimiter = false; if (is_file($file) === true) { $file = fopen($file, 'r'); if (is_resource($file) === true) { $query = array(); while (feof($file) === false) { $query[] = fgets($file); if (preg_match('~' . preg_quote('delimiter', '~') . '\s*([^\s]+)$~iS', end($query), $matches) === 1){ //DELIMITER DIRECTIVE DETECTED array_pop($query); //WE DON'T NEED THIS LINE IN SQL QUERY if( $otherDelimiter = ( $matches[1] != $delimiter )){ }else{ //THIS IS THE DEFAULT DELIMITER, DELETE THE LINE BEFORE THE LAST (THAT SHOULD BE THE NOT DEFAULT DELIMITER) AND WE SHOULD CLOSE THE STATEMENT array_pop($query); $query[]=$delimiter; } } if ( !$otherDelimiter && preg_match('~' . preg_quote($delimiter, '~') . '\s*$~iS', end($query)) === 1) { $query = trim(implode('', $query)); if (mysql_query($query) === false){ echo '<h3>ERROR: ' . $query . '</h3>' . "\n"; }else{ echo '<h3>SUCCESS: ' . $query . '</h3>' . "\n"; } while (ob_get_level() > 0){ ob_end_flush(); } flush(); } if (is_string($query) === true) { $query = array(); } } return fclose($file); } } return false; } 

Надеюсь, я тоже смогу помочь кому-то. Хорошего дня!

http://www.ozerov.de/bigdump/ был очень полезен для меня при импорте 200 + MB sql-файла.

Примечание. Файл SQL должен присутствовать на сервере, чтобы процесс был завершен без каких-либо проблем.

Можете ли вы использовать LOAD DATA INFILE?

Если вы отформатируете файл db dump с помощью SELECT INTO OUTFILE, это должно быть именно то, что вам нужно. Нет причин, чтобы PHP разбирал что угодно.

Помогают ли эти ссылки:
http://www.ozerov.de/bigdump.php
http://www.wanderings.net/notebook/Main/HowToImportLargeMySQLDataFiles

Я столкнулся с той же проблемой. Я решил это, используя регулярное выражение:

 function splitQueryText($query) { // the regex needs a trailing semicolon $query = trim($query); if (substr($query, -1) != ";") $query .= ";"; // i spent 3 days figuring out this line preg_match_all("/(?>[^;']|(''|(?>'([^']|\\')*[^\\\]')))+;/ixU", $query, $matches, PREG_SET_ORDER); $querySplit = ""; foreach ($matches as $match) { // get rid of the trailing semicolon $querySplit[] = substr($match[0], 0, -1); } return $querySplit; } $queryList = splitQueryText($inputText); foreach ($queryList as $query) { $result = mysql_query($query); } 

Вы можете использовать phpMyAdmin для импорта файла. Даже если он огромен, просто используйте каталог конфигурации UploadDir , загрузите его и выберите его на странице импорта phpMyAdmin. Когда обработка файлов будет близка к ограничениям PHP, phpMyAdmin прерывает импорт, показывает, что вы снова импортируете страницу с предопределенными значениями, указывающими, где продолжить импорт.

что ты думаешь о:

 system("cat xxx.sql | mysql -l username database");