php – 在mySQL中使用FOUND_ROWS()的错误结果

发布时间：2020-12-13 21:41:32 所属栏目：PHP教程来源：网络整理

导读：我总共有6行.当我进行查询(比如表格中的SELECT *)时,我有 LIMIT 3 = FOUND_ROWS()给出3 =检索3行 LIMIT 1,3 = FOUND_ROWS()给出4 =检索3行 LIMIT 2,3 = FOUND_ROWS()给出5 =检索3行 LIMIT 3,3 = FOUND_ROWS()给出6 =检索3行 LIMIT 4,3 = FOUND_ROWS()给出6 =

我总共有6行.当我进行查询(比如表格中的SELECT *)时,我有

> LIMIT 3 => FOUND_ROWS()给出3 =>检索3行
> LIMIT 1,3 => FOUND_ROWS()给出4 =>检索3行
> LIMIT 2,3 => FOUND_ROWS()给出5 =>检索3行
> LIMIT 3,3 => FOUND_ROWS()给出6 =>检索3行
> LIMIT 4,3 => FOUND_ROWS()给出6 =>检索到2行

知道这种奇怪行为的原因是什么？

SQL QUERY

SELECT `places`.*,`category`.*,COUNT(places_reviews.place_id) AS num_reviews,(places_popularity.rating_1 + 2*places_popularity.rating_2 + 3*places_popularity.rating_3 + 4*places_popularity.rating_4 + 5*places_popularity.rating_5)/(places_popularity.rating_1 + places_popularity.rating_2 + places_popularity.rating_3 + places_popularity.rating_4 + places_popularity.rating_5) AS average_rating,FOUND_ROWS() AS num_rows FROM (`places`) JOIN `category` ON `places`.`category_id` = `category`.`category_id` LEFT JOIN `places_reviews` ON `places_reviews`.`place_id` = `places`.`id` LEFT JOIN `places_popularity` ON `places_popularity`.`place_id` = `places`.`id` WHERE `places`.`category_id` = 1 AND `places`.`name` LIKE '%%' GROUP BY `places`.`id` ORDER BY `id` desc LIMIT 3

或者在一个街区：

SELECT `places`.*,FOUND_ROWS() AS num_rows FROM (`places`) 
JOIN `category` ON `places`.`category_id` = `category`.`category_id` 
LEFT JOIN `places_reviews` ON `places_reviews`.`place_id` = `places`.`id` 
LEFT JOIN `places_popularity` ON `places_popularity`.`place_id` = `places`.`id` 
WHERE `places`.`category_id` = 1 
    AND `places`.`name` LIKE '%%' 
GROUP BY `places`.`id` 
ORDER BY `id` desc LIMIT 3

解决方法

编辑：

这就是你要找的东西：
http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_found-rows

所以在你的查询中：

SELECT sql_calc_found_rows …..

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!