mysql中count(1)会报错么,在同一select子句中使用count（*）和*会产生错误。为什么？...-优快云博客

本文介绍了在MySQL中使用COUNT(*)时遇到的语法错误，以及错误的原因。当你在SELECT语句中同时使用COUNT(*)和*时，数据库无法正确处理，通常需要配合GROUP BY子句来指定聚合的列。如果不使用GROUP BY，应确保查询中没有其他非聚合列。示例展示了如何正确地进行计数查询。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

mysql> select COUNT(*), * from help_keyword;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '* from help_keyword' at line 1

This one does not:

mysql> select COUNT(*), help_keyword.* from help_keyword;

+----------+-----------------+------+

| COUNT(*) | help_keyword_id | name |

+----------+-----------------+------+

| 452 | 0 | JOIN |

+----------+-----------------+------+

1 row in set (0.00 sec)

Why is that?

解决方案

Because by using Count(*) you tell the database that you want an aggregation query, and therefore any columns or expressions in the select that do not include some aggregaton function (like count, min, max, etc.) are used to define the "buckets" that the aggregation will be performed on.. If there are any such expressions, you generally need to also include a Group By clause to explicitly list these columns/expressions. By including ALL the columns of the table, (which is what * does), you are saying aggregate over EVERY row in the table, which does not make sense. (In any proper relational tbale, there would always be only one row that matches every value for that row)