sql删除重复数据

方法一

例如表字段的name重复

select * FROM user
WHERE user_id NOT IN (
    SELECT MIN(user_id )
    FROM user
    GROUP BY name
);

方法二

如果方法一sql执行太慢了，使用方法二分批删除。

1、先查询出有重复的name

select name from user group by name having count(name) > 1

2、查询出要保留的记录，复制insert语句

select * from user where id in (
    select MIN(id) from user where name in (
        select name from user group by name having count(name) > 1
    ) group by name
)

3、删除所有重复的记录

delete from user where name in (
   select name from user group by name having count(name) > 1
)

4、插入第二步保存的记录