MySQL - 找出重複記錄

Hello, 動手學習數據庫的熱情者們！今天，我們將進入 MySQL 的世界，學習如何找出那些討厭的重複記錄。作為你們親切鄰居的計算機老師，我很興奮能夠指導你們這次旅程。別擔心如果你是編程新手——我們會從基礎開始，逐步學習。讓我們開始吧！

MySQL - Find Duplicate Records

理解重複記錄

在我們進行代碼編寫之前，讓我們先來了解什麼是重複記錄。想像你有一盒五彩繽紛的彈珠。如果你有兩個或更多完全相同顏色、大小和花紋的彈珠，那麼它們就是重複的。在數據庫術語中，重複記錄是指表中具有一個或多個列中相同值的行。

找出重複記錄

現在，讓我們探討在 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