主页 / 问题 / 743468
Accepted
Alexxosipov
Asked:2020-11-12 09:41:46 +0000 UTC2020-11-12 09:41:46 +0000 UTC

如何从 mysql 中找出落入 where 子句的行数，避免 LIMIT 和 OFFSET？

  • 772

有一个SQL查询：

SELECT  p.link AS link,
        p.id AS id, 
        p.title AS title,
        p.can_use_promocode AS can_use_promocode,                        
        p.price AS price,
        p.sale_price AS sale_price,
        p.sale_percent AS sale_percent,
        p.is_sale AS is_sale,
        p.photo AS photo,
        c1.title AS c1_title,
        c1.link AS c1_link,
        c2.title AS c2_title,
        c2.link AS c2_link
        FROM products AS p
        LEFT JOIN categories AS c1 ON p.category_parent_id = c1.id
        LEFT JOIN categories AS c2 ON p.category_children_id = c2.id 
        WHERE p.category_parent_id = :parent_id AND p.category_children_id = :children_id 
        AND ((p.is_sale = 0 AND p.price >= 4000) OR (p.is_sale = 1 AND p.sale_price >= 4000))  
        ORDER BY p.id DESC LIMIT 25 OFFSET 0

它输出特定分页页面的数据，但是，要构建分页，我还需要知道总行数（即忽略 LIMIT 和 OFFSET）。问：怎么办？

sql

4 个回答

  1. Best Answer

    可以在单个表传递中获取同一查询中的记录总数。这当然会比原始查询慢，但可能比运行单独的计数查询更快。而且我认为保存收到的金额而不为数据库中的后续页面重新计算它是值得的，因为它仍然需要很多时间。

    SELECT  p.link AS link, p.id AS id, p.title AS title,
        p.can_use_promocode AS can_use_promocode, p.price AS price,
        p.sale_price AS sale_price, p.sale_percent AS sale_percent,
        p.is_sale AS is_sale, p.photo AS photo, c1.title AS c1_title,
        c1.link AS c1_link, c2.title AS c2_title, c2.link AS c2_link,
        @cnt as total_count
   FROM (select p.*, @cnt:=@cnt+1
           from products AS p,(select @cnt:=0) X
          WHERE p.category_parent_id = :parent_id AND p.category_children_id =:children_id 
            AND (  (p.is_sale = 0 AND p.price >= 4000)
                OR (p.is_sale = 1 AND p.sale_price >= 4000))
        ) p
   LEFT JOIN categories AS c1 ON p.category_parent_id = c1.id
   LEFT JOIN categories AS c2 ON p.category_children_id = c2.id 
  ORDER BY p.id DESC LIMIT 25 OFFSET 0
    • 3

  2. 从物理的角度来看这个问题：为了找出有多少行匹配你的查询，DBMS 仍然必须不受限制地执行完整的查询。显而易见的方法是调用COUNT您的请求：

    SELECT COUNT(*)
FROM (
    SELECT /* ваш запрос */
    LIMIT /* абсолютное ограничение на пагинацию */
) q

    如果用户在大多数情况下只看第一页，是否要执行完整查询是一个悬而未决的问题：这取决于数据库的大小、负载等。DBMS 是否会缓存整个结果一个查询，它会用于后续的分页吗？取决于 DBMS。

    • 1

  3. 如果是 MySQL，那么：https ://dev.mysql.com/doc/refman/5.7/en/select.html

    SQL_CALC_FOUND_ROWS告诉 MySQL 计算结果集中有多少行，忽略任何 LIMIT 子句。然后可以使用SELECT FOUND_ROWS()检索行数

    所以就

    SELECT SQL_CALC_FOUND_ROWS p.link AS link,
    p.id AS id, 
-- и далее по тексту
-- а потом
SELECT FOUND_ROWS();
    • 1

  4. 您可以将此列添加到您的查询中：

    (select count(*)
FROM products AS p
        LEFT JOIN categories AS c1 ON p.category_parent_id = c1.id
        LEFT JOIN categories AS c2 ON p.category_children_id = c2.id 
        WHERE p.category_parent_id = :parent_id AND p.category_children_id = :children_id 
        AND ((p.is_sale = 0 AND p.price >= 4000) OR (p.is_sale = 1 AND p.sale_price >= 4000))
)  qty
    • 0

相关问题