mysql踩坑之count与distinct返回0、count(distinct null)返回0、count与distinct联合使用的坑、count(null)返回0

以下内容基于mysql8.0进行讲解

一、背景

mysql中count与distinct组合使用的时候，如果distinct的列为null, 则返回了0，这与我的预期结果不符，因此研究一下。

1. 数据准备

drop table user;
CREATE TABLE `user` (
  `id` int NOT NULL PRIMARY KEY AUTO_INCREMENT comment '主键id',
  `name` varchar(10)  DEFAULT NULL COMMENT '姓名',
  `class` varchar(10)  DEFAULT NULL COMMENT '班级'
) ENGINE=InnoDB AUTO_INCREMENT=1;

insert into user(name,class) values
('a',null),
('b',null),
('c',null);

2. 问题复现
我们通过对class进行去重，预期结果返回1， 但是实际返回了0.

select count(distinct class) from user;

试试使用name，class一起去重，同样返回了0

select count(distinct name,class) from user;

二、问题原因

1. COUNT（）、MIN（）和SUM（）忽略NULL值。

2. COUNT（*）是一个例外，它计算行数，而不是单个列值。

三、解决方法

那么当count与distinct需要一起使用的时候，如何将null值计算进去呢？

方式一：子查询

可以使用子查询的方式，先对数据进行去重，然后再count(*) 即可得到结果。

select count(*) from (select distinct name,class from user) as tmp

方式二：对空值进行转换

可以通过IFNULL函数对null值进行判断，当为null时返回其他字符，这样就可以参与计算了。 注意：IFNULL(class,'0')返回的0不要与真实数据重复，否则就被去重掉了

 select count(distinct name,IFNULL(class,'0')) from user;