如何在 PHP/MYSQL 中同时执行两个 mysql 查询？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/802437/
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
How to execute two mysql queries as one in PHP/MYSQL?
提问by Prashant
I have two queries, as following:
我有两个疑问，如下：
SELECT SQL_CALC_FOUND_ROWS Id, Name FROM my_table WHERE Name LIKE '%prashant%' LIMIT 0, 10;
SELECT FOUND_ROWS();
I want to execute both these queries in a single attempt.
我想一次尝试执行这两个查询。
$result = mysql_query($query);
But then tell me how I will handle each tables set separately. Actually in ASP.NET we uses dataset which handles two queries as
但是然后告诉我我将如何分别处理每张桌子。实际上在 ASP.NET 中，我们使用数据集来处理两个查询
ds.Tables[0];
ds.Tables[1]; .. etc
How can I do the same using PHP/MYSQL?
我如何使用 PHP/MYSQL 做同样的事情？
回答by Emil H
Update:Apparently possible by passing a flag to mysql_connect(). See Executing multiple SQL queries in one statement with PHPNevertheless, any current reader should avoid using the mysql_-class of functions and prefer PDO.
更新：显然可以通过将标志传递给mysql_connect(). 请参阅使用 PHP 在一个语句中执行多个 SQL 查询然而，任何当前的读者都应该避免使用mysql_-class 函数而更喜欢 PDO。
You can't do that using the regular mysql-api in PHP. Just execute two queries. The second one will be so fast that it won't matter. This is a typical example of micro optimization. Don't worry about it.
您不能使用 PHP 中的常规 mysql-api 来做到这一点。只需执行两个查询。第二个会如此之快，以至于无关紧要。这是微优化的典型例子。别担心。
For the record, it can be done using mysqli and the mysqli_multi_query-function.
为了记录，可以使用 mysqli 和mysqli_multi_query函数来完成。
回答by Bill Karwin
As others have answered, the mysqli API can execute multi-queries with the msyqli_multi_query() function.
正如其他人回答的那样，mysqli API 可以使用 msyqli_multi_query() 函数执行多查询。
For what it's worth, PDO supports multi-query by default, and you can iterate over the multiple result sets of your multiple queries:
值得一提的是，PDO 默认支持多查询，您可以迭代多个查询的多个结果集：
$stmt = $dbh->prepare("
select sql_calc_found_rows * from foo limit 1 ;
select found_rows()");
$stmt->execute();
do {
while ($row = $stmt->fetch()) {
print_r($row);
}
} while ($stmt->nextRowset());
However, multi-query is pretty widely considered a bad ideafor security reasons. If you aren't careful about how you construct your query strings, you can actually get the exact type of SQL injection vulnerability shown in the classic "Little Bobby Tables" XKCD cartoon. When using an API that restrict you to single-query, that can't happen.
然而，出于安全原因，多查询被广泛认为是一个坏主意。如果您不注意如何构造查询字符串，您实际上可以获得经典的“小鲍比表”XKCD 卡通中显示的 SQL 注入漏洞的确切类型。当使用限制您进行单一查询的 API 时，这不会发生。
回答by Jon Benedicto
You'll have to use the MySQLi extension if you don't want to execute a query twice:
如果您不想执行两次查询，则必须使用 MySQLi 扩展：
if (mysqli_multi_query($link, $query))
{
$result1 = mysqli_store_result($link);
$result2 = null;
if (mysqli_more_results($link))
{
mysqli_next_result($link);
$result2 = mysqli_store_result($link);
}
// do something with both result sets.
if ($result1)
mysqli_free_result($result1);
if ($result2)
mysqli_free_result($result2);
}
回答by Jon Benedicto
You have to use MySQLi, below code works well
你必须使用 MySQLi，下面的代码运行良好
<?php
$mysqli = new mysqli("localhost", "my_user", "my_password", "world");
/* check connection */
if (mysqli_connect_errno()) {
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
}
$query = "SELECT CURRENT_USER();";
$query .= "SELECT Name FROM City ORDER BY ID LIMIT 20, 5";
/* execute multi query */
if ($mysqli->multi_query($query)) {
do {
/* store first result set */
if ($result = $mysqli->store_result()) {
while ($row = $result->fetch_row()) {
printf("%s\n", $row[0]);
}
$result->free();
}
/* print divider */
if ($mysqli->more_results()) {
printf("-----------------\n");
}
} while ($mysqli->next_result());
}
/* close connection */
$mysqli->close();
?>
回答by tpdi
Using SQL_CALC_FOUND_ROWSyou can't.
使用SQL_CALC_FOUND_ROWS你不能。
The row count available through FOUND_ROWS() is transient and not intended to be available past the statement following the SELECT SQL_CALC_FOUND_ROWS statement.
通过 FOUND_ROWS() 可用的行数是暂时的，不打算在 SELECT SQL_CALC_FOUND_ROWS 语句之后的语句之后可用。
As someone noted in your earlier question, using SQL_CALC_FOUND_ROWSis frequently slower than just getting a count.
正如有人在您之前的问题中指出的那样，使用SQL_CALC_FOUND_ROWS通常比仅计算要慢。
Perhaps you'd be best off doing this as as subquery:
也许您最好将其作为子查询执行：
SELECT
(select count(*) from my_table WHERE Name LIKE '%prashant%')
as total_rows,
Id, Name FROM my_table WHERE Name LIKE '%prashant%' LIMIT 0, 10;
回答by Jon Benedicto
Like this:
像这样：
$result1 = mysql_query($query1);
$result2 = mysql_query($query2);
// do something with the 2 result sets...
if ($result1)
mysql_free_result($result1);
if ($result2)
mysql_free_result($result2);
回答by Michael Pryor
It says on the PHP site that multiple queries are NOT permitted(EDIT: This is only true for the mysql extension. mysqli and PDO allow multiple queries) . So you can't do it in PHP, BUT, why can't you just execute that query in another mysql_query call, (like Jon's example)? It should still give you the correct result if you use the same connection. Also, mysql_num_rowsmay help also.
它在 PHP 站点上说不允许多个查询（编辑：这仅适用于 mysql 扩展。mysqli 和 PDO 允许多个查询）。所以你不能在 PHP 中做到这一点，但是，为什么你不能在另一个 mysql_query 调用中执行该查询（如 Jon 的例子）？如果您使用相同的连接，它仍然应该为您提供正确的结果。此外，mysql_num_rows也可能有帮助。
回答by Pacerier
Yes it is possiblewithout using MySQLi extension.
是的，不使用 MySQLi 扩展是可能的。
Simply use CLIENT_MULTI_STATEMENTSin mysql_connect's 5th argument.
只需使用CLIENT_MULTI_STATEMENTSinmysql_connect的第 5 个参数。
Refer to the comments below Husni's postfor more information.
有关更多信息，请参阅Husni 帖子下方的评论。
