id 标题 内容 SY_key SY_jianjie hit 1 a .. .. ... .. 2 b .. .. ... .. 3 c .. .. ... .. 4 a .. .. ... .. ------------------- 只要标题不重复就可以了。
假设ID唯一 select * from tt a where not exists(select 1 from tt where a.标题=标题 and a.id<id)
delete a from tt a inner join tt b on a.标题=b.标题 and a.id<b.id
delete data_content_1 from data_content_1 inner join data_content_2 on data_content_1.标题=data_content_2.标题 and data_content_1.id<data_content_2.id是不是这样写? 把原表复制一份,然后比较下? ------ 50W条数据,这样写机器直接卡死了~
由于mySQL不能在查询表的同时修改表, 解决思路:创建一个临时表,再对比删除记录,删除临时表 create table tmp as select min(id) as col1 from a group by 标题; delete from a where id not in (select col1 from tmp); drop table tmp;
哦,忘记了,a要换成你的表名data_content_1 create table tmp as select min(id) as col1 from data_content_1 group by 标题; delete from data_content_1 where id not in (select col1 from tmp); drop table tmp;
1 a .. .. ... ..
2 b .. .. ... ..
3 c .. .. ... ..
4 a .. .. ... ..
-------------------
只要标题不重复就可以了。
select * from tt a where not exists(select 1 from tt where a.标题=标题 and a.id<id)
from data_content_1
inner join data_content_2 on data_content_1.标题=data_content_2.标题
and data_content_1.id<data_content_2.id是不是这样写?
把原表复制一份,然后比较下?
------
50W条数据,这样写机器直接卡死了~
解决思路:创建一个临时表,再对比删除记录,删除临时表
create table tmp as select min(id) as col1 from a group by 标题;
delete from a where id not in (select col1 from tmp);
drop table tmp;
create table tmp as select min(id) as col1 from data_content_1 group by 标题;
delete from data_content_1 where id not in (select col1 from tmp);
drop table tmp;
建立临时表(以title为主键,这样标题就不会重复)
3g大小数据库的来回到腾了下,速度还能接受。斑竹的方法,一折腾就mysql就卡死机了,速度接受不了
还有个兄弟方法还在试
这个应该对你有用