最佳实践:在 PHP 中导入 mySQL 文件；拆分查询 [英] Best practice: Import mySQL file in PHP; split queries

问题描述

我遇到了一种情况，我必须更新共享托管服务提供商的网站.该网站有一个 CMS.使用 FTP 上传 CMS 的文件非常简单.

I have a situation where I have to update a web site on a shared hosting provider. The site has a CMS. Uploading the CMS's files is pretty straightforward using FTP.

我还必须导入一个大的(相对于 PHP 脚本的范围)数据库文件(大约 2-3 MB 未压缩).Mysql 是关闭的，无法从外部访问，所以我必须使用 FTP 上传一个文件，并启动一个 PHP 脚本来导入它.遗憾的是，我无权访问 mysql 命令行函数，因此我必须使用本机 PHP 解析和查询它.我也不能使用 LOAD DATA INFILE.我也不能使用任何类型的交互式前端，如 phpMyAdmin，它需要以自动化方式运行.我也不能使用 mysqli_multi_query().

I also have to import a big (relative to the confines of a PHP script) database file (Around 2-3 MB uncompressed). Mysql is closed for access from the outside, so I have to upload a file using FTP, and start a PHP script to import it. Sadly, I do not have access to the mysql command line function so I have to parse and query it using native PHP. I also can't use LOAD DATA INFILE. I also can't use any kind of interactive front-end like phpMyAdmin, it needs to run in an automated fashion. I also can't use mysqli_multi_query().

是否有人知道或拥有已编码的简单解决方案，可靠地将此类文件拆分为单个查询(可能有多行语句)并运行查询.我想避免自己开始摆弄它，因为我可能会遇到许多问题(如何检测字段分隔符是否是数据的一部分；如何处理备注字段中的换行符；等等在).对此，必须有现成的解决方案.

Does anybody know or have a already coded, simple solution that reliably splits such a file into single queries (there could be multi-line statements) and runs the query. I would like to avoid to start fiddling with it myself due to the many gotchas that I'm likely to come across (How to detect whether a field delimiter is part of the data; how to deal with line breaks in memo fields; and so on). There must be a ready made solution for this.

推荐答案

这是一个内存友好的功能，它应该能够在单个查询中拆分一个大文件，而无需一次打开整个文件:

Here is a memory-friendly function that should be able to split a big file in individual queries without needing to open the whole file at once:

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 转储上对其进行了测试，效果很好.

I tested it on a big phpMyAdmin SQL dump and it worked just fine.

一些测试数据:

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;

这篇关于最佳实践:在 PHP 中导入 mySQL 文件；拆分查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！