最佳实践：在PHP中导入mySQL文件;拆分查询

你不能安装phpMyAdmin，gzip文件（这应该使它更小）并使用phpMyAdmin导入它吗？

编辑：好吧，如果你不能使用phpMyAdmin，你可以使用phpMyAdmin中的代码。我不确定这个特定的部分，但它的结构通常很好。

您可以使用 LOAD DATA INFILE 吗？

如果使用 SELECT INTO OUTFILE 格式化数据库转储文件，这应该正是您所需要的。没有理由让PHP解析任何东西。

已经回答：从 PHP 中加载 .sql 文件 另外：

• 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

你怎么看：

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

下面是一个内存友好的函数，它应该能够在单个查询中拆分一个大文件，而无需一次打开整个文件：

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;

当 StackOverflow 以 XML 格式发布他们的每月数据转储时，我编写了 PHP 脚本将其加载到 MySQL 数据库中。我在几分钟内导入了大约 2.2 GB 的 XML。

我的技术是准备（）一个带有列值参数占位符的语句。然后使用 XMLReader 遍历 XML 元素并执行（）我准备好的查询，插入参数的值。我之所以选择 XMLReader，是因为它是一个流式 XML 读取器;它以增量方式读取 XML 输入，而不需要将整个文件加载到内存中。INSERT

您还可以使用 fgetcsv（） 一次读取一行 CSV 文件。

如果要移植到 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则可以使用它们。

我还添加了方便的功能，例如错误检查、进度报告，以及在数据文件不包含其中一个字段时支持默认值。

我在PHP类中编写了代码，该类是我需要加载的每个表的子类。每个子类都声明它要加载的列，并按名称（如果数据文件是 CSV，则按位置）将它们映射到 XML 数据文件中的字段。abstract

单页 PHPMyAdmin - Adminer - 只有一个 PHP 脚本文件. 检查 ： http://www.adminer.org/en/

我遇到了同样的问题。我使用正则表达式解决了它：

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就会中断导入，再次显示带有预定义值的导入页面，指示在导入中继续的位置。

出口

第一步是以合理的格式获取输入，以便在导出时进行解析。从您的问题 您似乎可以控制此数据的导出，但不能控制导入。

~: 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 分钟的时间内处理了一个 3MB 的 mysqldump，其峰值 RAM 使用率为 1.75 MB。

替代导出

如果您的memory_limit足够高，但速度太慢，则可以使用以下导出来尝试此操作：

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

这将允许扩展插入，即在单个查询中插入多行。以下是同一 datbase 的统计信息：

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

请注意，它使用的 RAM 是 3.75 MB 的 2 倍以上，但花费的时间约为 1/6。我建议尝试这两种方法，看看哪种方法适合您的需求。

编辑：

我无法使用任何 CHAR、VARCHAR、BINARY、VARIN 和 BLOB 字段类型让换行符出现在任何 mysqldump 输出中。如果您确实有 BLOB/BINARY 字段，请使用以下字段以防万一：

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

如果不进行分析，就无法可靠地拆分查询。这是有效的 SQL，不可能用正则表达式正确拆分。

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

我在 PHP 中编写了一个小的 SqlFormatter 类，其中包含一个查询分词器。我向它添加了一个 splitQuery 方法，该方法可以可靠地拆分所有查询（包括上面的示例）。

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

如果不需要，可以删除格式并突出显示方法。

一个缺点是它要求整个 sql 字符串都在内存中，如果您正在处理巨大的 sql 文件，这可能是一个问题。我敢肯定，只要稍加修改，您就可以使getNextToken方法在文件指针上工作。

首先，感谢您的这个话题。这为我节省了很多时间:) 让我对你的代码做一些小的修复。 有时，如果 TRIGGERS 或 PROCEDURES 位于转储文件中，则仅检查 ;分隔符。 在这种情况下，可能是 sql 代码中的 DELIMITER [something]，表示语句不会以 ;但是[某事]。例如，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文件应该已经存在于服务器中，以便该过程可以毫无问题地完成