如何删除重复的行并保留第一行？

I made a mistake and I have unwanted duplicates.

我犯了一个错误，我有不必要的重复。

I have a table with 4 key fields. A1, k1, k2, k3.

我有一个包含4个关键字段的表。 A1，k1，k2，k3。

A1 is auto increment and the primary key.

A1是自动增量和主键。

the combination of k1, k2 and k3 is supposed to be unique and I have to delete the duplicate rows before I create a unique index. Some rows have one duplicate, some have many.

k1，k2和k3的组合应该是唯一的，我必须在创建唯一索引之前删除重复的行。有些行有一个副本，有些行有很多。

SELECT CONCAT(k1, k2, k) AS dup_value
  FROM myviews
 GROUP BY dup_value
HAVING (COUNT(dup_value) > 1)

shows me duplicates values that I need to deal with. But now I don't know how to keep one and delete the rest of each duplicate set.

向我展示了我需要处理的重复值。但现在我不知道如何保留一个并删除每个重复集的其余部分。

4 个解决方案

#1

Backup your data, then...

MySQL supports JOINs in DELETE statements. If you want to keep the first of the duplicates:

MySQL支持DELETE语句中的JOIN。如果您想保留第一个重复项：

DELETE a
  FROM MYVIEWS a
  JOIN (SELECT MIN(t.a1) AS min_a1, t.k1, t.k2, t.k3
          FROM MYVIEWS t
      GROUP BY t.k1, t.k2, t.k3
        HAVING COUNT(*) > 1) b ON b.k1 = a.k1
                              AND b.k2 = a.k2
                              AND b.k3 = a.k3
                              AND b.min_a1 != a.a1

If you want to keep the last of the duplicates:

如果你想保留最后一个重复项：

DELETE a
  FROM MYVIEWS a
  JOIN (SELECT MAX(t.a1) AS max_a1, t.k1, t.k2, t.k3
          FROM MYVIEWS t
      GROUP BY t.k1, t.k2, t.k3
        HAVING COUNT(*) > 1) b ON b.k1 = a.k1
                              AND b.k2 = a.k2
                              AND b.k3 = a.k3
                              AND b.max_a1 != a.a1

#2

You can create a new table with the same structure but empty, then create the unique key on it, then do a INSERT IGNORE / SELECT * FROM the original table into the new table, then delete the original table.

您可以创建一个具有相同结构但是为空的新表，然后在其上创建唯一键，然后将INSERT IGNORE / SELECT * FROM原始表执行到新表中，然后删除原始表。

INSERT IGNORE will automatically ignore any primary or unique key issues and just skip the duplicates.

INSERT IGNORE将自动忽略任何主要或唯一的密钥问题，并跳过重复项。

#3

Someting like this?

喜欢这样吗？

DELETE FROM myviews WHERE EXISTS(SELECT CONCAT(k1, k2, k) AS dup_value
FROM myviews
GROUP BY dup_value
HAVING (COUNT(dup_value) > 1));

#4

You need a separator in your concat function, because otherwise "a", "b", and "cd" is the same as "abcd", "", "".

在concat函数中需要一个分隔符，因为否则“a”，“b”和“cd”与“abcd”，“”，“”相同。