PHP：一个mysql_query语句中的多个SQL查询

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 mysqli extension 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的最佳方法是什么？

4 个解决方案

#1

You have more problem cases than just semicolons within strings.

您遇到的问题不仅仅是字符串中的分号。

Script builtin commands that cannot be executed by mysql_query(), like USE.
脚本内置命令，不能由mysql_query（）执行，如USE。
Statements that are not terminated with a semicolon, like DELIMITER.
不以分号结尾的语句，如DELIMITER。
Statements that contain semicolons, but not inside quotes, like CREATE PROCEDURE.
包含分号但不在引号内的语句，如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 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.

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

See also my answers to these related questions:

另见我对这些相关问题的回答：

Running MySQL *.sql files in PHP
在PHP中运行MySQL * .sql文件
Loading .sql files from within PHP
从PHP中加载.sql文件
is it possible to call a sql script from a stored procedure in another sql script?
是否可以从另一个sql脚本中的存储过程调用sql脚本？

#2

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.

还有，谢谢你的帮助，比尔。

#3

There is a bigger issue here. You might have statements that rely on other statements. You can't just execute them linearly blindly.

这里有一个更大的问题。您可能有依赖其他语句的语句。你不能盲目地线性执行它们。

#4

<?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);
}
?>

#1