[mysql 去重问题] 如何高效的删除数据表中的重复数据？

[Nick2VIPUser]

有一个数据表，共三个字段：id，data，datetime。

其中 data 是不能重复的，但是由于粗心在最开始的时候忘记给data字段设置了unique约束。 在插入数据的过程中造成了大量重复。

目前: 数据总量（count(* )）是 50W，不重复数据量 （distinct(data) ） 35W。

现在的需求是把重复的数据删除掉且只保留其中一条。

使用如下语句进行去重（网络上找的）：

delete from bj where data_info in
 (select data_info from
 ( select data_info from bj group by data_info having count(data_info)>1) a) 
and id not in 
( select min(id) from 
(select min(id) as id from bj group by data_info having count(data_info)>1 ) b)

先测试了总量为 1000 的数据，功能是 OK 的。

然后在单核 2G 的云主机上对 50W 总量的数据跑这个 sql，跑了超过 1 个小时还没有出结果。

求问有什么高效的方法可以替代这个方法呢？

在此感谢各位老哥。

感谢各位的答案！ 综合 3L @l00t 和 8L @Immortal 的答案，解决了问题。

1. 先建立一个同样字段的新表newtable，给指定data字段设置好unique约束
2. 使用如下sql语句将旧表的数据全部INSERT转移到新表，由于使用了IGNORE选项这样遇到重复数据会忽略，直到将所有数据插入完成

INSERT IGNORE INTO newtable SELECT * FROM oldtable;

再次感谢各位的答案！

补充：以上方法使用innodb引擎3分钟30秒完成数据插入和去重，暂没有测试MyISAM引擎。

[blueorange]

执行 sql 之前 加索引呢？ 尝试了没有？

[Nick2VIPUser]

@blueorange 有加索引的

[l00t]

建个新表插一遍不重的数据，然后把老表 drop 掉，再把新表名字改成老表的。

[nosay]

3L + 1

[st157285231]

取出全部数据，然后做去重处理，筛选出重复 ID，然后 delete from xx where id in 1,2,3

[justfindu]

delete * from table where table_id not in ( select table_id from table group by data) ?
哦 我不知道效率如何 哈哈哈, in 效率估计会爆炸, mysql5.7 下会快很多..

最快 3L

[jsnjfz]

试试 exists?
参考 http://blog.csdn.net/ldl22847/article/details/7800572

[Immortal]

之前看<高性能 mysql>这书的时候貌似看到过一样的情况
alter ignore table 表名 add unique index(列名);

[Immortal]

参考[这里]( http://www.cnblogs.com/sunss/archive/2010/10/23/1858874.html)和书上基本一致

[Nick2VIPUser]

@l00t
@nosay
@st157285231
@justfindu
@jsnjfz
@Immortal
感谢各位，综合几种答案已经将问题解决！

[SbloodyS]

一般慢的话可以对相应字段加索引，子查询中不要嵌套子查询 SQL 效率比较高
问题的删除 SQL 用这个会效率比较高
DELETE
FROM
bj
WHERE
id NOT IN (
SELECT
min(id)
FROM
bj
GROUP BY
data
);

[lihongjie0209]

@SbloodyS #11 和我之前的做法一样

[SbloodyS]

@lihongjie0209 哈哈，我之前就是这么做的

[Nick2VIPUser]

@SbloodyS @lihongjie0209 谢谢！刚刚试了一下，在 5.7.17 版本 mysql 下会报错：
```
mysql> DELETE FROM bj WHERE id NOT IN (SELECT min(id) FROM bj GROUP BY data_info);
ERROR 1093 (HY000): You can't specify target table 'bj' for update in FROM clause
```
貌似这个版本不支持这么做-_-

[lihongjie0209]

@Nick2VIPUser

DELETE
FROM
signin
WHERE
signin.id NOT IN (
SELECT
*
FROM
(
SELECT
MAX(id)
FROM
signin
GROUP BY
signin.student_id,
signin.question_id
) AS t
)

按照这个改一下, 这是我之前用的

[annielong]

关键是怎么定义重复的数据才麻烦，我目前就遇到四个字段都一样才是重复

[SbloodyS]

@Nick2VIPUser 噢，忘记了，Mysql 不支持 Delete 中 Select，我这个使用在 PG 里的 0.0......那可以先执行 select 然后把 ID 复制出来手动填入 Delete 的 In 中 23333

[Arthur001]

5.7 以后就不支持 alter ignore table 表名 add unique index(列名); 这样的语法了


[Arthur001]

我能转载一下吗

[zhangyp123]

刚验证了一下，alter ignore table 表名 add unique index(列名); 5.6 是可行的，5.7 就不支持了

[Nick2VIPUser]

@lihongjie0209 谢谢您帮我找出来，我试一下！

[Nick2VIPUser]

@Arthur001 对，我在 stackoverflow 也看到了这个。可以转载的~

[Nick2VIPUser]

@zhangyp123
@SbloodyS
谢谢各位！

[Nick2VIPUser]

@annielong 我遇到的需求是只有一个字段重复-_-，mysql 也许可以把四个字段结合起来整理成一个 hash 值然后通过 hash 值判断？

[xjroot]

已收藏，好帖

[chaodada]

@lihongjie0209 #15 感谢楼主 参考您的语句解决了我的问题