今天在删除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
)