SQL count(*) vs count(column-name) - 哪个更正确？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3003457/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
count(*) vs count(column-name) - which is more correct?
提问by bread
Does it make a difference if you do count(*)vs count(column-name)as in these two examples?
如果你在这两个例子中做count(*)vs有什么不同count(column-name)吗？
I have a tendency to always write count(*)because it seems to fit better in my mind with the notion of it being an aggregate function, if that makes sense.
我有一种总是写作的倾向，count(*)因为它似乎更适合我认为它是一个聚合函数的概念，如果这是有道理的。
But I'm not sure if it's technically best as I tend to see example code written without the *more often than not.
但我不确定它在技术上是否是最好的，因为我往往会经常看到没有编写的示例代码*。
count(*):
数数（*）：
select customerid, count(*), sum(price)
from items_ordered
group by customerid
having count(*) > 1;
vs. count(column-name):
与计数（列名）：
SELECT customerid, count(customerid), sum(price)
FROM items_ordered
GROUP BY customerid
HAVING count(customerid) > 1;
回答by gbn
COUNT(*)counts all rowsCOUNT(column)counts non-NULLs onlyCOUNT(1)is the same asCOUNT(*)because 1 is a non-null expressions
COUNT(*)计算所有行COUNT(column)仅计算非 NULLCOUNT(1)是一样的，COUNT(*)因为 1 是一个非空表达式
Your use of COUNT(*)or COUNT(column)should be based on the desired output only.
您在使用COUNT(*)或COUNT(column)应根据所需的输出只。
回答by nickf
This applies to MySQL. I'm not sure about the others.
这适用于 MySQL。我不确定其他人。
The difference is:
区别在于：
COUNT(*)will count the number of records.COUNT(column_name)will count the number of records where column_name is not null.
COUNT(*)将计算记录数。COUNT(column_name)将计算 column_name 不为空的记录数。
Therefore COUNT(*)is what you should use. If you're using MyISAM and there is no WHEREclause, then the optimiser doesn't even have to look at the table, since the number of rows is already cached.
因此，这COUNT(*)是您应该使用的。如果您使用的是 MyISAM 并且没有WHERE子句，那么优化器甚至不必查看表，因为行数已经被缓存。
回答by Dean Harding
When it's an identifier (and guaranteed to be non-NULL) then it probably doesn't matter.
当它是一个标识符（并保证是非NULL）时，它可能无关紧要。
However, there isa difference between COUNT(*)and COUNT(column)in general, in that COUNT(column)will return a count of the non-NULLvalues in the column. There is also the COUNT(DISTINCT column)variant which returns the number of unique, non-NULLvalues.
但是，有是之间的差异COUNT(*)，并COUNT(column)在一般情况下，这COUNT(column)将返回非计数NULL列中的值。还有一个COUNT(DISTINCT column)变体，它返回唯一的、非NULL值的数量。
回答by zed_0xff
Generally it's the same, but in details AFAIK "count(*)" is betterb/c "count(columnname)" forces DB to execute a little more code to lookup that column name (but not necessary though).
通常它是相同的，但在细节上 AFAIK "count(*)" 更好b/c "count(columnname)" 强制 DB 执行更多代码来查找该列名（但不是必需的）。
回答by Tommi
Yes, there is possible difference in performance. Depending on your query, and the indexing of the table in question, it can be quicker to get the count from the index instead of going to table for the data. Thus you probably should specify the field name, instead of using *.
是的，性能可能存在差异。根据您的查询和相关表的索引，从索引中获取计数可能会更快，而不是转到表中获取数据。因此，您可能应该指定字段名称，而不是使用 *。
