Mysql根据电子邮件删除重复的基础但保留最长的行和长度记录

Question

我有这个代码用于根据电子邮件删除重复项并保留较大的 ID 记录:

DELETE FROM mytable
 WHERE id NOT IN (SELECT * 
                    FROM (SELECT MAX(n.id)
                            FROM mytable n
                        GROUP BY n.email) x)

现在我根据电子邮件编写此代码，但保留最长的行总和长度记录:

DELETE FROM mytable
 WHERE (char_length(firstname) + char_length(lastname) + char_length(location) + char_length(address)) NOT IN (SELECT * 
                    FROM (SELECT MAX(char_length(n.firstname) + char_length(n.lastname) + char_length(n.location) + char_length(n.address))
                            FROM mytable n
                        GROUP BY n.email) x)

奇怪的是，它适用于小表，但对于我的大表，它只运行一秒钟，没有显示任何效果。谁能告诉我哪里做错了？

Answer 1

你的做法相当危险。它可能适用于 id，但不适用于不同行之间可能相同的长度。相反，请使用 join:

DELETE t
    FROM mytable t JOIN
         (SELECT email,
                 (char_length(firstname) + char_length(lastname) + 
                  char_length(location) + char_length(address)
                 )  as len
          FROM mytable
          GROUP BY email
         ) tt
         ON t.email = tt.email
    WHERE (char_length(t.firstname) + char_length(t.lastname) + 
           char_length(t.location) + char_length(t.address)
          ) <> tt.len;