mysql 删除重复行数据

网上查了下资料，很多都是这种

delete from people 
where   peopleName in (select peopleName    from people group by peopleName      having count(peopleName) > 1) 
and   peopleId not in (select min(peopleId) from people group by peopleName     having count(peopleName)>1)

 这种在mysql里是不支持的，会报“You can't specify target table 'people' for update in FROM clause” 的错误（应该是由于mysql在删除的时候加了锁，具体是行锁还是别的锁暂时没有查清楚）。所以修改了上面的语句，加了临时表就可以了。

delete from people
where   peopleName in (select peopleName from (select peopleName    from people group by peopleName      having count(peopleName) > 1) a)
and   peopleId not in (select peopleId from  (select min(peopleId) peopleId from people group by peopleName     having count(peopleName)>1) b) 

 

上面这个虽然可以实现，但是还可以优化，一般peopleId为索引或者为主键，所以先用这个来过滤一部分数据（如果是大表的话，这个非常关键），然后再用另一个条件回表搜索。

delete from people
where  peopleId not in (select peopleId from  (select min(peopleId) peopleId from people group by peopleName     having count(peopleName)>1) b) 
and    peopleName in (select peopleName from (select peopleName    from people group by peopleName      having count(peopleName) > 1) a)