从查询返回的MySQL总结果有一个限制:FOUND_ROWS错误 [英] Getting total mysql results returned from query with a limit: FOUND_ROWS error

问题描述

我有这个mysql查询:

I have this mysql query:

SELECT Files.GUID, Files.Name, Files.Type, Files.Visibility, Files.CreationDate, Files.OwnerUser, Date, BackupsCount
FROM Files
  LEFT JOIN (
    SELECT FileGUID, Date, COUNT(*) AS BackupsCount
    FROM Versions
    GROUP BY FileGUID
  ) Versions ON Files.GUID = Versions.FileGUID
WHERE Files.ParentFolder = '96251A8B-B2A8-416B-92D7-3509E6A645C7'
ORDER BY Files.Type DESC, CreationDate ASC
LIMIT 0, 50

我想知道多少查询将返回如果LIMIT指令不存在，我搜索了一下，发现我需要添加SELECT SQL_CALC_FOUND_ROWS和SELECT FOUND_ROWS();

I'd like to know how many record this query would return if the LIMIT directive was not there, I googled and found that I needed to add SELECT SQL_CALC_FOUND_ROWS and SELECT FOUND_ROWS();

我的问题是:我无法正常工作:

SELECT SQL_CALC_FOUND_ROWS * FROM (
SELECT Files.GUID, Files.Name, Files.Type, Files.Visibility, Files.CreationDate, Files.OwnerUser, Date, BackupsCount
FROM Files
  LEFT JOIN (
    SELECT FileGUID, Date, COUNT(*) AS BackupsCount
    FROM Versions
    GROUP BY FileGUID
  ) Versions ON Files.GUID = Versions.FileGUID
WHERE Files.ParentFolder = '96251A8B-B2A8-416B-92D7-3509E6A645C7'
ORDER BY Files.Type DESC, CreationDate ASC
LIMIT 0, 50)

-- Find total rows
SELECT FOUND_ROWS()

假设这是个好主意(我的意思是做一个查询而不是两个单独的查询)，我怎样才能使FOUND_ROWS()与此查询一起使用?

Assuming this is good idea (I mean doing one query vs two separate ones), how can I get the FOUND_ROWS() to work with this query?

推荐答案

这个想法是正确的，第二个查询几乎是瞬时的.但是您只需要在SELECT之后添加关键字，就可以了:

The idea is correct, and the second query is almost instantaneous. But you have to add the keyword after the SELECT, no more:

SELECT Files.GUID, Files.Name, Files.Type, ...

成为

SELECT SQL_CALC_FOUND_ROWS Files.GUID, Files.Name, Files.Type, ...

然后将填充找到的行数，您将可以恢复它.

Then the number of found rows will be populated and you'll be able to recover it.

如果您要运行一个检索查询，但是能够执行多个查询，则可以执行此操作(这是两个查询):

If you want to run a single retrieval query, but you are able to execute more than one query, then you can do this (it is two queries):

SELECT COUNT(*) INTO @FOUNDROWS FROM ( YOUR_QUERY_WITHOUT_SELECT ) AS orig;

，并通过在字段中添加@FOUNDROWS as foundrows来更改您的检索查询:

and alter your retrieval query by adding @FOUNDROWS as foundrows to the fields:

SELECT ...YOUR FIELDS..., @FOUNDROWS AS foundrows FROM...

最后，您可以将JOIN作为包含两个查询的单个查询运行:

Finally you can run a JOIN as a single query containing the two queries:

SELECT ALL_YOUR_FIELDS, counter.FOUND_ROW_NUMBER FROM
( YOUR_QUERY_WITH_LIMIT ) AS limited
JOIN ( SELECT COUNT(*) AS FOUND_ROW_NUMBER FROM YOUR_QUERY ) AS counter;

这篇关于从查询返回的MySQL总结果有一个限制:FOUND_ROWS错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！