MySQL - 找出重複記錄
Hello, 動手學習數據庫的熱情者們！今天，我們將進入 MySQL 的世界，學習如何找出那些討厭的重複記錄。作為你們親切鄰居的計算機老師，我很興奮能夠指導你們這次旅程。別擔心如果你是編程新手——我們會從基礎開始，逐步學習。讓我們開始吧！
理解重複記錄
在我們進行代碼編寫之前，讓我們先來了解什麼是重複記錄。想像你有一盒五彩繽紛的彈珠。如果你有兩個或更多完全相同顏色、大小和花紋的彈珠，那麼它們就是重複的。在數據庫術語中，重複記錄是指表中具有一個或多個列中相同值的行。
找出重複記錄
現在，讓我們探討在 MySQL 中找出重複記錄的不同方法。我們將在整個課程中使用一個簡單的 students 表作為例子。
1. 使用 GROUP BY 和 HAVING 子句
這是最直接了當的方法之一來找出重複記錄。讓我們一步步分解。
SELECT name, email, COUNT(*)
FROM students
GROUP BY name, email
HAVING COUNT(*) > 1;讓我們解碼這個查詢：
-
SELECT name, email：我們選擇要顯示哪些列。 -
COUNT(*)：這計算出現的次數。 -
FROM students：這是我們的表名。 -
GROUP BY name, email：我們將具有相同名稱和電子郵件的記錄分組。 -
HAVING COUNT(*) > 1：這過濾出只有超過一條記錄的組。
想像你正在整理一堆學生報名表。你將它們按照名稱和電子郵件分組，然後挑出那些有超過一張表的組。這個查詢正是這樣做的！
2. 使用 ROW_NUMBER() 函數與 PARTITION BY
這種方法稍微進階一些，但非常強大。它為結果集內的每行分配一個數字。
WITH CTE AS (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY name, email ORDER BY id) AS row_num
FROM students
)
SELECT * FROM CTE WHERE row_num > 1;讓我們分解這個查詢：
-
WITH CTE AS (...)：這創建了一個公共表達式（CTE），就像一個臨時命名的結果集。 -
ROW_NUMBER() OVER (PARTITION BY name, email ORDER BY id)：這為每個名稱和電子郵件組中的每行分配一個從1開始的數字。 -
SELECT * FROM CTE WHERE row_num > 1：這選擇所有行號大於1的行，意味著它是重複的。
想像這是給每個學生根據他們報名的時間分配一個號碼，但每個獨特的名稱和電子郵件組合都會重新開始。然後我們挑出所有不是第一個報名的學生。
3. 使用自連接
另一種方法涉及將表與自身連接。這是它如何工作的：
SELECT DISTINCT s1.*
FROM students s1
JOIN students s2
ON s1.name = s2.name AND s1.email = s2.email AND s1.id > s2.id;這個查詢：
- 將
students表與自身連接。 - 匹配名稱和電子郵件相同的記錄，但 ID 不同。
-
s1.id > s2.id確保我們不會得到相同的記錄兩次。
想像你正在將每個學生的表與其他每個學生的表進行比較。當你找到兩個名稱和電子郵件相同但 ID不同的記錄時，你就找到了一個重複！
使用客戶端程序找出重複記錄
有時候，你可能想使用像 Python 這樣的客戶端程序來找出重複記錄。這裡有一個簡單的例子：
import mysql.connector
# 連接到數據庫
mydb = mysql.connector.connect(
host="localhost",
user="yourusername",
password="yourpassword",
database="yourdatabase"
)
mycursor = mydb.cursor()
# 執行查詢
mycursor.execute("""
SELECT name, email, COUNT(*)
FROM students
GROUP BY name, email
HAVING COUNT(*) > 1
""")
# 獲取並打印結果
myresult = mycursor.fetchall()
for x in myresult:
print(x)這個 Python 腳本：
- 連接到你的 MySQL 數據庫。
- 執行我們之前學習的 SQL 查詢。
- 獲取並打印結果。
這就像有一個機器人助手，它會通過數據庫找到重複記錄，並向你報告！
方法比較
這裡是我們討論過的方法的快速比較：
| 方法 | 優點 | 缺點 |
|---|---|---|
| GROUP BY 和 HAVING | 簡單，適用於所有 MySQL 版本 | 在大型數據集上可能會很慢 |
| ROW_NUMBER() | 高效，靈活 | 需要 MySQL 8.0+ |
| 自連接 | 適用於所有 MySQL 版本 | 對於多列可能會變得複雜 |
| 客戶端程序 | 允許進一步處理結果 | 需要額外的設置和編程 |
結論
恭喜你！你剛剛學會了在 MySQL 中找出重複記錄的多种方法。記住，每種方法都有其優勢，最佳選擇取決於你的具體情況。隨著你繼續在數據庫領域的旅程，你將會培養出對使用哪種方法的直覺。
持續練習，保持好奇心，並不要害怕嘗試。誰知道呢？你可能甚至會發現一種新的找出重複記錄的方法！直到下次見，快樂編程！
Credits: Image by storyset