MySQL 5.5 Community Server MySQL 5.6 Community Server Percona Configuration Wizard XtraBackup 搭建主从复制 Great Sites on MySQL Percona MySQL Performance Blog Severalnines 推荐管理工具 Sequel Pro phpMyAdmin 推荐书目 MySQL Cookbook
MySQL 相关项目 MariaDB Drizzle 参考文档 http://mysql-python.sourceforge.net/MySQLdb.html
MySQL 相关项目 MariaDB Drizzle 参考文档 http://mysql-python.sourceforge.net/MySQLdb.html 
[mysql 去重问题] 如何高效的删除数据表中的重复数据？
Nick2VIPUser nickliqian 2018-02-26 16:59:52 +08:00 11655 次点击这是一个创建于 2855 天前的主题，其中的信息可能已经有所发展或是发生改变。
有一个数据表，共三个字段：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 个小时还没有出结果。
求问有什么高效的方法可以替代这个方法呢？
在此感谢各位老哥。
第 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引擎。
 | 1 blueorange 2018-02-26 17:04:33 +08:00  1 执行 sql 之前 加索引呢？ 尝试了没有？ |
 | 2 Nick2VIPUser OP @blueorange 有加索引的 |
 | 3 l00t 2018-02-26 17:10:45 +08:00 1 建个新表插一遍不重的数据，然后把老表 drop 掉，再把新表名字改成老表的。 |
 | 4 nosay 2018-02-26 17:11:51 +08:00 1 3L + 1 |
 | 5 st157285231 2018-02-26 17:12:38 +08:00 1 取出全部数据，然后做去重处理，筛选出重复 ID，然后 delete from xx where id in 1,2,3 |
 | 6 justfindu 2018-02-26 17:19:09 +08:00 1 delete * from table where table_id not in ( select table_id from table group by data) ? 哦 我不知道效率如何 哈哈哈, in 效率估计会爆炸, mysql5.7 下会快很多.. 最快 3L |
 | 7 jsnjfz 2018-02-26 17:23:06 +08:00 1 |
 | 8 Immortal 2018-02-26 17:26:13 +08:00 1 之前看<高性能 mysql>这书的时候貌似看到过一样的情况 alter ignore table 表名 add unique index(列名); |
| 9 Immortal 2018-02-26 17:27:03 +08:00 1 |
| 10 Nick2VIPUser OP |
 | 11 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 ); |
 | 12 lihongjie0209 2018-02-26 23:46:30 +08:00 @SbloodyS #11 和我之前的做法一样 |
| 13 SbloodyS 2018-02-26 23:49:22 +08:00 @lihongjie0209 哈哈，我之前就是这么做的 |
| 14 Nick2VIPUser OP @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 ``` 貌似这个版本不支持这么做-_- |
| 15 lihongjie0209 2018-02-27 09:53:58 +08:00 2 @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 ) 按照这个改一下, 这是我之前用的 |
16 annielong 2018-02-27 10:54:50 +08:00 关键是怎么定义重复的数据才麻烦，我目前就遇到四个字段都一样才是重复 |
| 17 SbloodyS 2018-02-27 11:08:52 +08:00 1 @Nick2VIPUser 噢，忘记了，Mysql 不支持 Delete 中 Select，我这个使用在 PG 里的 0.0......那可以先执行 select 然后把 ID 复制出来手动填入 Delete 的 In 中 23333 |
 | 18 Arthur001 2018-02-27 14:38:34 +08:00 5.7 以后就不支持 alter ignore table 表名 add unique index(列名); 这样的语法了  |
| 19 Arthur001 2018-02-27 14:42:35 +08:00 我能转载一下吗 |
 | 20 zhangyp123 2018-02-27 17:41:20 +08:00 1 刚验证了一下，alter ignore table 表名 add unique index(列名); 5.6 是可行的，5.7 就不支持了 |
| 21 Nick2VIPUser OP @lihongjie0209 谢谢您帮我找出来，我试一下！ |
| 22 Nick2VIPUser OP @Arthur001 对，我在 stackoverflow 也看到了这个。可以转载的~ |
 | 23 Nick2VIPUser OP |
| 24 Nick2VIPUser OP @annielong 我遇到的需求是只有一个字段重复-_-，mysql 也许可以把四个字段结合起来整理成一个 hash 值然后通过 hash 值判断？ |
 | 25 xjroot 2018-10-01 16:40:57 +08:00 已收藏，好帖 |
 | 26 chaodada 2020-01-10 18:00:34 +08:00 @lihongjie0209 #15 感谢楼主 参考您的语句解决了我的问题 |
Select Language