mysql 删除重复项

在table中，由于之前没有设置primary key，导致代码运行replace 等sql语句时重复插入了数据库项。在整理数据库时，需要删除重复项，并保留一项。使用了如下sql 语句。

下面这个语句会删除guide重复项，并保留time最大的那项。

创建临时表
create table tmp (select * from (select * from sys_guide where uid = 4) a group by a.guide having count(a.guide)>1);
删除数据
delete from sys_guide where uid in (select uid from tmp) and guide in (select guide from tmp);
重新插入临时表数据
insert into sys_guide (select * from tmp);
删除临时表
drop table tmp;

用下面这个sql语句来检查重复项是否删除，如果结果为空，则重复项已被删除。

select  *  from sys_guide  where uid = 4 group  by  guide   having  count(guide) > 1