如何在MYSQL中的单个查询中计数和限制记录？ [英] How to count and limit record in a single query in MYSQL?

问题描述

我在表中搜索记录如下：

I am searching for records in a table as follows:

SELECT Id, Name FROM my_table WHERE Name LIKE '%prashant%' LIMIT 0, 10;

现在，我添加了LIMIT来维护我的分页。但是当用户搜索单词'prashant'时，我所拥有的记录总数为124，用于'prashant'。但由于限制应用于查询，所以它只在我的PHP脚本中获取10条记录，当我计数的PHP代码中的mysql变量，它返回的总记录发现是10。

Now, I am adding LIMIT to maintain my paging. But when user searches for word 'prashant' then total records I have is 124 for 'prashant'. But as the limit applied to the query so it only fetches 10 records in my PHP script and when I am count the mysql variable in PHP code it returns total records found is 10.

所以基本上我想计数和限制使用单个查询，通过在上面的查询中做一些修改，我想要总计数（124）的记录。我不想运行一个单独的计数（*）查询，只是计算查询发现的总结果。

So basically I want to count and Limit using a single query, by making some modification in the above query, I want the total count (124) of records. I don't want to run a separate count(*) query for just counting the total result found by the query.

谢谢。

推荐答案

SELECT SQL_CALC_FOUND_ROWS
  Id, Name
FROM my_table
WHERE
  Name LIKE '%prashant%'
LIMIT 0, 10;

# Find total rows
SELECT FOUND_ROWS();

更多信息

这篇关于如何在MYSQL中的单个查询中计数和限制记录？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！