PHP:在一个mysql_query语句中进行多个SQL查询 [英] PHP: multiple SQL queries in one mysql_query statement

问题描述

所以我有一个SQL转储文件，需要使用mysql_query()加载.不幸的是，不可能用它执行多个查询.

So I have an SQL dump file that needs to be loaded using mysql_query(). Unfortunately, it's not possible to execute multiple queries with it.

->不能假定已安装 mysql命令行客户端(mysql --help)-直接加载SQL文件

-> It cannot be assumed that the mysql command-line client (mysql --help) is installed -- for loading the SQL file directly

->不能假定已安装 mysqli 扩展

-> It cannot be assumed that the mysqli extension is installed

/* contents of dump.sql, including comments */
DELETE FROM t3 WHERE body = 'some text; with semicolons; scattered; throughout';
DELETE FROM t2 WHERE name = 'hello';
DELETE FROM t1 WHERE id = 1;

下面的explode()无效，因为某些转储内容的值包含分号.

The explode() below won't work because some of the dump content's values contain semicolons.

$sql = explode(';', file_get_contents('dump.sql'));
foreach ($sql as $key => $val) {
    mysql_query($val);
}

在不修改转储文件的情况下加载SQL的最佳方法是什么?

What's the best way to load the SQL without modifying the dump file?

推荐答案

除了字符串中的分号外，您遇到的问题还更多.

You have more problem cases than just semicolons within strings.

• 无法执行的脚本内置命令通过mysql_query()，例如USE.
• 未用分号终止的语句，例如 DELIMITER .
• 包含分号但不包含引号的语句，例如CREATE PROCEDURE.

• Script builtin commands that cannot be executed by mysql_query(), like USE.
• Statements that are not terminated with a semicolon, like DELIMITER.
• Statements that contain semicolons, but not inside quotes, like CREATE PROCEDURE.

我不知道一种简单的方法来处理此任务，而无需使用mysql命令行客户端.我意识到您说过您不能依靠该客户端，但是如果没有该客户端，则需要大量的PHP代码来解析脚本并适当地执行语句.

I don't know of an easy way to handle this task, without shelling out to the mysql command-line client. I realize you said you can't rely on that client being present, but without that client, you need a large amount of PHP code to parse the script and execute statements appropriately.

您也许可以在 phpMyAdmin 产品中找到此类代码.但是，该产品是根据GPL许可的，因此，如果您使用任何代码，则还必须根据GPL许可自己的项目.

You may be able to find such code inside the phpMyAdmin product. However, that product is licensed under the GPL, so if you use any of the code, you must also license your own project under the GPL.

另请参阅我对这些相关问题的回答:

See also my answers to these related questions:

• 在PHP中运行MySQL * .sql文件
• 从PHP内加载.sql文件

• Running MySQL *.sql files in PHP
• Loading .sql files from within PHP
• is it possible to call a sql script from a stored procedure in another sql script?

这篇关于PHP:在一个mysql_query语句中进行多个SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！