MySQL - 如何计数分页之前的行？ [英] MySQL - How to count rows before pagination?
问题描述
我在寻找使用者的搜寻网页。我有查询查询它们，实际上我可以做的分页与LIMIT startRow，numberRows。但是如何计算在执行分页之前找到的寄存器的总数？我想在搜索页面中添加在搜索中找到的用户数量。
I am making a search page to find users. I have que query to find them and actually I can do the pagination with "LIMIT startRow, numberRows". But how could I count the total number of "registers" found before doing the pagination? I would like to add at my search page, the number of the users found in a search.
我需要类似的内容：第1页，共100页。我实际上有Page 1，但我不知道如何计算分页之前的结果总数。
I need something like: "Page 1 of 100". I actually I have "Page 1" but I don't know how to count the total of results before paginate.
可能需要执行额外的查询， SELECT COUNT（*）？
¿Maybe could be necesary execute an extra query with "SELECT COUNT(*)"? ¿Is there another way to count before pagination to avoid another query?
我使用两个sql查询，一个用于单字，另一个用于多字：
I use two sql queries, one for single word, and another for multiword:
基本sql查询（用于单字和多字搜索）：
Base sql query (for single word and multi word search):
"SELECT * FROM accounts AS A INNER JOIN profiles AS P ON A.account_id = P.account_id "
单字条件：
"WHERE A.username LIKE ? OR P.name LIKE ? OR P.name LIKE ? OR P.surname LIKE ? OR P.surname LIKE ? LIMIT ?,?"
多字条件：
"WHERE CONCAT(P.name, ' ', P.surname) LIKE ? LIMIT ?,?"
非常感谢。
推荐答案
修改查询如下：
SELECT SQL_CALC_FOUND_ROWS * FROM accounts ... LIMIT ...
这将返回与之前相同的有限/偏移结果（例如结果的第一页） ，但如果你立即发送第二个查询到服务器...
This will return the same limited/offset result as before (the first page of results, for example), but if you then immediately send this second query to the server...
SELECT FOUND_ROWS();
服务器将返回一个计数，如果 LIMIT 未应用于上一个查询。
The server will return a count of the total number of rows that would have been returned if the LIMIT had not been imposed on the previous query. There's your total number of results.
这当然意味着您的初始查询需要更长的时间，因为优化器不能采取任何可能允许它的快捷方式 LIMIT 满足时，停止评估行，但在某些情况下，无论如何也没有这样的快捷方式。
This will, of course, mean that your initial query takes longer, because the optimizer can't take any shortcuts that might allow it to stop evaluating rows once the LIMIT is satisfied, but in some cases, no such shortcuts are available anyway.
是做正在尝试完成的官方机制。
This is the "official" mechanism for doing what you are trying to accomplish.
http://dev.mysql.com/doc/refman/5.7/en/information-functions.html#function_found-rows
这篇关于MySQL - 如何计数分页之前的行？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
