如何删除我的MySQL数据库中的重复行？ （保留主ID最低的一个） [英] How do I remove duplicates rows in my MySQL database? (Keep the one with lowest Primary ID)

问题描述

假设我想先选择download_link相同的行。然后，我想保留一个具有最低主id的那个，并丢弃其余的。

Let's say I want to first select rows which have download_link the same. Then, I want to keep the one that has lowest primary id, and throw away the rest.

这是否有一个简单的SQL语句？这将工作吗？

Is there an easy SQL statement for this? Would this work?

delete from mytable
where id not in
    (select min(id)
     from mytable
     group by download_link);

推荐答案

您不需要临时表或子查询。您可以使用简单的连接：

You don't need temporary tables or subqueries. You can do it with a simple join:

DELETE t0
FROM mytable AS t0
JOIN mytable AS t1 ON t1.download_link=t0.download_link AND t1.id<t0.id;

也就是说，删除具有相同链接和较低ID的另一行。

That is, "delete every row for which there is another row with the same link and a lower ID".

这篇关于如何删除我的MySQL数据库中的重复行？ （保留主ID最低的一个）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！