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子句時，其所包含的條件表達式與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多列問題怎麼解決的詳細內容。更多資訊請關注PHP中文網其他相關文章！