MySQL 數據庫中刪除重復記錄方法總結

本文章總結了關於在mysql數據庫中各種刪除重復記錄的sql語句，下面我們用實例介紹了操作方法，大家可參考一下。

MYSQL數據庫中，經常會遇到重復記錄的情況，那麼就需要SQL刪除重復記錄，下面為您列舉了四種刪除重復記錄的方式，用於不同的情況，希望對您有所幫助。

1、查找表中多余的重復記錄，重復記錄是根據單個字段（peopleId）來判斷

 代碼如下 復制代碼 select * from people  where peopleId in (select   peopleId from   people group by   peopleId having count(peopleId) > 1)    

 

2、SQL刪除重復記錄，重復記錄是根據單個字段（peopleId）來判斷，只留有rowid最小的記錄

 代碼如下 復制代碼 delete from people  where peopleId in (select   peopleId from people group by   peopleId   having count(peopleId) > 1)  and rowid not in (select min(rowid) from   people group by peopleId having count(peopleId )>1)    

  
  

3、查找表中多余的重復記錄（多個字段）

 代碼如下 復制代碼 select * from vitae a  where (a.peopleId,a.seq) in   (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)   

 

 
4、刪除表中多余的重復記錄（多個字段），只留有rowid最小的記錄

 代碼如下 復制代碼

delete from vitae a  where (a.peopleId,a.seq) in   (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)  and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)

演示數據

表結構：

 

 代碼如下 復制代碼

mysql> desc demo;

+-------+------------------+------+-----+---------+----------------+

| Field | Type | Null | Key | Default | Extra |

+-------+------------------+------+-----+---------+----------------+

| id | int(11) unsigned | NO | PRI | NULL | auto_increment |

| site | varchar(100) | NO | MUL | | |

+-------+------------------+------+-----+---------+----------------+

2 rows in set (0.00 sec)

數據：

mysql> select * from demo order by id;

+----+------------------------+

| id | site |

+----+------------------------+

| 1 | http://www.111cn.netn |

| 2 | http://安卓主題_www.111cn.net |

| 3 | http://www.zhutiy.com |

| 4 | http://www.111cn.netn |

| 5 | http://www.zhutiy.com |

+----+------------------------+

5 rows in set (0.00 sec)

當沒有創建表或創建索引權限的時候，可以用下面的方法：

如果你要刪除較舊的重復記錄，可以使用下面的語句：

 代碼如下 復制代碼

mysql> delete from a 

-> using demo as a, demo as b

-> where (a.id > b.id)

-> and (a.site = b.site);

Query OK, 2 rows affected (0.12 sec)

 

mysql> select * from demo order by id;

+----+------------------------+

| id | site |

+----+------------------------+

| 1 | http://www.111cn.netn |

| 2 | http://安卓主題_www.111cn.net |

| 3 | http://www.zhutiy.com |

+----+------------------------+

3 rows in set (0.00 sec)

如果你要刪除較新的重復記錄，可以使用下面的語句：

 代碼如下 復制代碼

mysql> delete from a 

-> using demo as a, demo as b

-> where (a.id < b.id)

-> and (a.site = b.site);

Query OK, 2 rows affected (0.12 sec)

 

mysql> select * from demo order by id;

+----+------------------------+

| id | site |

+----+------------------------+

| 2 | http://安卓主題_www.111cn.net |

| 4 | http://www.111cn.netn |

| 5 | http://www.zhutiy.com |

+----+------------------------+

3 rows in set (0.00 sec)

你可以用下面的語句先確認將被刪除的重復記錄：

 

 代碼如下 復制代碼

mysql> SELECT a.* 

-> FROM demo a, demo b

-> WHERE a.id > b.id

-> AND (a.site = b.site);

+----+------------------------+

| id | site |

+----+------------------------+

| 1 | http://www.111cn.netn |

| 3 | http://www.zhutiy.com |

+----+------------------------+

2 rows in set (0.00 sec)

如果有創建索引的權限，可以用下面的方法：

在表上創建唯一鍵索引：

 代碼如下 復制代碼

mysql> alter ignore table demo add unique index ukey (site);

Query OK, 5 rows affected (0.46 sec)

Records: 5 Duplicates: 2 Warnings: 0

 

mysql> select * from demo order by id;

+----+------------------------+

| id | site |

+----+------------------------+

| 1 | http://www.111cn.netn |

| 2 | http://安卓主題_www.111cn.net |

| 3 | http://www.zhutiy.com |

+----+------------------------+

3 rows in set (0.00 sec)

 

重復記錄被刪除後，如果需要，可以刪除索引：

 代碼如下 復制代碼

mysql> alter table demo drop index ukey;

Query OK, 3 rows affected (0.37 sec)

Records: 3 Duplicates: 0 Warnings: 0

如果有創建表的權限，可以用下面的方法：

