mysql踩坑之count?distinct多列問題怎么解決

這篇文章主要介紹“mysql踩坑之count distinct多列問題怎么解決”的相關知識，小編通過實際案例向大家展示操作過程，操作方法簡單快捷，實用性強，希望這篇“mysql踩坑之count distinct多列問題怎么解決”文章能幫助大家解決問題。

復現(xiàn)的測試數(shù)據(jù)庫如下所示：

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;

表內(nèi)測試數(shù)據(jù)如下，現(xiàn)在我們需要統(tǒng)計這三列去重后的列的數(shù)量。

問題分析

小伙伴給了我四條用來定位問題的查詢語句

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';

查詢結果，如下所示：

注意！??！從測試數(shù)據(jù)很快就能大概猜出問題在哪，但是原來表中數(shù)據(jù)是有3萬多條，無法用肉眼查看數(shù)據(jù)。

上面查詢結果違反直覺的點有兩個：

• 第二條去重統(tǒng)計后數(shù)據(jù)少了一條，但是，第三條數(shù)據(jù)的結果顯示并沒有相同的數(shù)據(jù)。

• 用同一張表做左外連接出現(xiàn)了驅(qū)動表有數(shù)據(jù)，而被驅(qū)動表為空的情況。

先看第二個問題，官方文檔上有如下解釋：

• 與ON一起使用的search_condition和WHERE子句中使用的條件表達式一樣。 通常，ON子句用于指定如何連接表的條件，WHERE子句限制要包含在結果集中的行。

• 如果對于LEFT JOIN中ON或USING部分中的條件，右表沒有匹配的行，則右表使用所有列設置為NULL。

• 不能使用算術比較運算符（如=，<或<>）來比較NULL。

SELECT NULL = NULL;
SELECT NULL IS NULL;

所以問題二在于NULL=NULL的結果永遠為False，也就導致兩行原本相等的數(shù)據(jù)結果卻不相等。

可是這并沒有解決第一個問題：為什么去重后有一條數(shù)據(jù)消失了。但是，我們可以猜測消失的數(shù)據(jù)很有可能和NULL值有關系。

我們將count和distinct兩個操作分開：

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

嗯？結果是正確的，那就說明count(distinct expr)生成的查詢計劃可能和我們想象的不一樣，并不是先去重再統(tǒng)計，使用explain分析一下兩條語句的查詢計劃，如下所示：

從表中可以看到，mysql執(zhí)行引擎直接將count(distinct expr)作為一個查詢，查看官方文檔：

解決辦法

至此問題才終于弄清楚了。解決這個問題的辦法有兩種，第一種就是上述的先去重后統(tǒng)計，第二種可以利用IFNULL()函數(shù):

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列中不同的且非空的行數(shù)；

• COUNT()有兩個非常不同的作用：它可以統(tǒng)計某個列值的數(shù)量，也可以統(tǒng)計行數(shù)。在統(tǒng)計列值時要求列值是非空的（不統(tǒng)計NULL）。如果在COUNT()的括號中定了列或者列表達式，則統(tǒng)計的就是這個表達式有值的結果數(shù)。COUNT()的另一個作用是統(tǒng)計結果集的行數(shù)。當MySQL確認括號內(nèi)的表達式值不可能為空時，實際上就是在統(tǒng)計行數(shù)。最簡單的就是當我們使用COUNT()的時候，這種情況下通配符并不像我們猜想的那樣擴展成所有的列，實際上，他會忽略所有列而直接統(tǒng)計所有的行數(shù)&mdash;&mdash;《高性能MySQL》；

• 在InnoDB中，SELECT COUNT(*)和SELECT COUNT(1)處理方式一樣， 沒有性能差異。

關于“mysql踩坑之count distinct多列問題怎么解決”的內(nèi)容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業(yè)相關的知識，可以關注億速云行業(yè)資訊頻道，小編每天都會為大家更新不同的知識點。