如何删除MySQL表中的重复项？

Question

我需要在MySQL表上为指定的sid DELETE重复的行。

如何使用SQL查询执行此操作？

DELETE (DUPLICATED TITLES) FROM table WHERE SID = "1"

就像这样，但我不知道该怎么做。

Answer 1

这将就地删除重复项，而无需创建新表

ALTER IGNORE TABLE `table_name` ADD UNIQUE (title, SID)

注意:只有在索引适合内存的情况下才能正常工作

Answer 2

假设您有一个表employee，其中包含以下列：

employee (first_name, last_name, start_date)

要删除具有重复first_name列的行：

delete
from employee using employee,
    employee e1
where employee.id > e1.id
    and employee.first_name = e1.first_name  

Answer 3

在位删除MySQL中的重复行(假设您有时间戳col可作为排序依据)演练：

创建表并插入一些行：

create table penguins(foo int, bar varchar(15), baz datetime);
insert into penguins values(1, 'skipper', now());
insert into penguins values(1, 'skipper', now());
insert into penguins values(3, 'kowalski', now());
insert into penguins values(3, 'kowalski', now());
insert into penguins values(3, 'kowalski', now());
insert into penguins values(4, 'rico', now());
select * from penguins;
    +------+----------+---------------------+
    | foo  | bar      | baz                 |
    +------+----------+---------------------+
    |    1 | skipper  | 2014-08-25 14:21:54 |
    |    1 | skipper  | 2014-08-25 14:21:59 |
    |    3 | kowalski | 2014-08-25 14:22:09 |
    |    3 | kowalski | 2014-08-25 14:22:13 |
    |    3 | kowalski | 2014-08-25 14:22:15 |
    |    4 | rico     | 2014-08-25 14:22:22 |
    +------+----------+---------------------+
6 rows in set (0.00 sec)

就地删除重复项：

delete a
    from penguins a
    left join(
    select max(baz) maxtimestamp, foo, bar
    from penguins
    group by foo, bar) b
    on a.baz = maxtimestamp and
    a.foo = b.foo and
    a.bar = b.bar
    where b.maxtimestamp IS NULL;
Query OK, 3 rows affected (0.01 sec)
select * from penguins;
+------+----------+---------------------+
| foo  | bar      | baz                 |
+------+----------+---------------------+
|    1 | skipper  | 2014-08-25 14:21:59 |
|    3 | kowalski | 2014-08-25 14:22:15 |
|    4 | rico     | 2014-08-25 14:22:22 |
+------+----------+---------------------+
3 rows in set (0.00 sec)

你完成了，重复的行被删除了，最后一行保留了时间戳。

对于那些没有时间戳或唯一列的人。

您没有timestamp或唯一索引列作为排序依据吗？你生活在一种退化的状态。您必须执行额外的步骤来删除重复的行。

创建企鹅表并添加一些行

create table penguins(foo int, bar varchar(15)); 
insert into penguins values(1, 'skipper'); 
insert into penguins values(1, 'skipper'); 
insert into penguins values(3, 'kowalski'); 
insert into penguins values(3, 'kowalski'); 
insert into penguins values(3, 'kowalski'); 
insert into penguins values(4, 'rico'); 
select * from penguins; 
    # +------+----------+ 
    # | foo  | bar      | 
    # +------+----------+ 
    # |    1 | skipper  | 
    # |    1 | skipper  | 
    # |    3 | kowalski | 
    # |    3 | kowalski | 
    # |    3 | kowalski | 
    # |    4 | rico     | 
    # +------+----------+ 

克隆第一个表并复制到其中。

drop table if exists penguins_copy; 
create table penguins_copy as ( SELECT foo, bar FROM penguins );  

#add an autoincrementing primary key: 
ALTER TABLE penguins_copy ADD moo int AUTO_INCREMENT PRIMARY KEY first; 

select * from penguins_copy; 
    # +-----+------+----------+ 
    # | moo | foo  | bar      | 
    # +-----+------+----------+ 
    # |   1 |    1 | skipper  | 
    # |   2 |    1 | skipper  | 
    # |   3 |    3 | kowalski | 
    # |   4 |    3 | kowalski | 
    # |   5 |    3 | kowalski | 
    # |   6 |    4 | rico     | 
    # +-----+------+----------+ 

最大聚合对新的moo索引进行操作：

delete a from penguins_copy a left join( 
    select max(moo) myindex, foo, bar 
    from penguins_copy 
    group by foo, bar) b 
    on a.moo = b.myindex and 
    a.foo = b.foo and 
    a.bar = b.bar 
    where b.myindex IS NULL; 

#drop the extra column on the copied table 
alter table penguins_copy drop moo; 
select * from penguins_copy; 

#drop the first table and put the copy table back: 
drop table penguins; 
create table penguins select * from penguins_copy; 

观察和清理

drop table penguins_copy; 
select * from penguins;
+------+----------+ 
| foo  | bar      | 
+------+----------+ 
|    1 | skipper  | 
|    3 | kowalski | 
|    4 | rico     | 
+------+----------+ 
    Elapsed: 1458.359 milliseconds 

那么大的SQL delete语句在做什么？

别名为'a‘的桌上企鹅在别名为'b’的桌上企鹅的子集上左连接。右手边的表'b‘是一个子集，它找到按列foo和bar分组的最大时间戳或最大moo。这与左边的表格'a‘相匹配。(foo，bar，baz)在左边有表中的每一行。右手边的子集'b‘有一个(maxtimestamp，foo，bar)，它只在最大的那个上匹配到左边。

不是最大值的每一行都有值maxtimestamp为NULL。向下过滤这些空行，您将得到一组按foo和bar分组的所有行，这不是最新的时间戳baz。删除那些。

在运行此命令之前，请备份该表。

防止此问题在此表中再次发生：

如果你让它工作，并且它扑灭了你的“重复行”的火。太棒了。现在在您的表上定义一个新的组合唯一键(在这两列上)，以防止从一开始就添加更多的重复项。

就像良好的免疫系统一样，在插入时，甚至不应该允许坏行进入表中。后来，所有那些添加重复的程序都会广播他们的抗议，当你修复它们时，这个问题再也不会出现了。