創建一個新表，然後將原表中不重復的數據插入新表：

 代碼如下 復制代碼

mysql> create table demo_new as select * from demo group by site;

Query OK, 3 rows affected (0.19 sec)

Records: 3 Duplicates: 0 Warnings: 0

 

mysql> show tables;

+----------------+

| Tables_in_test |

+----------------+

| demo |

| demo_new |

+----------------+

2 rows in set (0.00 sec)

 

mysql> select * from demo order by id;

+----+------------------------+

| id | site |

+----+------------------------+

| 1 | http://www.111cn.netn |

| 2 | http://安卓主題_www.111cn.net |

| 3 | http://www.zhutiy.com |

| 4 | http://www.111cn.netn |

| 5 | http://www.zhutiy.com |

+----+------------------------+

5 rows in set (0.00 sec)

 

mysql> select * from demo_new order by id;

+----+------------------------+

| id | site |

+----+------------------------+

| 1 | http://www.111cn.netn |

| 2 | http://安卓主題_www.111cn.net |

| 3 | http://www.zhutiy.com |

+----+------------------------+

3 rows in set (0.00 sec)

然後將原表備份，將新表重命名為當前表：

 

 代碼如下 復制代碼

mysql> rename table demo to demo_old, demo_new to demo;

Query OK, 0 rows affected (0.04 sec)

mysql> show tables;

+----------------+

| Tables_in_test |

+----------------+

| demo |

| demo_old |

+----------------+

2 rows in set (0.00 sec)

 

mysql> select * from demo order by id;

+----+------------------------+

| id | site |

+----+------------------------+

| 1 | http://www.111cn.netn |

| 2 | http://安卓主題_www.111cn.net |

| 3 | http://www.zhutiy.com |

+----+------------------------+

3 rows in set (0.00 sec)

注意：使用這種方式創建的表會丟失原表的索引信息！

 代碼如下 復制代碼

mysql> desc demo;

+-------+------------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+------------------+------+-----+---------+-------+

| id | int(11) unsigned | NO | | 0 | |

| site | varchar(100) | NO | | | |

+-------+------------------+------+-----+---------+-------+

2 rows in set (0.00 sec)

如果要保持和原表信息一致，你可以使用 show create table demo; 來查看原表的創建語句，然後使用原表的創建語句創建新表，接著使用 insert … select 語句插入數據，再重命名表即可。

實例

今天無意導入幾個測試數據，發現測試數據中，有很多數據記錄是相同的，我現在就想刪除掉這些數據，
如何查詢數據相同記錄呢？這個好說，以下語句就可以查看相同記錄的了：

 代碼如下 復制代碼 SELECT COUNT(*) AS c, key_word FROM search_keywrod GROUP BY key_word HAVING c > 1 

其中 HAVING c >1 代表相同記錄數就有相同的了。

查詢相同的是比較容易實現的了，但是想要刪除這些重復的，估計就比較麻煩的了，因為你是要刪除自身表裡的記錄，有些朋友可能就會使用到臨時表，把相同需要刪除的記錄，導到時這個臨時表，然後再通過臨時表來刪除主表。或者寫一個臨時程序，刪除掉其中的一條重復記錄。

以上二個方法刪除重復記錄，最大的麻煩就是操作繁瑣。而我們今天介紹的就是利用MYSQL自身的語句，不創建臨時表，不寫程序來刪除掉自身的重復記錄。請看以下SQL語句：

 代碼如下 復制代碼 SELECT t1.id, t1.key_word  
FROM search_keywrod t1, ( 
SELECT key_word, MIN(id) AS minid  
FROM search_keywrod  
GROUP BY key_word HAVING COUNT(key_word) > 1 
)t2  
WHERE t1.key_word = t2.key_word AND t1.id = t2.minid 

這條語句就跟我們第一條語句是一樣的功能，但是這條語句好處就是MIN(id)，可以控制是刪除大的id重復記錄(MAX)，還是刪除小的id重復記錄（MIN）。

OK，經過改良的語句實現了查詢，現在就可以利用DELETE FROM語句來刪除了。

 代碼如下 復制代碼

DELETE FROM search_keywrod WHERE id IN (SELECT id FROM ( 
 
SELECT t1.id 
FROM search_keywrod t1, ( 
SELECT key_word, MIN(id) AS minid FROM search_keywrod  
GROUP BY key_word HAVING COUNT(key_word) > 1 
)t2  
WHERE t1.key_word = t2.key_word AND t1.id = t2.minid 
 
)t3) 

執行時，請多執行幾次，因為每次刪除時，只刪除掉重復的一次記錄，如果你一條記錄重復五次，那你就要執行五次的了。所以多執行幾次，直到沒有可刪除的記錄了，這樣你直接一條語句刪除掉重復的mysql記錄功能就實現了