PHP：一个 mysql_query 语句中的多个 SQL 查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/345637/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PHP: multiple SQL queries in one mysql_query statement
提问by Matt
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_query() 加载的 SQL 转储文件。不幸的是，不可能用它执行多个查询。
-> It cannot be assumed that the mysql command-line client(mysql --help) is installed -- for loading the SQL file directly
-> 不能假设安装了mysql命令行客户端（mysql --help）--用于直接加载SQL文件
-> It cannot be assumed that the mysqliextension is installed
-> 不能假设安装了mysqli扩展
/* 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;
The explode() below won't work because some of the dump content's values contain semicolons.
下面的explode() 将不起作用，因为某些转储内容的值包含分号。
$sql = explode(';', file_get_contents('dump.sql'));
foreach ($sql as $key => $val) {
mysql_query($val);
}
What's the best way to load the SQL without modifying the dump file?
在不修改转储文件的情况下加载 SQL 的最佳方法是什么？
回答by Bill Karwin
You have more problem cases than just semicolons within strings.
您有更多的问题案例，而不仅仅是字符串中的分号。
- Script builtincommands that cannot be executed by
mysql_query(), likeUSE. - Statements that are not terminated with a semicolon, like
DELIMITER. - Statements that contain semicolons, but not inside quotes, like
CREATE PROCEDURE.
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.
我不知道有什么简单的方法来处理这个任务，而不是使用 mysql 命令行客户端。我意识到您说过您不能依赖该客户端的存在，但是如果没有该客户端，您需要大量 PHP 代码来解析脚本并适当地执行语句。
You may be able to find such code inside the phpMyAdminproduct. 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.
您可以在phpMyAdmin产品中找到此类代码。但是，该产品是根据 GPL 许可的，因此如果您使用任何代码，您还必须根据 GPL 许可您自己的项目。
See also my answers to these related questions:
另请参阅我对这些相关问题的回答：
回答by Matt
Occam's razor strikes again.
奥卡姆剃刀再次来袭。
The above explode() function wasn't going to work because there are semicolons in the values. However, what I failed to realize (and mention) is that semicolons always preceed newlines in the SQL dump file. Alas,
上面的explode() 函数不起作用，因为值中有分号。然而，我没有意识到（并提到）的是分号总是在 SQL 转储文件中的换行符之前。唉，
$sql = explode(";\n", file_get_contents('dump.sql'));
foreach ($sql as $key => $val) {
mysql_query($val);
}
Also, thanks for your help, Bill.
另外，感谢你的帮助，比尔。
回答by FlySwat
There is a bigger issue here. You might have statements that rely on other statements. You can't just execute them linearly blindly.
这里有一个更大的问题。您可能有依赖于其他语句的语句。你不能只是盲目地线性执行它们。
回答by DolDurma
<?php
//STATIC QUERY
$sql1 = "
CREATE TABLE tblTable (
strOne VARCHAR(50) NOT NULL,
strTwo VARCHAR(50) NOT NULL,
strThree VARCHAR(50) NOT NULL
);
INSERT INTO tblTable
(strOne, strTwo, strThree)
VALUES ('String 1', 'String 2', 'String 3');
UPDATE tblTable
SET
strOne = 'String One',
strTwo = 'String Two'
WHERE strThree = 'String 3';
";
//GET FROM FILE
$sql2 = file_get_contents('dump.sql');
$queries = preg_split("/;+(?=([^'|^\\']*['|\\'][^'|^\\']*['|\\'])*[^'|^\\']*[^'|^\\']$)/", $sql);
foreach ($queries as $query){
if (strlen(trim($query)) > 0) mysql_query($query);
}
?>
