mysql count(0),在mySQL / SQL中count（0），count（1）..和count（*）之间有什么区别？

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.

解决方案

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

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

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