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

php中文网 2024-10-15 12:28:05

介绍

使用数据库时，特别是在不断修改或附加大型数据集的环境中，经常会遇到重复数据。这可能会导致查询性能低下、数据不一致和报告不准确。幸运的是，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(*) &gt; 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 &gt; e2.id;

此查询的工作原理如下：

它对员工表执行自联接，其中记录具有相同的名字、姓氏和电子邮件。
它确保只有具有较高 id 的行（意味着它们是稍后插入的）才会被删除，同时保留具有最小 id 的记录。

3. 针对更复杂的场景使用临时表

在某些情况下，您可能有更复杂的条件来确定保留哪个副本，例如保留最新的signup_date或最高薪水的记录。

在这种情况下，您可以使用临时表来存储要保留的行，然后删除其他所有内容。

例子：

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 &gt; e2.id
LIMIT 1000;

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