如何删除MySQL数据库中的重复行？ （保留具有最低主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.

假设我想先选择download_link相同的行。然后，我想保留那个具有最低主要ID的那个，然后扔掉其余的。

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

是否有一个简单的SQL语句？这会有用吗？

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

4 个解决方案

#1

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;

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

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

#2

Something like this should work:

像这样的东西应该工作：

DELETE FROM `table` 
WHERE `id` NOT IN (
    SELECT MIN(`id`) 
    FROM `table`
    GROUP BY `download_link`)

Just to be on the safe side, before running the actual delete query, you might want to do an equivalent select to see what gets deleted:

为了安全起见，在运行实际删除查询之前，您可能希望执行等效选择以查看删除的内容：

SELECT * FROM `table` 
WHERE `id` NOT IN (
    SELECT MIN(`id`) 
    FROM `table`
    GROUP BY `download_link`)

#3

Error 1093 prevents your approach working in MySQL. Work-around by creating a temporary table:

错误1093阻止您的方法在MySQL中工作。通过创建临时表来解决此问题：

  CREATE TEMPORARY TABLE table_purge SELECT MIN(id) id FROM table GROUP BY download_link;
  DELETE FROM table where id NOT IN (SELECT id FROM table_purge);

Edited to add an alternative work-around that doesn't involve an explicit temporary table. Presumably this works because the query execution plan naturally creates a temporary table anyway:

编辑以添加不涉及显式临时表的替代解决方法。大概这是有效的，因为查询执行计划自然会创建一个临时表：

 DELETE table
  FROM table 
  NATURAL JOIN (
      SELECT id, download_link
      FROM table
      NATURAL JOIN (
          SELECT MIN(id) min_id, download_link
          FROM table
          GROUP BY download_link ) table_min
      WHERE id > min_id
  )  table_to_purge;

#4

try following query

尝试以下查询

 delete from table where id not in 
 (select * from 
      (select min(id) from table group by download_link)
 SWA_TABAL)

It works fine with mysql 5.0.x

它适用于mysql 5.0.x.