mysql count函数 null_MySql中COUNT(XXX OR NULL)

经常看见有人在代码中写出SELECT COUNT(XXX OR NULL) FROM XXX, 这样的sql语句. 撇开性能不谈, 咱先来看看, 这个所谓的COUNT(XXX OR NULL)到底是个什么鬼.

假设我们有一张名为employee的员工表, 该表共有两个字段, id和gender(性别). 其中我们约定, 当gender=1时表示该员工性别为男, gender=2时该员工性别为女(在数据库设计中建议大家多使用约定而不要使用枚举类型). 此时, 我们业务需要统计男员工的数量, 一般人都会写出这样的SQL语句来达到目的: SELECT COUNT(*) FROM employee WHERE gender = 1. 当然也有一些”大牛”喜欢用这样的SQL语句: SELECT COUNT(gender = 1 OR NULL) FROM employee. 首先, 要肯定的一点是, 这两种处理方式都可以达到统计男员工的目的. 但是关于后一种表达很多人就搞不明白了, 为什么不是SELECT COUNT(gender = 1) FROM employee, 而是要多加个OR NULL呢? 如果你在数据库里实际测试一下就会发现, SELECT COUNT(gender = 1) FROM employee返回的总是员工的总人数而不是男员工的人数.

其实, COUNT这个函数其中传入的参数只要不是null, 都会造成最终结果+1. 很多人都误以为, count是用来统计行数的, 这种说法只说对了一半. count有两个作用一个是统计行数不错(e. g count(*)的用法) , 还有一个就是统计列值. 在统计列值时要求列值是非空的(不统计null). 如果在count()的括号里指定了列值或列值的表达式, 则统计的是这个表达式有值的结果数. 所以, , 现在就能看出为什么SELECT COUNT(gender = 1) FROM employee是错误的了吧? 因为无论gender的值是1还是2, gender = 1这个表达式总有值, 1= 1是1, 2 = 1是0, 在怎么也不肯能是null吧! 所以以上语句总返回员工的总人数, 而不是你想要的男员工的数量.

可能到这里还没有能完全解决你的疑惑, 因为你现在只知道为啥gender = 1不可以达到目的, 却可能还是搞不明白为什么gender = 1 OR NULL就可以达到目的, 对吧? 我想大部分有疑惑的人可能并没有理解 gender = 1 OR NULL 这个表达式的意思, 如果我把它改写下你或许就明白了: (gender = 1) OR NULL, 而很多人其实误以为是这样的gender = 1 OR gender IS NULL. 亲, 这可是两个完全不同的表达式啊!!!! 第二个表达式不用我多说了, 当gender为1时, 整个表达式为1, 当gender为2时, 整个表达式为0. 第一个表达式其实也很容易看啊, 当gender为1时, gender = 1 OR NULL —–> 1=1 OR NULL —-> 1 OR NULL —-> 1, 当gender为2时, gender = 1 OR NULL —–> 2 = 1 OR NULL —-> 0 OR NULL —-> NULL. 可能做后台(尤其是用java的…)的同学, 就纳闷了为什么0或null就是null呢? 其实你要是了解过一点JavaScript这种弱类型的语言就不奇怪了null也是可以做逻辑运算的, 不像java里null || false 就报错了->__->.

操作数一

操作

操作数二

结果

0

OR

null

null

1

OR

null

1

null

OR

null

null

0

AND

null

0

1

AND

null

null

null

AND

null

null

如果不确定可以通过SQL语句select (0 or null) from dual试一下就行.

咱们再来看看COUNT(XXX OR NULL)性能方面的问题. 就刚刚那个例子, 咱们就只讨论已经在gender上建立过btree索引了哈, 没有索引情况下, 当然是扫全表啦(innodb没改过源码情况下, 强迫症多啰嗦一下). 现在explain一下该语句 explain SELECT COUNT(gender = 1 OR NULL) FROM employee.

id

select_type

table

type

…

1

SIMPLE

employee

index

…

从type不难看出, 扫了一遍索引, 性能相当差. rows字段应该就是你数据总记录条数(这里因每个库数据量不同, rows也会不同, 这里不贴出来做参考了, 应该就是等于数据库条目总量).

再来看看 SELECT COUNT(*) FROM employee WHERE gender = 1这个普通的sql语句,

id

select_type

table

type

…

1

SIMPLE

employee

ref

…

type是ref, 不用我多说了吧~, 性能完爆第一条啊, 用膝盖想想就知道, 第二种SQL语句遍历的条目少啊, 因为mysql会先通过where后面的条件利用索引找到符合的条目, 再传入count函数操作, 而不是直接遍历整个索引再去代入count中的表达式, 这应该很容易想通的把~.

但是我们应该全盘否定COUNT(XXX OR NULL)这样的写法么? 不是的, 比如现在我有这样的一个需求, 既要统计男员工的数量也要统计女员工的数量, 怎么办呢? 最简单的办法当然是查询两边数据库啦, 不过我们哪会用这么low 的办法呢. 当然是写在一条SQL语句里, 让数据库一次就统计完成, 性能就不会损耗在多次数据库连接上了.

SELECT COUNT(gender = 1 OR NULL) AS male, COUNT(gender = 2 OR NULL) AS female FROM employee

当然这也不是绝对快的, 如果你用的数据库存储引擎是MyISAM, 类似select count(*) from XXX 的SQL语句其实是一步到位的(前提是没有where语句), 这是某些存储引擎特性. 所以有时候查两次数据库会比一次完成的SQL语句还要快哦! 还有一点需要点一下, 当你的业务中真涉及到类似的需求, 建议每次都写上where 语句限定遍历的条目, 比如这里最好写成

SELECT COUNT(gender = 1 OR NULL) AS male, COUNT(gender = 2 OR NULL) AS female FROM employee WHEHE gender IN (1,2)

当然在这里这个where语句就是画蛇添足了, 因为世界上除了男人就是女人(强迫症再多说一句, 还有性染色体为XXY的人…), 哪还有第三种性别啊?

不过还是建议养成习惯把它写上.

题外吐槽: 不知道为什么身边总有那些伪”大牛”总是写一些看似高深莫测, 实则其烂无比的代码, 网上看来的伎俩一知半解, 生搬硬套的使用在自己的项目中, 真真是贻笑大方!