mySQL/SQL 中的 count(0), count(1).. 和 count(*) 有什么区别？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18291036/
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
What is the difference between count(0), count(1).. and count(*) in mySQL/SQL?
提问by Dhruv
I was recently asked this question in an interview. I tried this in mySQL, and got the same results(final results). All gave the number of rows in that particular table. Can anyone explain the major difference between them.
我最近在一次采访中被问到这个问题。我在 mySQL 中尝试了这个，并得到了相同的结果（最终结果）。All 给出了该特定表中的行数。谁能解释一下它们之间的主要区别。
回答by Bren
Nothing really, unless you specify a field in a table or an expression within parantheses instead of constant values or *
没什么，除非您在表中指定一个字段或在括号中指定一个表达式而不是常量值或 *
Let me give you a detailed answer. Count will give you non-null record number of given field. Say you have a table named A
让我给你一个详细的答案。Count 将为您提供给定字段的非空记录数。假设您有一个名为 A 的表
select 1 from A
select 0 from A
select * from A
will all return same number of records, that is the number of rows in table A. Still the output is different. If there are 3 records in table. With X and Y as field names
都将返回相同数量的记录，即表 A 中的行数。输出仍然不同。如果表中有3条记录。使用 X 和 Y 作为字段名称
select 1 from A will give you
1
1
1
select 0 from A will give you
0
0
0
select * from A will give you ( assume two columns X and Y is in the table )
X Y
-- --
value1 value1
value2 (null)
value3 (null)
So, all three queries return the same number. Unless you use
因此，所有三个查询都返回相同的数字。除非你使用
select count(Y) from A
since there is only one non-null value you will get 1 as output
由于只有一个非空值，因此您将获得 1 作为输出
回答by fthiella
COUNT(*)will count the number of rows, while COUNT(expression)will count non-null values in expression and COUNT(column)will count all non-null values in column.
COUNT(*)将计算行数，同时COUNT(expression)将计算表达式中的非空值并COUNT(column)计算列中的所有非空值。
Since both 0 and 1 are non-null values, COUNT(0)=COUNT(1)and they both will be equivalent to the number of rows COUNT(*). It's a different concept, but the result will be the same.
由于 0 和 1 都是非空值，COUNT(0)=COUNT(1)因此它们都将等价于行数COUNT(*)。这是一个不同的概念，但结果将是相同的。
回答by eftpotrm
Now - they should all perform identically.
现在 - 他们都应该表现相同。
In days gone by, though, COUNT(1) (or whatever constant you chose) was sometimes recommended over COUNT(*) because poor query optimisation code would make the database retrieve all of the field data prior to running the count. COUNT(1) was therefore faster, but it shouldn't matter now.
但是，在过去的日子里，有时建议使用 COUNT(1)（或您选择的任何常量）而不是 COUNT(*)，因为糟糕的查询优化代码会使数据库在运行计数之前检索所有字段数据。因此 COUNT(1) 更快，但现在应该无关紧要。
回答by Lukas Eder
Since the expression 1is a constant expression, they should always produce the same result, but the implementations might differ as some RDBMS might check whether 1 IS NULLfor every single row in the group. This is still being done by PostgreSQL 11.3 as I have shown in this article.
由于表达式1是一个常量表达式，它们应该总是产生相同的结果，但实现可能会有所不同，因为某些 RDBMS 可能会检查是否1 IS NULL针对组中的每一行。正如我在本文中所展示的，这仍然由 PostgreSQL 11.3 完成。
I've benchmarked queries on 1M rows doing the two types of count:
我对 100 万行的查询进行了两种类型的计数：
-- Faster
SELECT count(*) FROM t;
-- 10% slower on PostgreSQL 11.3
SELECT count(1) FROM t;
One reason why people might use the less intuitive COUNT(1)could be that historically, it was the other way round.
人们可能使用不那么直观的一个原因COUNT(1)可能是历史上，情况正好相反。
回答by Abhijit
1) count(any integer value) is faster than count(*) ---> gives all counts including null values
1) count(任何整数值)比count(*)快--->给出包括空值在内的所有计数
2) count(column_name) omits null
2) count(column_name) 省略 null
Ex-->
前-->
column name=> id
列名=> id
values => 1 1 null null 2 2
值 => 1 1 null null 2 2
==> count(0), count(1), count(*) -----> result is 6 only
==> count(0), count(1), count(*) -----> 结果只有 6
==> count(id) ----> result is 4
==> count(id) ----> 结果是 4
回答by Peter Csak
The result will be the same, however COUNT(*)is slower on a lot of production environments today, because in production the db engines can live decades. I prefer to use COUNT(0), someone use COUNT(1), but definitely not COUNT(*)even if its lets say safe to use on modern db engines, I would not depend on the engine, especially if its only one character difference, also the code will be more portable.
结果将是相同的，但是COUNT(*)在当今的许多生产环境中速度较慢，因为在生产中数据库引擎可以存活数十年。我更喜欢使用COUNT(0)，有人使用COUNT(1)，但绝对不是，COUNT(*)即使可以说在现代数据库引擎上使用安全，我也不会依赖引擎，特别是如果它只有一个字符差异，代码也会更便携。
回答by isha verma
COUNT(*), COUNT(1) , COUNT(0), COUNT('Y') , ...
All of the above return the total number of records (including the null ones).
以上所有都返回记录的总数（包括空记录）。
But COUNT('any constant')is faster than COUNT(*).
但COUNT('any constant')比COUNT(*).
回答by Vijay Vj
Let's say we have table with columns
假设我们有带列的表
Table
-------
col_A col_B
System returns all column (null and non-null) values when we query
当我们查询时，系统返回所有列（空和非空）值
select col_A from Table
System returns column values which are non-null when we query
当我们查询时，系统返回非空的列值
select count(col_A) from Table
System returns total rows when we query
查询时系统返回总行数
select count(*) from Table
