SQL_CALC_FOUND_ROWS / FOUND_ROWS()在PHP中不工作

I use SQL_CALC_FOUND_ROWS in Mysql SELECT statement, to get the number of lines my SELECT would return without a LIMIT clause.

我在Mysql SELECT语句中使用SQL_CALC_FOUND_ROWS，以获取在没有LIMIT子句的情况下我的SELECT返回的行数。

$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上，同样的代码运行良好，并返回正确的数字(例如，如果我在mysql中有17条记录，则返回3/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.

对不起，我之前的要求不明确。

6 个解决方案

#1

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页面(实际上，在一个常见的“include”中):

ini_set("mysql.trace_mode", "0");

or add this to the .htaccess:

或添加到。htaccess:

php_value mysql.trace_mode "0"

Thanks again, Jerry

再次感谢,杰瑞

#2

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_ROWS option in the SELECT statement, and then invoke FOUND_ROWS() afterward

要获取行计数，请在SELECT语句中包含SQL_CALC_FOUND_ROWS选项，然后随后调用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()的使用情况的大讨论。

#3

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

#4

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。6附带的mysql php扩展的问题。Mysqli运行良好，还有另一个php版本。对不起，有噪音和不清楚的问题。

If you have the same problem, my advice is to re-install PHP or change version.

如果您有同样的问题，我的建议是重新安装PHP或更改版本。

#5

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_ROWS mainly tracks rows found (i.e. without LIMITS) not rows returned.

现在你会得到正确的结果。我认为主要原因是SQL_CALC_FOUND_ROWS主要跟踪找到的行(即没有限制)，而不是返回的行。

#6

I had the same issue. The solution was stupid, I was using $wpdb->query instead of $wpdb->get_var. So you want to do

我也有同样的问题。解决方案很愚蠢，我使用了$wpdb->查询而不是$wpdb->get_var。你想这样做

$wpdb->get_var('select FOUND_ROWS()');

if you're on WordPress

如果你在WordPress

#1