如何在使用UNION的查询上实现SQL_CALC_FOUND_ROWS？

Mysql, I am using SQL_CALC_FOUND_ROWS to get the total number of possible records.
How do I use it on UNION?
The only thing that works for me right now, which seems somewhat off is:

Mysql,我使用SQL_CALC_FOUND_ROWS来获取可能的记录总数。我如何在UNION上使用它?对我来说唯一有用的东西,似乎有点偏离:

SELECT COUNT(*)
FROM(SELECT * FROM t1 UNION SELECT * FROM t2) A;

SELECT SQL_CALC_FOUND_ROWS *
FROM(SELECT * FROM t1 UNION SELECT * FROM t2) A;

2 个解决方案

#1

From the FOUND_ROWS() documentation:

从FOUND_ROWS()文档:

The use of SQL_CALC_FOUND_ROWS and FOUND_ROWS() is more complex for UNION statements than for simple SELECT statements, because LIMIT may occur at multiple places in a UNION. It may be applied to individual SELECT statements in the UNION, or global to the UNION result as a whole.

对于UNION语句,使用SQL_CALC_FOUND_ROWS和FOUND_ROWS()比使用简单的SELECT语句更复杂,因为LIMIT可能出现在UNION中的多个位置。它可以应用于UNION中的各个SELECT语句,也可以应用于整个UNION结果的全局。

The intent of SQL_CALC_FOUND_ROWS for UNION is that it should return the row count that would be returned without a global LIMIT. The conditions for use of SQL_CALC_FOUND_ROWS with UNION are:

SQL_CALC_FOUND_ROWS对UNION的意图是它应该返回在没有全局LIMIT的情况下返回的行计数。使用UNION的SQL_CALC_FOUND_ROWS的条件是:

The SQL_CALC_FOUND_ROWS keyword must appear in the first SELECT of the UNION.

SQL_CALC_FOUND_ROWS关键字必须出现在UNION的第一个SELECT中。

The value of FOUND_ROWS() is exact only if UNION ALL is used. If UNION without ALL is used, duplicate removal occurs and the value of FOUND_ROWS() is only approximate.

仅当使用UNION ALL时,FOUND_ROWS()的值才是精确的。如果使用UNION without ALL,则会发生重复删除,并且FOUND_ROWS()的值仅为近似值。

If no LIMIT is present in the UNION, SQL_CALC_FOUND_ROWS is ignored and returns the number of rows in the temporary table that is created to process the UNION.

如果UNION中不存在LIMIT,则忽略SQL_CALC_FOUND_ROWS并返回为处理UNION而创建的临时表中的行数。

#2

You must specify SQL_CALC_FOUND_ROWS on the first SELECT in the UNION only; you don't actually need an outer SELECT query as you do when using COUNT(*).

您必须仅在UNION中的第一个SELECT上指定SQL_CALC_FOUND_ROWS;您实际上不需要像使用COUNT(*)时那样使用外部SELECT查询。

By way of example, let's say we have the following LIMITed query:

举个例子,假设我们有以下LIMITed查询:

SELECT * FROM my_table1 
UNION ALL 
SELECT * FROM my_table2 
UNION ALL 
SELECT * FROM my_table3
LIMIT 0,10;

We can simply write:

我们可以简单地写:

SELECT SQL_CALC_FOUND_ROWS * FROM my_table1 
UNION ALL 
SELECT * FROM my_table2 
UNION ALL 
SELECT * FROM my_table3
LIMIT 0,10;

We then call:

然后我们打电话:

SELECT FOUND_ROWS();

This avoids some overhead of having the outer query mentioned in your question and in the comments of Joe Stefanelli's answer (although I'm not entirely convinced it would be a noticeable difference).

这避免了在你的问题和Joe Stefanelli的回答的评论中提到的外部查询的一些开销(尽管我并不完全相信它会有明显的区别)。

I think it's worth re-iterating that this will only work if you are using UNION ALL rather than UNION - This is because the row count is calculated before duplicates are removed, meaning you'll get the same result from FOUND_ROWS() as you would if you had used UNION ALL.

我认为值得重新思考,只有在使用UNION ALL而不是UNION时才会有效 - 这是因为在删除重复项之前计算行数,这意味着您将获得与FOUND_ROWS()相同的结果如果您使用了UNION ALL。

#1