如何计算所有记录但只检索(LIMIT)一个特定数字进行显示?

2022-01-04 00:00:00 mysql pagination

我只想回显前 10 行，但我需要计算受查询影响的总行数.

I want to echo only the first 10 rows, but I need to count the total number of rows affected by the query.

我在做一个 LIMIT 10 然后用明显的问题来计算我一直得到 10 作为计数.

I was doing a LIMIT 10 and then counting with the obvious problem I kept getting 10 as the count.

正确的做法是什么?

$data = mysql_query("SELECT * FROM Badges WHERE UID = '$user' ORDER by Date DESC");
$count = mysql_num_rows($data);

while($row = mysql_fetch_array( $data )) 
    { 
    echo $row['Site'];
    }

推荐答案

MySQL 对这类事情有一些特殊的支持.首先，在您的 SELECT 中包含 SQL_CALC_FOUND_ROWS:

MySQL has some special support for this sort of thing. First, include SQL_CALC_FOUND_ROWS in your SELECT:

SELECT SQL_CALC_FOUND_ROWS *
FROM Badges
WHERE UID = '$user'
ORDER by Date DESC
LIMIT 10 -- Or whatever

然后拉出你的行，然后立即查看FOUND_ROWS() 像这样:

Then pull out your rows and then immediately look at FOUND_ROWS() like this:

SELECT FOUND_ROWS()

在不考虑 LIMIT 子句的情况下获取与原始查询匹配的行数.

to get the number of rows that matched your original query without considering the LIMIT clause.

这是特定于 MySQL 的，但它应该比执行两次查询快一点.

This is MySQL-specific but it should be a little faster than doing two queries.

相关文章