MYSQL 增加唯一索引对于重复数据的处理

查看重复数据

select xx,yy,zz,COUNT(1) as num from TABLE1 group by xx,yy,zz having num>1;

获取重复数据最小的记录

select min(id) id,xx,yy,zz from TABLE1 group by xx,yy,zz having count(1)>1;

删除重复数据，保留最小id记录

delete a.* from TABLE1 as a,( select min(id) id,xx,yy,zz from TABLE1 group by xx,yy,zz having count(1)>1
) as b where a.xx=b.xx and a.yy=b.yy and a.zz=b.zz and a.id > b.id;

删除重复数据，保留最大id记录

delete a.* from TABLE1 as a,( select max(id) id,xx,yy,zz from TABLE1 group by xx,yy,zz having count(1)>1
) as b where a.xx=b.xx and a.yy=b.yy and a.zz=b.zz and a.id < b.id;

增加唯一索引

ALTER TABLE TABLE1 ADD UNIQUE INDEX uniq_xx_yy_zz(xx,yy,zz);