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

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)