SQL在使用LIMIT时如何计算总行数

Question

我正在尝试创建一个简单的图库，每页显示16张图片。使用LIMIT 16来显示正确数量的图像，但如果行数超过16行，则希望在底部有链接，允许用户导航到下一页。

我知道可以通过移除限制并简单地使用循环来显示前16个项目来实现预期结果，但这样效率低下。显然，计算行数总是等于16。

$sql .= "posts p, images im, postimages pi WHERE
    i.active = 1 
    AND pi.post_id = p.id
    AND pi.image_id = im.image_id 
    ORDER BY created_at LIMIT 16";

有没有人能建议一种更有效率的方法来完成它？

谢谢。

- dan

Answer 1

在 MySQL 中：

SELECT  SQL_CALC_FOUND_ROWS
        *
FROM    posts p, images im, postimages pi
WHERE   i.active = 1 
        AND pi.post_id = p.id
        AND pi.image_id = im.image_id 
ORDER BY
        created_at
LIMIT 16;

SELECT  FOUND_ROWS();

第一个查询将返回 16 行数据，而第二个查询将会返回在第一个查询中没有 LIMIT 子句的情况下会被返回的行数。

你可以使用它来决定是否显示“下一页”链接。

- Quassnoi

---

+1. 请参阅MySQL文档：[http://dev.mysql.com/doc/refman/5.6/en/information-functions.html#function_found-rows] - Wolfgang

@Quassnoi，你是如何返回第一和第二个查询的结果的？我只能看到FOUND_ROWS()的结果。 - tim peterson

@Quassnoi，我认为你是对的，似乎我正在处理的更多是使用PHP让它们都显示出来的问题... - tim peterson

@timpeterson，我发现唯一的方法是运行两个单独的查询，如果这对你有帮助的话。（你应该在事务中包装这个过程，以确保没有其他人可以在两个之间偷偷插入第二个查询） - RonLugge

@RonLugge：FOUND_ROWS() 是会话范围的，其他会话不会影响它。 - Quassnoi

Answer 2

运行一个不带限制的单独查询，仅返回计数。

- Joel Coehoorn

---

鉴于得票最高的答案，您能否解释更多为什么这样说？ - tim peterson

@timpeterson 这也是一个有效的选择。它在需要两次访问数据库方面存在一些问题，但它仍然是一个完全有效（如果不太“高效”）的答案。就其效率而言，根据您所做的事情，有时最高评分的答案可能会通过提供获取相同信息的两种不同方式来违反DRY原则。 - RonLugge

Answer 3

你是想完整地分页，还是只需要 这一页 和 下一页？如果是后者，你可以简单地使用 LIMIT 17 并计算结果的行数。如果你得到了 17 行，那么就需要进行分页。

- Ian Elliott

Answer 4

除了其他人提到的单独查询获取计数之外，您还需要为所在页面偏移结果。

$sql .= "posts p, images im, postimages pi WHERE
    i.active = 1 
    AND pi.post_id = p.id
    AND pi.image_id = im.image_id 
    ORDER BY created_at 
    LIMIT ". ($page - 1) * $pagesize .", ". $pagesize;

这里应用普通的SQL注入警告。

- AndyMcKenna

Answer 5

使用两个查询，其中一个是您的查询，另一个是：

SELECT COUNT(primary_key) FROM ... WHERE...

- KM.