当我只想在搜索查询中获得结果数量时，MySQL是否接受ORDER BY“无”LIMIT“全部”？

I have ported a search from Coldfusion into a MySQL stored procedure.

我已将Coldfusion的搜索移植到MySQL存储过程中。

The actual search is run twice on the site. Once to get the number of records, the 2nd time to get the actual results to display.

实际搜索在网站上运行两次。一旦得到记录数，第二次得到实际结果显示。

So both MySQL syntax differ in the last two lines:

因此，MySQL语法在最后两行中有所不同：

Get no of records:

没有记录：

 SELECT COUNT(*) ...
 GROUP BY a, b, c
 HAVING ...

Actual results:

实际结果：

 SELECT "rows"...
 GROUP BY a, b, c
 HAVING ... 
 ORDER BY var1, var2
 LIMIT var_start, var_end

My question:
Since I need to run this twice, is there a way to at least use the same stored procedure = can I add default values to order/grouping "ORDER BY 'nothing' LIMIT 'all', and a parameter to the SELECT?

我的问题：因为我需要运行两次，是否有办法至少使用相同的存储过程=我可以添加默认值来命令/分组“ORDER BY'not''LIMIT'all'，以及SELECT的参数？

Sort of like an if statement

有点像if语句

WHERE 1
AND IF( var_x = '', '.', var_x = some_value )

Thanks for input!

感谢您的投入！

3 个解决方案

#1

The first query is wrong - you shouldn't ask the MySQL engine to actually return the rows, and then count them, you should ask MySQL to count them directly:

第一个查询是错误的 - 您不应该要求MySQL引擎实际返回行，然后计算它们，您应该要求MySQL直接计算它们：

SELECT COUNT(*)
FROM ...
GROUP BY a, b, c
HAVING ...

Trying to shoe-horn both record count and record retrieval in the same SQL statement or stored procedure is an unnecessary optimisation.

试图在同一个SQL语句或存储过程中对记录计数和记录检索进行擦除是不必要的优化。

#2

Why don't you use extra parameters with the use of count(*) if it's just the number of rows you want.

如果它只是你想要的行数，为什么不使用count（*）来使用额外的参数。

SELECT count(a) as number_of_a, count(b) as number_of b ...
 GROUP BY a, b, c
 HAVING ... 
 ORDER BY var1, var2
 LIMIT var_start, var_end

#3

Have you looked at sql_calc_found_rows?

你看过sql_calc_found_rows吗？

select sql_calc_found_rows mytable.* ...

#1