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.111cn.net |

| 2 |  |

| 3 | http://www.111cn.net |

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

| 5 | http://www.111cn.net |

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

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.net |

| 2 |  |

| 3 | http://www.111cn.net |

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

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.111cn.net |

| 5 | http://www.111cn.net |

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

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.net |

| 3 | http://www.111cn.net |

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

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.net |

| 2 |  |

| 3 | http://www.111cn.net |

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

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.net |

| 2 |  |

| 3 | http://www.111cn.net |

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

| 5 | http://www.111cn.net |

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

5 rows in set (0.00 sec)

 

mysql> select * from demo_new order by id;

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

| id | site |

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

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

| 2 |  |

| 3 | http://www.111cn.net |

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

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.net |

| 2 |  |

| 3 | http://www.111cn.net |

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

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 語句插入數據，再重命名表即可。