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中文網其他相關文章!

熱AI工具

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

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

Undress AI Tool
免費脫衣圖片

Clothoff.io
AI脫衣器

Video Face Swap
使用我們完全免費的人工智慧換臉工具,輕鬆在任何影片中換臉!

熱門文章

熱工具

記事本++7.3.1
好用且免費的程式碼編輯器

SublimeText3漢化版
中文版,非常好用

禪工作室 13.0.1
強大的PHP整合開發環境

Dreamweaver CS6
視覺化網頁開發工具

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

MySQL是一種開源的關係型數據庫管理系統,主要用於快速、可靠地存儲和檢索數據。其工作原理包括客戶端請求、查詢解析、執行查詢和返回結果。使用示例包括創建表、插入和查詢數據,以及高級功能如JOIN操作。常見錯誤涉及SQL語法、數據類型和權限問題,優化建議包括使用索引、優化查詢和分錶分區。

可以通過以下步驟打開 phpMyAdmin:1. 登錄網站控制面板;2. 找到並點擊 phpMyAdmin 圖標;3. 輸入 MySQL 憑據;4. 點擊 "登錄"。

選擇MySQL的原因是其性能、可靠性、易用性和社區支持。 1.MySQL提供高效的數據存儲和檢索功能,支持多種數據類型和高級查詢操作。 2.採用客戶端-服務器架構和多種存儲引擎,支持事務和查詢優化。 3.易於使用,支持多種操作系統和編程語言。 4.擁有強大的社區支持,提供豐富的資源和解決方案。

MySQL在數據庫和編程中的地位非常重要,它是一個開源的關係型數據庫管理系統,廣泛應用於各種應用場景。 1)MySQL提供高效的數據存儲、組織和檢索功能,支持Web、移動和企業級系統。 2)它使用客戶端-服務器架構,支持多種存儲引擎和索引優化。 3)基本用法包括創建表和插入數據,高級用法涉及多表JOIN和復雜查詢。 4)常見問題如SQL語法錯誤和性能問題可以通過EXPLAIN命令和慢查詢日誌調試。 5)性能優化方法包括合理使用索引、優化查詢和使用緩存,最佳實踐包括使用事務和PreparedStatemen

Apache 連接數據庫需要以下步驟:安裝數據庫驅動程序。配置 web.xml 文件以創建連接池。創建 JDBC 數據源,指定連接設置。從 Java 代碼中使用 JDBC API 訪問數據庫,包括獲取連接、創建語句、綁定參數、執行查詢或更新以及處理結果。

在 Docker 中啟動 MySQL 的過程包含以下步驟:拉取 MySQL 鏡像創建並啟動容器,設置根用戶密碼並映射端口驗證連接創建數據庫和用戶授予對數據庫的所有權限

MySQL在Web應用中的主要作用是存儲和管理數據。 1.MySQL高效處理用戶信息、產品目錄和交易記錄等數據。 2.通過SQL查詢,開發者能從數據庫提取信息生成動態內容。 3.MySQL基於客戶端-服務器模型工作,確保查詢速度可接受。

優雅安裝 MySQL 的關鍵在於添加 MySQL 官方倉庫。具體步驟如下:下載 MySQL 官方 GPG 密鑰,防止釣魚攻擊。添加 MySQL 倉庫文件:rpm -Uvh https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm更新 yum 倉庫緩存:yum update安裝 MySQL:yum install mysql-server啟動 MySQL 服務:systemctl start mysqld設置開機自啟動
