MySQL复杂重复数据删除_mysql 重复数据删除

业务中遇到要从表里删除重复数据的需求。想当然写了下面的语句。

//示例表：

CREATE TABLE `users` (

`id` int(10) NOT NULL AUTO_INCREMENT,

`name` char(50) NOT NULL,

PRIMARY KEY (`id`)

)

delete from users where id in (select

min(id) from users group by name having count(name)>1);

结果报错：1093  you can't specify target table ....

原因是mysql删除动作不能带有本表的查询动作，意思是你删除users表的东西不能以users表的信息为条件

所以这个语句会报错，执行不了。只要通过创建临时表作为查询条件。如下

delete from users where id in (select * from (select

min(id) from users group by name having count(name)>1));

还要注意 delete from users 这里不能用别名

其他方法。

delete users as a from users as a,( select min(id)  id, name from users group by name having count(name)

> 1

) as bwhere a.name = b.name and a.id <>

b.id;

建立零时表：

create table tmp_users select min(`id`), `name` from users group by name ;

truncate table users;

insert into users select *

from tmp_users;

drop table tmp_users ;