这篇文章主要介绍“mysql踩坑之count distinct多列问题怎么解决”的相关知识,小编通过实际案例向大家展示操作过程,操作方法简单快捷,实用性强,希望这篇“mysql踩坑之count distinct多列问题怎么解决”文章能帮助大家解决问题。
复现的测试数据库如下所示:
CREATE TABLE `test_distinct` ( `id` int(11) NOT NULL AUTO_INCREMENT, `a` varchar(50) CHARACTER SET utf8 DEFAULT NULL, `b` varchar(50) CHARACTER SET utf8 DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;
表内测试数据如下,现在我们需要统计这三列去重后的列的数量。
小伙伴给了我四条用来定位问题的查询语句
SELECT COUNT(*) AS cnt FROM test_distinct; SELECT COUNT(DISTINCT id, a, b) as cnt FROM test_distinct; SELECT id, a, b, COUNT(*) AS cnt FROM test_distinct GROUP BY id, a, b HAVING cnt > 1; SELECT l.id AS l_id, l.a AS l_a, l.b AS l_b, r.id AS r_id, r.a AS r_a, r.b AS r_b FROM test_distinct l LEFT JOIN test_distinct r ON l.id = r.id AND l.a = r.a AND l.b = r.b WHERE r.id is NULL or r.id = 'null';
查询结果,如下所示:
注意!!!从测试数据很快就能大概猜出问题在哪,但是原来表中数据是有3万多条,无法用肉眼查看数据。
上面查询结果违反直觉的点有两个:
第二条去重统计后数据少了一条,但是,第三条数据的结果显示并没有相同的数据。
用同一张表做左外连接出现了驱动表有数据,而被驱动表为空的情况。
先看第二个问题,官方文档上有如下解释:
与ON一起使用的search_condition和WHERE子句中使用的条件表达式一样。 通常,ON子句用于指定如何连接表的条件,WHERE子句限制要包含在结果集中的行。
如果对于LEFT JOIN中ON或USING部分中的条件,右表没有匹配的行,则右表使用所有列设置为NULL。
不能使用算术比较运算符(如=,<或<>)来比较NULL。
SELECT NULL = NULL; SELECT NULL IS NULL;
所以问题二在于NULL=NULL的结果永远为False,也就导致两行原本相等的数据结果却不相等。
可是这并没有解决第一个问题:为什么去重后有一条数据消失了。但是,我们可以猜测消失的数据很有可能和NULL值有关系。
我们将count和distinct两个操作分开:
SELECT COUNT(*) as cnt FROM (SELECT DISTINCT id, a, b FROM test_distinct) as tmp;
嗯?结果是正确的,那就说明count(distinct expr)
生成的查询计划可能和我们想象的不一样,并不是先去重再统计,使用explain分析一下两条语句的查询计划,如下所示:
从表中可以看到,mysql执行引擎直接将count(distinct expr)
作为一个查询,查看官方文档:
至此问题才终于弄清楚了。解决这个问题的办法有两种,第一种就是上述的先去重后统计,第二种可以利用IFNULL()
函数:
SELECT COUNT(DISTINCT id, a, IFNULL(b, '0')) as cnt FROM test_distinct;
另外补充一点,count()嘚瑟使用:
SELECT id, a, b, COUNT(*) FROM test_distinct GROUP BY id, a, b; SELECT id, a, b, COUNT(b) FROM test_distinct GROUP BY id, a, b;
不能使用算术比较运算符(如=,<或<>)来比较空值;
count(distinct expr)返回expr列中不同的且非空的行数;
COUNT()有两个非常不同的作用:它可以统计某个列值的数量,也可以统计行数。在统计列值时要求列值是非空的(不统计NULL)。如果在COUNT()的括号中定了列或者列表达式,则统计的就是这个表达式有值的结果数。COUNT()的另一个作用是统计结果集的行数。当MySQL确认括号内的表达式值不可能为空时,实际上就是在统计行数。最简单的就是当我们使用COUNT()的时候,这种情况下通配符并不像我们猜想的那样扩展成所有的列,实际上,他会忽略所有列而直接统计所有的行数——《高性能MySQL》;
在InnoDB中,SELECT COUNT(*)和SELECT COUNT(1)处理方式一样, 没有性能差异。
关于“mysql踩坑之count distinct多列问题怎么解决”的内容就介绍到这里了,感谢大家的阅读。如果想了解更多行业相关的知识,可以关注亿速云行业资讯频道,小编每天都会为大家更新不同的知识点。
免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。