MySQL查找并删除重复记录

叁歲伎倆 2022-07-13 11:09 299阅读 0赞

> 项目中有时会遇到查找并删除重复记录的需求，下面谈谈如何实现

#### 新创建一个表【item】 ####

#### 表结构：item\_code varchar(20), value int(11), quantity int(11) where item\_code is the primary key. ####

![SouthEast][]

#### 方法一：使用INNER JOIN和子查询 ####

SELECT item_code, value, item.quantity FROM item INNER JOIN( SELECT quantity FROM item GROUP BY quantity HAVING COUNT(item_code) >1 )temp ON item.quantity= temp.quantity;

##### 输出结果： #####

![SouthEast 1][]

#### 为了得到上面的结果，这里使用了INNER JOIN（内连接查询两个表中所有匹配的数据），再看一下子查询的SQL语句： ####

SELECT quantity FROM item GROUP BY quantity HAVING COUNT(item_code) >1

##### 输出结果： #####

![SouthEast 2][]

#### 方法二：使用INNER JOIN和DISTINCT ####

SELECT distinct a.item_code, a.value, a.quantity
    FROM item a
    INNER JOIN item b ON a.quantity = b.quantity
    WHERE a.item_code <> b.item_code

##### 输出结果： #####

![SouthEast 3][]

#### 第二种方法直接使用INNER JOIN连接表item，但是使用DISTINCT去重，所以，同样能查处重复记录 ####

#### 如果想按某个分类查询出重复记录的条数，SQL如下： ####

SELECT item_code, COUNT( quantity ) x FROM item GROUP BY quantity HAVING x >1

##### 输出结果： #####

![SouthEast 4][]

#### 如果想查询重复记录的总数，SQL如下： ####

SELECT count(*) AS Total_duplicate_count FROM (SELECT item_code FROM item GROUP BY quantity HAVING COUNT(quantity) > 1 )AS x

##### 输出结果： #####

![SouthEast 5][]

> #### 说完查询，下面再来看看如何删除重复记录，大多时候，除了删除重复记录，还想保留一条记录，在测试前最好备份数据，看看如何实现 ####

#### 1. 如果想保留记录中id值最小的那条记录： ####

DELETE n1 FROM names n1, names n2 WHERE n1.id > n2.id AND n1.name = n2.name

#### 2. 如果想保留记录中id值最大的那条记录： ####

DELETE n1 FROM names n1, names n2 WHERE n1.id < n2.id AND n1.name = n2.name

> #### 参考资料： ####
> 
> [http://www.w3resource.com/mysql/advance-query-in-mysql/find-duplicate-data-mysql.php][http_www.w3resource.com_mysql_advance-query-in-mysql_find-duplicate-data-mysql.php]  
> [http://stackoverflow.com/questions/4685173/delete-all-duplicate-rows-except-for-one-in-mysql][http_stackoverflow.com_questions_4685173_delete-all-duplicate-rows-except-for-one-in-mysql]

[SouthEast]: /images/20220713/19a9f3decafb4ade8a8747489411ba16.png
[SouthEast 1]: /images/20220713/76677a3767e24c3eb7dfa2f3608eac43.png
[SouthEast 2]: /images/20220713/b756cdbd9b224f97b7943c6b6061ed43.png
[SouthEast 3]: /images/20220713/ad97afbeb47e44ae9c2dc20eecb1c9ed.png
[SouthEast 4]: /images/20220713/72290007748843349d6e31f71f7f2456.png
[SouthEast 5]: /images/20220713/917342464ef242b58a16b91335c5fca6.png
[http_www.w3resource.com_mysql_advance-query-in-mysql_find-duplicate-data-mysql.php]: http://www.w3resource.com/mysql/advance-query-in-mysql/find-duplicate-data-mysql.php
[http_stackoverflow.com_questions_4685173_delete-all-duplicate-rows-except-for-one-in-mysql]: http://stackoverflow.com/questions/4685173/delete-all-duplicate-rows-except-for-one-in-mysql