mysql踩坑之count distinct多列问题

mysql踩坑之count distinct多列问题

mysql踩坑之count 多列问题

更新时间：2023年03月23日 08:50:12   作者：山木枝

这篇文章主要介绍了mysql踩坑之count 多列问题，具有很好的参考价值，希望对大家有所帮助。如有错误或未考虑完全的地方mysql踩坑之count distinct多列问题，望不吝赐教

背景

有个小伙伴在用mysql做统计分析的时候发现有行数据凭空消失了。

最近我刚好在学习相关内容mysql count，所以对这个问题比较感兴趣，就研究了一下。

复现的测试数据库如下所示：

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万多条mysql count，无法用肉眼查看数据。

上面查询结果违反直觉的点有两个：

先看第二个问题，官方文档上有如下解释：

SELECT NULL = NULL;
SELECT NULL IS NULL;

所以问题二在于NULL=NULL的结果永远为False，也就导致两行原本相等的数据结果却不相等。

可是这并没有解决第一个问题：为什么去重后有一条数据消失了。但是，我们可以猜测消失的数据很有可能和NULL值有关系。

我们将count和两个操作分开：

SELECT COUNT(*) as cnt FROM (SELECT  DISTINCT id, a, b FROM test_distinct) as tmp;

嗯？结果是正确的，那就说明count( expr)生成的查询计划可能和我们想象的不一样，并不是先去重再统计，使用分析一下两条语句的查询计划，如下所示：

从表中可以看到，mysql执行引擎直接将count( expr)作为一个查询mysql踩坑之count distinct多列问题，查看官方文档：

解决办法

至此问题才终于弄清楚了。解决这个问题的办法有两种，第一种就是上述的先去重后统计，第二种可以利用()函数:

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;

知识点总结

以上为个人经验mysql count，希望能给大家一个参考，也希望大家多多支持脚本之家。