关于MySql删除表中重复数据

今天在删除MySQL表中重复数据的时候，遇到一些问题，在这里做一些笔记。

创建一个表测试

create table user(
	id int  not null primary key auto_increment,
	name varchar(20),
	password varchar(20)
)

为表添加数据

接下来查询重复数据

	select username from n_user group by username having count(username)>1

结果

删除重复数据

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

结果

查了一下，MySQL在查询后又执行删除操作就会报这个错，解决办法是在查询语句外面再嵌套一层查询，如下

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

这里还有一个套就是如果没给查询设置别名，报这个错

错误解决，再次执行，就可以成功删除表中重复数据了

要保留重复数据中的一条记录，这样写

delete from user  where name in(
	select name from(
		select name from user group by name having count(name)>1
	) as aa
) and id not in(
	select id from(
		select min(id) id from user group by name having count(name)>1
	) as ab
)