目录背景问题分析解决办法知识点总结背景 有个小伙伴在用Mysql做统计分析的时候发现有行数据凭空消失了。 最近我刚好在学习相关内容,所以对这个问题比较感兴趣,就研究了一下。 复现的测试数据库如下所示: CREATE TA
有个小伙伴在用Mysql做统计分析的时候发现有行数据凭空消失了。
最近我刚好在学习相关内容,所以对这个问题比较感兴趣,就研究了一下。
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万多条,无法用肉眼查看数据。
上面查询结果违反直觉的点有两个:
先看第二个问题,官方文档上有如下解释:
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;
以上为个人经验,希望能给大家一个参考,也希望大家多多支持我们。
--结束END--
本文标题: mysql踩坑之count distinct多列问题
本文链接: https://www.lsjlt.com/news/200970.html(转载时请注明来源链接)
有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
下载Word文档到电脑,方便收藏和打印~
2024-04-29
2024-04-29
2024-04-29
2024-04-29
2024-04-28
2024-04-28
2024-04-28
2024-04-28
2024-04-28
2024-04-28
回答
回答
回答
回答
回答
回答
回答
回答
回答
回答
0