最佳实践:在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屋!