目錄
問題分析
解決方案
#知識點
首頁 資料庫 mysql教程 mysql踩坑之count distinct多列問題怎麼解決

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

Jun 03, 2023 am 10:49 AM
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;
登入後複製

表內測試資料如下,現在我們需要統計這三列去重後的列的數量。

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

問題分析

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

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';
登入後複製

查詢結果,如下所示:

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

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

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

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

#注意! ! !從測試資料很快就能大概猜出問題在哪,但原來表中資料是有3萬多條,無法用肉眼查看資料。

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

  • 第二條去重統計後資料少了一條,但是,第三條資料的結果顯示並沒有相同的數據。

  • 用同一張表做左外連接出現了驅動表有數據,而被驅動表為空的情況。

先看第二個問題,官方文件上有以下解釋:

  • 在使用ON子句時,其所包含的條件表達式與WHERE子句中使用的相同。常見的情況是使用ON子句來指定表的連接條件,而使用WHERE子句對結果集中包含的行進行限制。

  • 如果對於LEFT JOIN中ON或USING部分中的條件,則右表沒有符合的行,則右表使用所有欄位設定為NULL。

  • 不能使用算術比較運算子(如=,<或<>)來比較NULL。

SELECT NULL = NULL;
SELECT NULL IS NULL;
登入後複製

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

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

所以問題二在於NULL=NULL的結果永遠是False,也就導致兩行原本相等的數據結果卻不相等。

可是這並沒有解決第一個問題:為什麼去重後有資料消失了。但是,我們可以猜測消失的資料很有可能和NULL值有關係。

我們將count和distinct兩個動作分開:

SELECT COUNT(*) as cnt FROM (SELECT  DISTINCT id, a, b FROM test_distinct) as tmp;
登入後複製

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

嗯?結果是正確的,那就說明count(distinct expr)產生的查詢計劃可能和我們想像的不一樣,並不是先去重再統計,使用explain分析一下兩條語句的查詢計劃,如下所示:

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

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

從表中可以看到,mysql執行引擎直接將count(distinct expr)作為查詢,請查看官方文件:

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

解決方案

至此問題才終於弄清楚了。解決這個問題的方法有兩種,第一種就是上述的先去重後統計,第二種可以利用IFNULL()函數:

SELECT COUNT(DISTINCT id, a, IFNULL(b, &#39;0&#39;)) 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 distinct多列問題怎麼解決

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

#知識點

  • 不能使用算術比較運算符(如=,)來比較空值;

  • count(distinct expr)傳回expr列中不同的且非空的行數;

  • COUNT()有兩種截然不同的用途:它既可用於計算某個列值的數量,也可用於計算行數。在統計列值時要求列值是非空的(不統計NULL)。當在COUNT()函數的括號中指定了一個欄位或表達式時,函數會統計這個表達式中有值的結果數。 COUNT()的另一個作用是統計結果集的行數。當MySQL確認括號內的表達式值不可能為空時,實際上就是在統計行數。最簡單的就是當我們使用COUNT()的時候,這種情況下通配符並不像我們猜想的那樣擴展成所有的列,實際上,他會忽略所有列而直接統計所有的行數—— 《高效能MySQL》;

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

#

以上是mysql踩坑之count distinct多列問題怎麼解決的詳細內容。更多資訊請關注PHP中文網其他相關文章!

本網站聲明
本文內容由網友自願投稿,版權歸原作者所有。本站不承擔相應的法律責任。如發現涉嫌抄襲或侵權的內容,請聯絡admin@php.cn

熱AI工具

Undresser.AI Undress

Undresser.AI Undress

人工智慧驅動的應用程序,用於創建逼真的裸體照片

AI Clothes Remover

AI Clothes Remover

用於從照片中去除衣服的線上人工智慧工具。

Undress AI Tool

Undress AI Tool

免費脫衣圖片

Clothoff.io

Clothoff.io

AI脫衣器

AI Hentai Generator

AI Hentai Generator

免費產生 AI 無盡。

熱門文章

R.E.P.O.能量晶體解釋及其做什麼(黃色晶體)
2 週前 By 尊渡假赌尊渡假赌尊渡假赌
倉庫:如何復興隊友
4 週前 By 尊渡假赌尊渡假赌尊渡假赌
Hello Kitty Island冒險:如何獲得巨型種子
3 週前 By 尊渡假赌尊渡假赌尊渡假赌

熱工具

記事本++7.3.1

記事本++7.3.1

好用且免費的程式碼編輯器

SublimeText3漢化版

SublimeText3漢化版

中文版,非常好用

禪工作室 13.0.1

禪工作室 13.0.1

強大的PHP整合開發環境

Dreamweaver CS6

Dreamweaver CS6

視覺化網頁開發工具

SublimeText3 Mac版

SublimeText3 Mac版

神級程式碼編輯軟體(SublimeText3)

PHP 的大數據結構處理技巧 PHP 的大數據結構處理技巧 May 08, 2024 am 10:24 AM

PHP 的大數據結構處理技巧

如何優化 PHP 中的 MySQL 查詢效能? 如何優化 PHP 中的 MySQL 查詢效能? Jun 03, 2024 pm 08:11 PM

如何優化 PHP 中的 MySQL 查詢效能?

如何在 PHP 中使用 MySQL 備份和還原? 如何在 PHP 中使用 MySQL 備份和還原? Jun 03, 2024 pm 12:19 PM

如何在 PHP 中使用 MySQL 備份和還原?

如何使用 PHP 插入資料到 MySQL 表? 如何使用 PHP 插入資料到 MySQL 表? Jun 02, 2024 pm 02:26 PM

如何使用 PHP 插入資料到 MySQL 表?

Java 枚舉類型在資料庫中的應用場景有哪些? Java 枚舉類型在資料庫中的應用場景有哪些? May 05, 2024 am 09:06 AM

Java 枚舉類型在資料庫中的應用場景有哪些?

如何修復 MySQL 8.4 上的 mysql_native_password 未載入錯誤 如何修復 MySQL 8.4 上的 mysql_native_password 未載入錯誤 Dec 09, 2024 am 11:42 AM

如何修復 MySQL 8.4 上的 mysql_native_password 未載入錯誤

如何在 PHP 中使用 MySQL 預存程序? 如何在 PHP 中使用 MySQL 預存程序? Jun 02, 2024 pm 02:13 PM

如何在 PHP 中使用 MySQL 預存程序?

如何使用 PHP 建立 MySQL 表? 如何使用 PHP 建立 MySQL 表? Jun 04, 2024 pm 01:57 PM

如何使用 PHP 建立 MySQL 表?

See all articles