共有三种方法,前两种是利用临时表,下面分别说明:
1、
create temporary table tmp_news select * from news group by nid having count(1) >= 1;
truncate table news;
insert into news select * from tmp_news;
drop table tmp_news;
2、
新建一个临时表
create table tmp_news as select * from news group by nid;
删除原来的表
truncate table youtable;
重命名表
alter table tmp_news rename news;
3、
查找重复的,并且除掉最小的那个。
delete news as a from news as a,
(
select *,min(id) from news group by nid having count(1) > 1
) as b
where a.nid = b.nid and a.id > b.id;