mysql - 根据一列删除重复项，并保留在不同列中具有值的行，如果没有，则保留 ID 最低的行

Question

在 Google Cloud 上使用 MySQL 5.7，我尝试根据“EmailAddress”列对 MySQL 数据进行重复数据删除，但有些行在“FullName”列中具有值，而有些行则没有。我想保留在 FullName 列中具有值的行，但如果具有该 EmailAddress 值的行中没有一个是 FullName 值，则只需保留具有最低 ID 号的重复项(第一列 - 主键)。

我最终将其分解为两个单独的查询，一个查询首先删除 FullName 列中没有值的行，如果有另一个重复行在 FullName 列中具有值:

DELETE
FROM customer_info
WHERE id IN
(
 SELECT * 
 FROM
    (   
     SELECT c1.id
     FROM customer_info c1
            INNER JOIN customer_info c2 on c1.EmailAddress=c2.EmailAddress and c1.id!=c2.id
     WHERE 
            (trim(c1.FullName)='' or c1.FullName is NULL)
            and c2.FullName is not NULL
            and length(trim(c2.FullName))!=0
    ) t
)

另一个查询用于删除具有较大 ID 且在 FullName 列中未找到值的行:

DELETE
FROM customer_info
WHERE id IN
(
 SELECT * 
 FROM
    (   
     SELECT c1.id
     FROM customer_info c1
            INNER JOIN customer_info c2 on c1.EmailAddress=c2.EmailAddress and c1.id>c2.id
    ) t
)

这“有效”，但实际上并非如此。有一次，当我让它运行一夜以获取一小部分数据时，它起作用了，当我醒来时，出现了错误，但我查看了数据，它是完整的。

我的查询中是否遗漏了某些内容，导致其效率极低，或者它是否只是此类查询类(class)的标准，并且我的代码中没有可能的优化可以带来切实的改进？我已经将 Google Cloud SQL 实例的大小最大化到了 db-n1-highmem-32，具有 32 GB 内存和 1000 GB 存储空间，但运行一小时后它仍然卡住并抛出 2013 错误。我需要对总共略多于 300 万行执行此操作。

例如:

id | FullName      | EmailAddress            |
----------------------------------------------
1  | John Doe      | john.doe@email.com      |
2  | null          | janedoe@box.com         |
3  | null          | billybob@bobby.com      |
4  | null          | john.doe@email.com      |
5  | John Lennon   | jlennon@yoohoo.com      |
6  | null          | james.smith@coolmail.com|
7  | null          | billybob@bobby.com      |
8  | Jane Doe      | janedoe@box.com         |

会导致这样的结果:

id | FullName      | EmailAddress            |
----------------------------------------------
1  | John Doe      | john.doe@email.com      |
3  | null          | billybob@bobby.com      |
5  | John Lennon   | jlennon@yoohoo.com      |
6  | null          | james.smith@coolmail.com|
8  | Jane Doe      | janedoe@box.com         |

Answer 1

在这种情况下使用 exists() 可能会更简单

delete
from customer_info c
where (trim(c.FullName)='' or c.FullName is null)
  and exists (   
     select 1
     from customer_info i
     where i.Email = c.EmailAddress 
      and trim(i.FullName)>'' 
  )

delete
from customer_info c
where exists (
     select 1
     from customer_info i
     where i.Email = c.EmailAddress 
       and i.id < c.id
  )