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 b   where 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 ;




    留下足迹