SQL_CALC_FOUND_ROWS / FOUND_ROWS() 在 PHP 中不起作用
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/674061/
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
SQL_CALC_FOUND_ROWS / FOUND_ROWS() does not work in PHP
提问by Cédric Girard
I use SQL_CALC_FOUND_ROWSin Mysql SELECT statement, to get the number of lines my SELECT would return without a LIMIT clause.
我SQL_CALC_FOUND_ROWS在 Mysql SELECT 语句中使用，以获取我的 SELECT 将在没有 LIMIT 子句的情况下返回的行数。
$sql = new mysqli('localhost', 'root', '');
$sql->select_db('mysql');
$s1 = $sql->query('select SQL_CALC_FOUND_ROWS * from db limit 0, 3');
$s2 = $sql->query('select FOUND_ROWS()');
if($row = $s2->fetch_row()) printf('%d/%d', $s1->num_rows, $row[0]);
On my WinXP dev station it return 3/0 everytime for several weeks. When I use another MySQL server from my station it return 3/0 too. On anothers PC the same code runs fine, and return the correct number (3/17 for example, if I have 17 records in mysql.db table). Every XP PC have the same PHP/Mysql version, and it ran fine in the past on my PC Using Mysql Query Browser with the same SQL queries I get the right number.
在我的 WinXP 开发站上，它在几周内每次都返回 3/0。当我从我的站点使用另一个 MySQL 服务器时，它也会返回 3/0。在另一台 PC 上，相同的代码运行良好，并返回正确的数字（例如 3/17，如果我在 mysql.db 表中有 17 条记录）。每台 XP PC 都有相同的 PHP/Mysql 版本，过去它在我的 PC 上运行良好，使用 Mysql 查询浏览器和相同的 SQL 查询，我得到了正确的数字。
Could anyone give me an idea of solution, without re-install all?
谁能给我一个解决方案的想法，而无需重新安装所有？
Sorry, my previous request was awfully unclear.
抱歉，我之前的请求非常不清楚。
采纳答案by jerry
Thank you.
谢谢你。
When I ran something analogous to your example on the mysql command line, it would work; but running it from php, it failed. The second query has to "know about" the first one, so I figure somehow that persistence/memory linking the two queries was getting messed up by the php.
当我在 mysql 命令行上运行类似于您的示例的内容时，它会起作用；但是从 php 运行它，它失败了。第二个查询必须“了解”第一个查询，所以我认为链接这两个查询的持久性/内存以某种方式被 php 搞砸了。
(It turns out that Wordpress uses this type of query to do its pagination - so our larger problem was that the pagination in a wordpress install suddenly stopped working when we moved to php 5.2.6 ... eventually tracked it down to the FOUND_ROWS()).
（事实证明，Wordpress 使用这种类型的查询来进行分页——所以我们更大的问题是，当我们移至 php 5.2.6 时，wordpress 安装中的分页突然停止工作......最终将其追踪到 FOUND_ROWS( ))。
Just for the sake of posting for people who may run into this in the future... for me it was the php setting "mysql.trace_mode" - this defaulted "on" in 5.2.6 instead of "off" like previously, and for some reason prevents the FOUND_ROWS() from working.
只是为了给将来可能会遇到这个问题的人发帖……对我来说，这是 php 设置“mysql.trace_mode”——这在 5.2.6 中默认为“on”，而不是像以前那样“off”，并且由于某种原因阻止 FOUND_ROWS() 工作。
As a "fix", we could either put this in every php page (actually, in a common "include"):
作为“修复”，我们可以将其放在每个 php 页面中（实际上，在一个常见的“包含”中）：
ini_set("mysql.trace_mode", "0");
or add this to the .htaccess:
或将此添加到 .htaccess：
php_value mysql.trace_mode "0"
Thanks again, Jerry
再次感谢，杰瑞
回答by ólafur Waage
Are you using a MySQL query method that allows for multiple queries.
您是否使用允许多个查询的 MySQL 查询方法。
From MySQL documentation.
来自 MySQL 文档。
To obtain this row count, include a
SQL_CALC_FOUND_ROWSoption in the SELECT statement, and then invokeFOUND_ROWS()afterward
要获得此行数，包括
SQL_CALC_FOUND_ROWS在SELECT语句选项，然后调用FOUND_ROWS()之后
Example:
例子：
mysql> SELECT SQL_CALC_FOUND_ROWS * FROM tbl_name
-> WHERE id > 100 LIMIT 10;
mysql> SELECT FOUND_ROWS();
Also just for fun, there's a great discussion about the race condition of FOUND_ROWS()'s usage here.
也只是为了好玩，这里有一个关于FOUND_ROWS()'s 用法的竞争条件的很好的讨论。
回答by Thierry Marianne
Another way would be to use mysqli_multi_query as stated in the PHP manual by passing both queries containing SQL_CALC_FOUND_ROWS and FOUND_ROWS separated with a semicolon
另一种方法是使用 PHP 手册中所述的 mysqli_multi_query，方法是传递包含 SQL_CALC_FOUND_ROWS 和 FOUND_ROWS 的两个查询，用分号分隔
<?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 SQL_CALC_FOUND_ROWS * FROM db limit 0, 3;";
$query .= "SELECT FOUND_ROWS()";
/* 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 Raheel Hasan
The quickest solution is to subquery your actual query like this:
最快的解决方案是子查询您的实际查询，如下所示：
SELECT SQL_CALC_FOUND_ROWS * FROM (SELECT whatever FROM whatever WHERE whatever LIMIT whatever) ax;
select FOUND_ROWS();
Now you will get the correct results. I think the main reason being that SQL_CALC_FOUND_ROWSmainly tracks rows found (i.e. without LIMITS) not rows returned.
现在您将得到正确的结果。我认为主要原因是SQL_CALC_FOUND_ROWS主要跟踪找到的行（即没有LIMITS）而不是返回的行。
回答by artnikpro
I had the same issue. The solution was stupid, I was using $wpdb->queryinstead of $wpdb->get_var. So you want to do
我遇到过同样的问题。解决方案很愚蠢，我正在使用$wpdb->query而不是$wpdb->get_var. 所以你想做
$wpdb->get_var('select FOUND_ROWS()');
if you're on WordPress
如果您使用 WordPress
回答by Cédric Girard
Well, it was a problem with mysql php extension bundled with php 5.2.6. Mysqli run fine, and another php version too. Sorry for noise and unclear question.
好吧，这是与 php 5.2.6 捆绑在一起的 mysql php 扩展的问题。Mysqli 运行良好，还有另一个 php 版本。对不起，噪音和不清楚的问题。
If you have the same problem, my advice is to re-install PHP or change version.
如果您有同样的问题，我的建议是重新安装 PHP 或更改版本。
