MySQL - 刪除重複記錄

你好，未來的數據庫魔法師們！今天，我們將踏上一段令人興奮的旅程，進入MySQL的世界，特別專注於如何刪除那些討厭的重複記錄。作為你們友好的鄰居計算機老師，我會一步一步地引導你們，確保你們理解每一個步驟。所以，拿起你們的虛擬掃把，讓我們來清理那些數據庫吧！

MySQL - Delete Duplicate Records

刪除MySQL中的重複記錄

在我們深入刪除重複記錄的細節之前，讓我們先花一分鐘來了解為什麼這很重要。想像一下，你正在管理一個圖書館數據庫，不知何故，你發現同一本書有多個條目。這不僅浪費空間，還可能導致混亂和錯誤。這就是我們刪除重複記錄操作派上用場的地方！

什麼是重複記錄？

重複記錄是數據庫表中在兩個或更多列中具有相同值的條目。在我們的圖書館例子中，這可能是具有相同ISBN編號、作者和書名的書籍。

查找重複值

在我們可以刪除重複記錄之前，我們需要先找到它們。這就像在玩一個"找出不同"的遊戲，但是反過來！讓我們看看一些識別這些重複記錄的方法。

使用GROUP BY和HAVING子句

SELECT column_name, COUNT(*) as count
FROM table_name
GROUP BY column_name
HAVING count > 1;

這個查詢將記錄按照指定列進行分組，並計算每個值出現的次數。HAVING子句過濾出計數大於1的組，有效地向我們展示了重複值。

例如，如果我們正在尋找我們圖書館中的重複書籍：

SELECT title, author, COUNT(*) as count
FROM books
GROUP BY title, author
HAVING count > 1;

這將向我們展示所有在我們數據庫中出現超過一次的書名和作者。

使用自連接（Self JOIN）

另一種查找重複的方法是使用自連接：

SELECT t1.*
FROM table_name t1
JOIN table_name t2
WHERE t1.id < t2.id
AND t1.column_name = t2.column_name;

這個查詢將表與自己連接，並比對每條記錄與其他記錄。它返回所有重複記錄，除了ID最大的那一個。

刪除重複記錄

現在我們已經找到了我們的重複記錄，是時候向它們說再見了。有幾種方法可以做到這一點，每種方法都有其優點和缺點。讓我們來探討一下！

使用带有子查询的DELETE

DELETE t1 FROM table_name t1
INNER JOIN table_name t2
WHERE t1.id < t2.id
AND t1.column_name = t2.column_name;

這個查詢刪除所有重複記錄，除了ID最大的那一個。這就像一場音樂椅遊戲，最後留下的記錄可以留下！

使用CREATE TABLE和INSERT

另一種方法是創建一個具有唯一記錄的新表，然後替換原始表：

CREATE TABLE temp_table AS
SELECT DISTINCT * FROM original_table;

DROP TABLE original_table;

ALTER TABLE temp_table RENAME TO original_table;

這種方法就像製作你喜歡的播放清單的新副本，但只保留每首歌的一個版本。

使用ROW_NUMBER()

對於更進階的使用者，我們可以使用ROW_NUMBER()函數：

DELETE FROM table_name
WHERE id NOT IN (
SELECT id
FROM (
SELECT id,
ROW_NUMBER() OVER (
PARTITION BY column_name
ORDER BY id
) AS row_num
FROM table_name
) t
WHERE t.row_num = 1
);

這為每個組中的每條記錄分配一個行號，然後刪除每個組中除第一行以外的所有行。

使用客戶端程序刪除重複記錄

有時候，在MySQL之外處理重複記錄更容易。以下是一個簡單的Python腚本，可以幫助你：

import mysql.connector

def delete_duplicates(connection, table_name, column_name):
cursor = connection.cursor()

# 查找並刪除重複記錄
query = f"""
DELETE t1 FROM {table_name} t1
INNER JOIN {table_name} t2
WHERE t1.id < t2.id
AND t1.{column_name} = t2.{column_name}
"""

cursor.execute(query)
connection.commit()

print(f"刪除了 {cursor.rowcount} 個重複記錄。")

# 使用範例
connection = mysql.connector.connect(
host="localhost",
user="yourusername",
password="yourpassword",
database="yourdatabase"
)

delete_duplicates(connection, "books", "isbn")

connection.close()

這個腚本連接到你的MySQL數據庫，執行刪除查詢，並報告刪除了多少重複記錄。這就像有了一個個人事務助理來清理你的數據庫！

結論

恭喜你們！你們現在已經學會了幾種在MySQL中查找和刪除重複記錄的方法。記住，維護一個乾淨、無重複記錄的數據庫對於數據完整性和高效操作至關重要。

這裡是我們討論過的方法的快速總結：

方法	優點	缺點
GROUP BY和HAVING	簡單易懂	只查找重複記錄，不刪除
自連接	灵活，可以比较多列	在大表上可能较慢
带有子查询的DELETE	对小到中等大小的表效率高	在非常大的表上可能较慢
CREATE TABLE和INSERT	保留原始数据	需要临时额外存储
ROW_NUMBER()	非常灵活和强大	语法更复杂
客户端程序	可以加入自定义逻辑	需要额外的编程

选择最适合您特定需求和数据库大小的方法。並且記住，在执行刪除操作之前总是备份你的数据。愉快地刪除重複記錄吧！

Credits: Image by storyset