mysql 删除重复记录

这几天做到两次考数据库删除重复记录的问题，整理一下防止忘记。

1.数据源，如图1-1 所示，在名为tb1的表中，a和b的数据是有重复的。现在要删除掉重复的记录

图1-1  数据源

先用GROUP BY 分组试一下：

SELECT MIN(id) ,name ,age FROM tb1 GROUP BY name,age ;

结果如图1-2 所示 ： 

图1-2 使用GROUP BY之后的查询结果

从图1-1,1-2可以看出来id不在图1-2中的2,3,5的是重复记录，是要删除的，就可以使用NOT IN 来删除 。

但是NOT IN （A,B,C）只能是单列，（这里是id）,所以这里就不能够直接使用复合语句来达到删除重复项的目的，这里可以建一个临时表或者临时的视图来保存group 之后的数据，在从临时表或者视图里边SELECT id 出来，这样就能够满足NOT IN 的要求了。

1.建立临时表 tb2,

CREATE TABLE tb2 AS SELECT MIN(id) AS id,name,age FROM tb1 GROUP BY name ;

 

图1-3 临时表和临时表中的数据

2. 利用临时表删除重复的记录 

DELETE FROM tb1 WHERE id NOT IN ( SELECT id FROM tb2 );

图1-4 利用临时表删除重复的数据以及删除的结果

3.删除临时表 

这里也可以使用视图的方式来删除 ：

2.2  ·创建临时视图v1,这次保留id较大的数据

CREATE VIEW v1 AS SELECT MAX(id) AS id ,name ,age FROM tb1 GROUP BY name ,age ;

图1-5 临时视图以及视图内容

2.3 同样的方法删除重复项

DELETE FROM tb1 WHERE id NOT IN (SELECT id FROM v1);

图 1-6 删除后的结果

最后删除掉临时的表或者视图就可以了。

这里值得注意的是 ： 将选中的id要取别名，（SELECT MAX(id) AS id）, 因为不取别名的话，临时表或者视图的这一列的列名就是MAX(id)或者MIN（id）,因为MAX 和MIN 本身又是函数所以会出问题。

同样的我们也可以使用COUNT 函数来删除重复数大于2或者其他条件的数据：

1.先用count函数查看每个记录的重复数 并创建临时视图：

CREATE VIEW v1 AS SELECT COUNT(*) AS num ,MIN(id) AS id,  name ,age  FROM tb1 GROUP BY name ,age ;

图1-7 利用count函数查看重复数

2.删除重复数大于2 的记录

DELETE FROM tb1 WHERE  ID  NOT IN ( SELECT id FROM v1 ) AND name IN ( SELECT name FROM v1 WHERE num > 2 );

图1-7 删除重复数大于2 的结果

3.删除临时视图

对于临时表和临时视图，我更喜欢临时视图，因为视图只是对SELECT 语句的封装，临时表则是实实在在创建出来的表，会占用存储空间，如果后边搞忘了删除掉临时表或者视图，数据量大的话，临时表会浪费很多资源，临时视图也只是一个SQL语句而已，不会占用很多资源，而且创建临时表本质上（我觉得）是先查询，再插入到新表，而视图则只是查询，少了插入的步骤，所以我觉得视图应该是更快的。