在SQL中count(*)和count()有区别吗?

时间:2022-01-17 15:22:40

Pretty self explanatory question. Is there any reason to use one or the other?

漂亮的自我解释性的问题。有什么理由使用其中一个或另一个?

5 个解决方案

#1


46  

Count(*) counts all records, including nulls, whereas Count(fieldname) does not include nulls.

Count(*)计算所有记录,包括nulls,而Count(fieldname)不包含nulls。

#2


6  

Select count(*) selects any row, select count(field) selects rows where this field is not null.

选择count(*)选择任何行,选择count(字段)选择该字段不为空的行。

#3


3  

If you want to improve performance (i.e. be a complete performance Nazi), you might want to do neither.

如果你想提高性能(比如,做一个完全的纳粹分子),你可能也不想这样做。

Example:

例子:

SELECT COUNT(1) FROM MyTable WHERE ...

#4


1  

This puzzled me for a while too.

这也让我困惑了一阵子。

In MySQL at least COUNT(*) counts the number of rows where every (*) value in the row is not null. Just COUNTing a column will count the number of rows where that column is not null.

在MySQL中,至少COUNT(*)计算行中每个(*)值不为空的行数。仅仅数一列就会数出列不为空的行数。

In terms of performance using a single column would be slightly faster,

就性能而言,使用单个列会稍微快一些,

#5


-1  

count(*) is faster if table type is MyISAM with no WHERE statement. With WHERE the speed will be the same for MyISAM and InnoDB.

如果表类型是MyISAM,而没有WHERE语句,则count(*)更快。MyISAM和InnoDB的速度是一样的。

#1


46  

Count(*) counts all records, including nulls, whereas Count(fieldname) does not include nulls.

Count(*)计算所有记录,包括nulls,而Count(fieldname)不包含nulls。

#2


6  

Select count(*) selects any row, select count(field) selects rows where this field is not null.

选择count(*)选择任何行,选择count(字段)选择该字段不为空的行。

#3


3  

If you want to improve performance (i.e. be a complete performance Nazi), you might want to do neither.

如果你想提高性能(比如,做一个完全的纳粹分子),你可能也不想这样做。

Example:

例子:

SELECT COUNT(1) FROM MyTable WHERE ...

#4


1  

This puzzled me for a while too.

这也让我困惑了一阵子。

In MySQL at least COUNT(*) counts the number of rows where every (*) value in the row is not null. Just COUNTing a column will count the number of rows where that column is not null.

在MySQL中,至少COUNT(*)计算行中每个(*)值不为空的行数。仅仅数一列就会数出列不为空的行数。

In terms of performance using a single column would be slightly faster,

就性能而言,使用单个列会稍微快一些,

#5


-1  

count(*) is faster if table type is MyISAM with no WHERE statement. With WHERE the speed will be the same for MyISAM and InnoDB.

如果表类型是MyISAM,而没有WHERE语句,则count(*)更快。MyISAM和InnoDB的速度是一样的。