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

Ответ 1

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

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;

Ответ 2

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

Ответ 3

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

Моя техника заключается в prepare() a INSERT с записями параметров для значений столбца. Затем используйте 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 не является наукой о ракетах, и он работает довольно быстро, когда вы используете подготовленные операторы и явные транзакции. Эти функции недоступны в устаревшем mysql расширении PHP, но вы можете использовать их, если используете mysqli или PDO_MySQL.

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

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

Ответ 4

Экспорт

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

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

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

Импорт

Импорт script прост как это:

<?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

В чем говорится, что вы обработали 15MB 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. Я предлагаю попробовать оба метода и посмотреть, что подходит вашим потребностям.

Edit:

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

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

Ответ 5

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

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

Ответ 6

Уже ответил: Загрузка файлов .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

Ответ 7

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

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

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

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

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

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

Ответ 8

Во-первых, спасибо за эту тему. Это сэкономило много времени для меня:) И позвольте мне немного исправить ваш код. Иногда, если 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;
}

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

Ответ 9

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

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

Ответ 10

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

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

Ответ 11

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

Ответ 12

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

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

Ответ 13

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

Ответ 14

что вы думаете о:

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