如何在 PHP 中使用 MySQL Found_Rows()？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3453809/
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 use MySQL Found_Rows() in PHP?
提问by murvinlai
I try to avoid doing Count() because of performance issue. (i.e. SELECT COUNT() FROM Users)
由于性能问题，我尽量避免使用 Count( )。（即 SELECT COUNT() FROM Users）
If I run the followings in phpMyAdmin, it is ok:
如果我在 phpMyAdmin 中运行以下命令，就可以了：
SELECT SQL_CALC_FOUND_ROWS * FROM Users;
SELECT FOUND_ROWS();
It will return # of rows. i.e. # of Users.
它将返回# 行。即用户数量。
However, if I run in in PHP, I cannot do this:
但是，如果我在 PHP 中运行，我不能这样做：
$query = 'SELECT SQL_CALC_FOUND_ROWS * FROM Users;
SELECT FOUND_ROWS(); ';
mysql_query($query);
It seems like PHP doesn't like to have two queries passing in. So, how can I do that?
似乎 PHP 不喜欢传入两个查询。那么，我该怎么做呢？
回答by Marc B
SQL_CALC_FOUND_ROWSis only useful if you're using a LIMITclause, but still want to know how many rows would've been found without the LIMIT.
SQL_CALC_FOUND_ROWS仅当您使用LIMIT子句时才有用，但仍想知道如果没有LIMIT.
Think of how this works:
想想这是如何工作的：
SELECT SQL_CALC_FOUND_ROWS * FROM Users;
You're forcing the database to retrieve/parse ALL the data in the table, and then you throw it away. Even if you aren't going to retrieve any of the rows, the DB server will still start pulling actual data from the disk on the assumption that you will want that data.
您正在强制数据库检索/解析表中的所有数据，然后将其丢弃。即使您不打算检索任何行，数据库服务器仍会开始从磁盘中提取实际数据，前提是您需要这些数据。
In human terms, you bought the entire contents of the super grocery store, but threw away everything except the pack of gum from the stand by the cashier.
用人的话说，你买了超级杂货店的所有东西，但除了收银台的一包口香糖外，你把所有东西都扔掉了。
Whereas, doing:
鉴于，做：
SELECT count(*) FROM users;
lets the DB engine know that while you want to know how many rows there are, you couldn't care less about the actual data. On most any intelligent DBMS, the engine can retrieve this count from the table's metadata, or a simple run through the table's primary key index, without ever touching the on-disk row data.
让数据库引擎知道，虽然您想知道有多少行，但您不能不关心实际数据。在大多数智能 DBMS 上，引擎可以从表的元数据或简单地运行表的主键索引中检索此计数，而无需触及磁盘上的行数据。
回答by JochenJung
Its two queries:
它的两个查询：
$query = 'SELECT SQL_CALC_FOUND_ROWS * FROM Users';
mysql_query($query);
$query = 'SELECT FOUND_ROWS()';
mysql_query($query);
PHP can only issue a single query per mysql_query call
PHP 每次 mysql_query 调用只能发出一个查询
回答by Mchl
It's a common misconception, that SQL_CALC_FOUND_ROWS performs better than COUNT(). See this comparison from Percona guys: http://www.mysqlperformanceblog.com/2007/08/28/to-sql_calc_found_rows-or-not-to-sql_calc_found_rows/
这是一个常见的误解，认为 SQL_CALC_FOUND_ROWS 的性能优于 COUNT()。从 Percona 人那里看到这个比较：http://www.mysqlperformanceblog.com/2007/08/28/to-sql_calc_found_rows-or-not-to-sql_calc_found_rows/
To answer you question: Only one query is allowed per one mysql_query call, as described in manual: mysql_query() sends a unique query (multiple queries are not supported)
回答您的问题：每个 mysql_query 调用只允许一个查询，如手册中所述： mysql_query() sends a unique query (multiple queries are not supported)
Multiple queries are supported when using ext/mysqlias your MySQL extension:
ext/mysqli用作 MySQL 扩展时支持多个查询：
回答by diyism
Use 'union' and empty columns:
使用“联合”和空列：
$sql="(select sql_calc_found_rows tb.*, tb1.title
from orders tb
left join goods tb1 on tb.goods_id=tb1.id
where {$where}
order by created desc
limit {$offset}, {$page_size})
union
(select found_rows(), '', '', '', '', '', '', '', '', '')
";
$rs=$db->query($sql)->result_array();
$total=array_pop($rs);
$total=$total['id'];
回答by Bagova
Only this code works for me so i want to share it for you.
只有这段代码对我有用，所以我想为你分享。
$Result=mysqli_query($i_link,"SELECT SQL_CALC_FOUND_ROWS id From users LIMIT 10");
$NORResult=mysqli_query($i_link,"Select FOUND_ROWS()");
$NORRow=mysqli_fetch_array($NORResult);
$NOR=$NORRow["FOUND_ROWS()"];
echo $NOR;
回答by Amin Adel
This is an easy way & works for me :
这是一种简单的方法，对我有用：
$query = "
SELECT SQL_CALC_FOUND_ROWS *
FROM tb1
LIMIT 5";
$result = mysqli_query($link, $query);
$query = "SELECT FOUND_ROWS() AS count";
$result2 = mysqli_query($link, $query);
$row = mysqli_fetch_array($result2);
echo $row['count'];
回答by Naktibalda
Do you really think that selecting ALL rows from tables is faster than counting them?
Myisam stores a number of records in table's metadata, so SELECT COUNT(*) FROM table don't have to access data.
你真的认为从表中选择所有行比计算它们更快吗？
Myisam 在表的元数据中存储了许多记录，因此 SELECT COUNT(*) FROM table 不必访问数据。
