如何從 MySQL 資料庫中刪除重複數據

介紹

使用資料庫時，特別是在不斷修改或附加大型資料集的環境中，經常會遇到重複資料。這可能會導致查詢效能低下、資料不一致和報告不準確。幸運的是，MySQL 提供了強大的工具來識別和刪除這些重複項。

在本部落格中，我將引導您了解一種高效且全面的方法來檢測和刪除 MySQL 資料庫中的重複記錄。這裡的方法適用於大多數關係型資料庫系統，但本教學我們將重點放在 MySQL。

什麼構成重複項？

在深入研究程式碼之前，定義什麼是重複是至關重要的。在許多情況下，重複項不僅僅是所有欄位都具有相同值的記錄。通常，重複項在鍵列的子集中可能具有相同的值。例如，在 users 表中，兩筆記錄可能具有相同的電子郵件地址，但其他欄位（例如使用者名稱或註冊日期）不同。

為了簡單起見，在本教程中，我們假設重複項是所有列（或列的子集）匹配的行。

考慮以下範例表員工：

id	first_name	last_name	email	salary
1	John	Doe	john@example.com	60000
2	Jane	Smith	jane@example.com	65000
3	John	Doe	john@example.com	60000
4	Alex	Johnson	alex@example.com	72000
5	John	Doe	john@example.com	60000

這裡， id = 1、id = 3 和 id = 5 的行是重複的。我們的目標是刪除它們，同時只保留一份副本。

刪除重複項的逐步指南

1. 辨識重複記錄

第一步是辨識哪些記錄是重複的。為此，我們需要按唯一的列對記錄進行分組。在這種情況下，我們假設名字、姓氏和電子郵件的組合應該是唯一的。

您可以使用以下查詢來尋找重複項：

SELECT first_name, last_name, email, COUNT(*)
FROM employees
GROUP BY first_name, last_name, email
HAVING COUNT(*) > 1;

此查詢根據名字、姓氏和電子郵件列對記錄進行分組，並僅顯示出現多次的群組，即重複項。

2. 選擇要保留或刪除的重複行

辨識出重複項後，我們需要一種方法來刪除它們。常見的做法是保留 id 最小或最大的記錄，刪除其他記錄。我們可以使用 自連接 來將每個重複記錄與我們想要保留的記錄進行配對。

例子：

DELETE e1
FROM employees e1
JOIN employees e2
ON e1.first_name = e2.first_name
AND e1.last_name = e2.last_name
AND e1.email = e2.email
AND e1.id > e2.id;

此查詢的工作原理如下：

• 它對員工表執行自聯接，其中記錄具有相同的名字、姓氏和電子郵件。
• 它確保只有具有較高 id 的行（意味著它們是稍後插入的）才會被刪除，同時保留具有最小 id 的記錄。

3. 針對更複雜的場景使用臨時表

在某些情況下，您可能有更複雜的條件來決定保留哪個副本，例如保留最新註冊日期或最高薪水的記錄。

在這種情況下，您可以使用臨時表來儲存要保留的行，然後刪除其他所有內容。

例子：

CREATE TEMPORARY TABLE temp_employees AS
SELECT * FROM employees e1
WHERE e1.id IN (
    SELECT MIN(id)
    FROM employees
    GROUP BY first_name, last_name, email
);

DELETE FROM employees
WHERE id NOT IN (SELECT id FROM temp_employees);

此查詢的作用如下：

1. 建立一個 臨時表 temp_employees，對於名字、姓氏和電子郵件的每個組合僅包含一條唯一記錄（在本例中為具有最小 id 的記錄）。
2. 刪除原始員工表中不存在於 temp_employees 表中的所有記錄。

當決定保留哪個重複項的標準比簡單地使用 id 更複雜時，此方法非常有用。

4. 增加唯一約束以防止將來重複

清除重複項後，最好防止它們再次出現。您可以透過在相關列中新增唯一約束來實現此目的。

例如，為了防止將來出現任何具有相同名字、姓氏和電子郵件的行：

ALTER TABLE employees
ADD CONSTRAINT unique_employee
UNIQUE (first_name, last_name, email);

這確保瞭如果嘗試插入重複記錄，資料庫將拋出錯誤，從而保持資料完整性。

性能考慮因素

處理大型資料集時，刪除重複項可能會很慢並且會佔用大量資源。以下是一些優化效能的技巧：

• 索引： 確保過濾涉及的欄位（如名字、姓氏、電子郵件）已建立索引。這可以顯著加快該過程。
• 批次刪除：如果要刪除大量行，請考慮分批進行，以避免長時間鎖定並減少伺服器的負載。

批次刪除範例：

DELETE e1
FROM employees e1
JOIN employees e2
ON e1.first_name = e2.first_name
AND e1.last_name = e2.last_name
AND e1.email = e2.email
AND e1.id > e2.id
LIMIT 1000;

您可以多次執行此查詢，直到刪除所有重複項。

結論

處理 MySQL 資料庫中的重複記錄是一項常見任務，如果處理不當，可能會導致資料遺失或結果不一致。使用本部落格中概述的步驟，您可以自信且有效率地刪除重複項，同時保持資料的完整性。此外，透過新增唯一的約束，您可以確保防止將來出現重複，從而幫助維護乾淨、可靠的資料集。

以上是如何從 MySQL 資料庫中刪除重複數據的詳細內容。更多資訊請關注PHP中文網其他相關文章！