MySQL中删除重复数据只保留一条

1. 查看表中重复的数据

select * from t_user;

重复的数据有4条,username=黄日华，id=2,3,4,5

2. 查询表中重复记录的个数，重复记录是根据单个字段username来判断的

select nickname,username,count(1) from t_user group by nickname,username having count(1)>1;

查询表中重复记录的详情信息，重复记录是根据单个字段username来判断的

select id,username from t_user where username = (select username from t_user group by username having count(1) > 1);

查找表中多余的重复记录（多个字段）

SELECT * FROM t_user t WHERE (t.nickname, t.username) IN (
SELECT nickname,username FROM t_user GROUP BY nickname,username
HAVING count(*) > 1);

3. 删除重复记录,重复记录是根据单个字段（username）来判断，只留有max(id)最大值的记录

delete from t_user where username in (
select username from(
select username from t_user group by username having count(username) > 1) tmp
)
and id not in(
selectid from (
select max(id) as id from t_user group by username having count(username) > 1) tmp
);

select * from t_user;

删除表中多余的重复记录（多个字段），只留有id最大的记录

删除前重复数据如下：

DELETE FROM t_user
WHERE(username, nickname) IN ( select * from (
SELECT username,nickname FROM t_user GROUP BY username,nickname HAVING count(*) > 1)tmp
)
AND id NOT IN (select * from(
SELECT max(id) FROM t_user GROUP BY username,nickname HAVING count(*) > 1)tmp
);