MySQL COUNT() 和空值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1354060/
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
MySQL COUNT() and nulls
提问by iceangel89
Am I correct in saying:
我说的对吗：
COUNT(expr)
WHERE expr IS NOT *
Will count only non nulls?
只会计算非空值吗？
Will COUNT(*)always count all rows? And What if all columns are null?
将COUNT(*)始终计算所有行？如果所有列都为空怎么办？
回答by Godeke
Correct. COUNT(*) is all rows in the table, COUNT(Expression) is where the expression is non-null only.
正确的。COUNT(*) 是表中的所有行， COUNT(Expression) 是表达式仅非空的地方。
If all columns are NULL (which indicates you don't have a primary key, so this shouldn't happen in a normalized database) COUNT(*) stillreturns all of the rows inserted. Just don't do that.
如果所有列都是 NULL（这表明您没有主键，所以这不应该发生在规范化的数据库中） COUNT(*)仍然返回所有插入的行。不要那样做。
You can think of the * symbol as meaning "in the table" and not "in any column".
您可以将 * 符号视为“在表格中”而不是“在任何列中”的意思。
This is covered in the MySQL Reference Manual.
回答by Roey
If you want to count NULLs as well, try
如果您还想计算 NULL，请尝试
SELECT COUNT(IFNULL(col, 1)) FROM table;
回答by Sergei
just checked:
刚刚检查：
select count(*)
returns 1with one record filled with NULLs
返回1，其中一条记录填充为 NULL
select count(field)
returns 0.
返回0。
I don't see the point in the record with NULL values. Such record must not exist.
我没有看到 NULL 值的记录点。此类记录不得存在。
回答by Michael Krelin - hacker
count(*)is not for non-null columns, it's just the way to ask to count all rows. Roughly equivalent to count(1).
count(*)不适用于非空列，这只是要求计算所有行的方法。大致相当于count(1).
回答by Mario Cespedes
Using MySQL I found this simple way:
使用 MySQL 我发现了这个简单的方法：
SELECT count(ifnull(col,1)) FROM table WHERE col IS NULL;
This way will not work:
这种方式行不通：
SELECT count(col) FROM table WHERE col IS NULL;
