如何从 MySQL 数据库中删除重复数据-java教程-PHP中文网

首页

Java

java教程

如何从 MySQL 数据库中删除重复数据

Linda Hamilton

Sep 27, 2024 am 06:14 AM

How to Delete Duplicate Data from a MySQL Database

介紹

使用資料庫時，特別是在不斷修改或附加大型資料集的環境中，經常會遇到重複資料。這可能會導致查詢效能低下、資料不一致和報告不準確。幸運的是，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);

登录后复制

此查询的作用如下：

创建一个 临时表 temp_employees，对于名字、姓氏和电子邮件的每个组合仅包含一条唯一记录（在本例中为具有最小 id 的记录）。
删除原始员工表中不存在于 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;

登录后复制

您可以多次运行此查询，直到删除所有重复项。