MySQL 資料庫中重複資料刪除記錄方法總結

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記錄功能就實現了