程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> mysql刪除數據庫重復記錄實現方法

mysql刪除數據庫重復記錄實現方法

編輯:MySQL綜合教程

文章詳細的利用了一個實例來告訴我們如何在mysql刪除數據庫重復記錄的實現方法與原理,有需要的同學可以參考一下下哦。

演示數據

表結構:

 

 代碼如下 復制代碼

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.bKjia.c0m |

| 2 |  |

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

| 4 | http://www.bKjia.c0m |

| 5 | http://www.zhutiai.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.bKjia.c0m |

| 2 |  |

| 3 | http://www.zhutiai.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 |  |

| 4 | http://www.bKjia.c0m |

| 5 | http://www.zhutiai.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.bKjia.c0m |

| 3 | http://www.zhutiai.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.bKjia.c0m |

| 2 |  |

| 3 | http://www.zhutiai.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.bKjia.c0m |

| 2 |  |

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

| 4 | http://www.bKjia.c0m |

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

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

5 rows in set (0.00 sec)

 

mysql> select * from demo_new order by id;

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

| id | site |

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

| 1 | http://www.bKjia.c0m |

| 2 |  |

| 3 | http://www.zhutiai.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.bKjia.c0m |

| 2 |  |

| 3 | http://www.zhutiai.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 語句插入數據,再重命名表即可。

 

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved