有一个数据表,共三个字段: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 的答案,解决了问题。
newtable
,给指定data
字段设置好unique
约束sql
语句将旧表的数据全部INSERT转移到新表,由于使用了IGNORE
选项这样遇到重复数据会忽略,直到将所有数据插入完成INSERT IGNORE INTO newtable SELECT * FROM oldtable;
再次感谢各位的答案!
补充:以上方法使用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(列名); 这样的语法了
![WL#7395: Deprecate (5.6) and remove (5.7) IGNORE for ALTER TABLE]( https://dev.mysql.com/worklog/task/?id=7395) |
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 感谢楼主 参考您的语句解决了我的问题
|