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

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 个小时还没有出结果。

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

在此感谢各位老哥。

第 1 条附言 · 2018-02-26 21:41:33 +08:00

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

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

INSERT IGNORE INTO newtable SELECT * FROM oldtable;

再次感谢各位的答案！

第 2 条附言 · 2018-02-26 21:43:52 +08:00

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

26 条回复 • 2020-01-10 18:00:34 +08:00

blueorange

2018-02-26 17:04:33 +08:00

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

Nick2VIPUser

2018-02-26 17:09:39 +08:00

@blueorange 有加索引的

l00t

2018-02-26 17:10:45 +08:00

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

nosay

2018-02-26 17:11:51 +08:00

3L + 1

st157285231

2018-02-26 17:12:38 +08:00

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

justfindu

2018-02-26 17:19:09 +08:00

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

最快 3L

jsnjfz

2018-02-26 17:23:06 +08:00

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

Immortal

2018-02-26 17:26:13 +08:00

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

Immortal

2018-02-26 17:27:03 +08:00

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

Nick2VIPUser

2018-02-26 21:45:23 +08:00

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

SbloodyS

2018-02-26 21:57:09 +08:00

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

lihongjie0209

2018-02-26 23:46:30 +08:00

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

SbloodyS

2018-02-26 23:49:22 +08:00

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

Nick2VIPUser

2018-02-27 09:31:43 +08:00

@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

2018-02-27 09:53:58 +08:00

@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

2018-02-27 10:54:50 +08:00

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

SbloodyS

2018-02-27 11:08:52 +08:00

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

Arthur001

2018-02-27 14:38:34 +08:00

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

![WL#7395: Deprecate (5.6) and remove (5.7) IGNORE for ALTER TABLE]( https://dev.mysql.com/worklog/task/?id=7395)

Arthur001

2018-02-27 14:42:35 +08:00

我能转载一下吗

zhangyp123

2018-02-27 17:41:20 +08:00

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

Nick2VIPUser

2018-02-28 14:36:21 +08:00

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

Nick2VIPUser

2018-02-28 14:37:13 +08:00

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

Nick2VIPUser

2018-02-28 14:37:51 +08:00

@zhangyp123
@SbloodyS
谢谢各位！

Nick2VIPUser

2018-02-28 14:38:49 +08:00

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

xjroot

2018-10-01 16:40:57 +08:00

已收藏，好帖

chaodada

2020-01-10 18:00:34 +08:00

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