Mysql删除重复数据，只保留最后一条数据，创建唯一索引

• 1、查询重复记录

SELECT * 
FROM 表名
WHERE 重复字段 IN (SELECT 重复字段 
   FROM 表名
   GROUP BY 重复字段 HAVING COUNT(重复字段)>1);

• 2、删除重复记录，但保留一条

DELETE FROM 表名
WHERE 重复字段 IN (SELECT 重复字段 
           FROM 表名
           GROUP BY 重复字段 HAVING COUNT(重复字段)>1)
      AND Id NOT IN (SELECT MIN(id) 
                     FROM 表名
                     GROUP BY 重复字段 HAVING COUNT(重复字段)>1);

具体操作时，可以用如下结构，：

delete from  table_name where col_name in (select col_name from
 (select col_name from table_name GROUP BY col_name having count(col_name)>1) 
as temp)
and id not in ( select id from
 (select max(id) as id from table_name GROUP BY col_name having count(col_name)>1)
as temp2);

 

然后添加唯一索引：

ALTER TABLE table_name ADD UNIQUE INDEX `uniq_col` (`col_name`);