如何删除customers表中deleted为true的重复邮件记录？

我们知道customers表中存在重复邮箱的记录，每个重复邮箱对应两条数据：一条deleted字段为0（未删除状态），另一条为1（标记为删除状态）。

通过以下SQL可以查询所有重复邮箱及其重复次数：

SELECT email, COUNT(*) count FROM customers GROUP BY email HAVING count > 1 ORDER BY count DESC;

查询结果示例：

+--------------------------------------+-------+
| email                                | count |
+--------------------------------------+-------+
| aaamedianamail@gmail.com             |     2 |
| aaaarialal@mail.ru                   |     2 |
| aaaa-aaaaosa@gmail.com               |     2 |
| aaa-t@gmail.com                      |     2 |
| aaaaa_aaa@hotmail.com                |     2 |
| aaaaamil@hotmail.com                 |     2 |
| ameaaaaanaa@hotmail.com              |     2 |
| aaaaaa@gmail.com                     |     2 |
| aqqqgmx.net                          |     2 |
+--------------------------------------+-------+

以aqqqgmx.net为例，该邮箱对应的两条记录详情：

SELECT id, name, deleted FROM customers where email = "aqqqgmx.net";

返回结果：

+--------------------------------------+----------------------------+---------+
| id                                   | name                       | deleted |
+--------------------------------------+----------------------------+---------+
| b4afc635-145a-f735-a2d2-6421584cd55f | Ada                        |       1 |
| d4b5952b-de3e-3b3c-d0d5-641e1a83aef1 | Ada                        |       0 |
+--------------------------------------+----------------------------+---------+

现在需要实现：删除所有deleted为1且属于重复邮箱的记录。

方法1：使用DELETE自连接 #

通过自连接匹配同一个邮箱下的两条记录，精准删除deleted=1的那条：

DELETE t1
FROM customers t1
JOIN customers t2
  ON t1.email = t2.email
WHERE t1.deleted = 1
  AND t2.deleted = 0;

这条语句会自动关联每个重复邮箱的两条记录，只删除标记为删除的那条，保留未删除的有效数据。

方法2：子查询筛选重复邮箱后删除 #

先筛选出所有存在重复的邮箱，再删除这些邮箱中deleted=1的记录：

DELETE FROM customers
WHERE deleted = 1
  AND email IN (
    SELECT email
    FROM customers
    GROUP BY email
    HAVING COUNT(*) > 1
  );

如果你的MySQL版本报错（不允许在DELETE中直接引用同表子查询），可以用派生表改写：

DELETE FROM customers
WHERE deleted = 1
  AND email IN (
    SELECT email FROM (
      SELECT email
      FROM customers
      GROUP BY email
      HAVING COUNT(*) > 1
    ) AS duplicate_emails
  );

重要提示 #

• 删除前一定要先验证目标记录，执行以下SELECT语句确认要删除的内容：

  SELECT * FROM customers WHERE deleted = 1 AND email IN (SELECT email FROM customers GROUP BY email HAVING COUNT(*) > 1);
  

• 操作前建议备份数据，避免误删导致数据丢失。

内容的提问来源于stack exchange，提问作者Mario