mysql 删除单表内多个字段重复的数据

转载地址：http://blog.csdn.net/lingyou37/article/details/17911457

遇到个问题，一个表内两个字段应该加上unique约束，但没加导致出现重复数据，网上找到一条sql可以删掉重复数据，原sql是这样的：

    DELETE from test   
    WHERE (mid,uid) in   
    (SELECT mid,uid FROM test GROUP BY mid,uid HAVING COUNT(*)>1)   
    AND  
    id NOT in (SELECT id FROM test GROUP BY mid,uid HAVING COUNT(*)>1);  

本地会出现这个错误

[Err] 1093 - You can't specify target table 'test' for update in FROM clause  http://dev.mysql.com/doc/refman/5.6/en/subquery-errors.html

在子查询的外面嵌一个select from 就可以了

    DELETE from test   
    WHERE (mid,uid) in   
    (SELECT mid,uid from (SELECT mid,uid FROM test GROUP BY mid,uid HAVING COUNT(*)>1) s1)   
    AND  
    id NOT in (SELECT id from (SELECT id FROM test GROUP BY mid,uid HAVING COUNT(*)>1) s2);  

测试表

    CREATE TABLE `test` (  
      `id` int(11) NOT NULL AUTO_INCREMENT,  
      `mid` int(11) NOT NULL,  
      `uid` int(11) NOT NULL,  
      PRIMARY KEY (`id`)  
    ) ENGINE=InnoDB AUTO_INCREMENT=122 DEFAULT CHARSET=utf8