Summary of methods for deleting duplicate records in MYSQL database [recommended]_mysql

Table structure:
Mysql> DESC Demo;
+-------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+----------------+
| ID | int (one) unsigned | NO | PRI | NULL | auto_increment |
| site | varchar (100) | NO | MUL | | |
+-------+------------------+------+-----+---------+----------------+
2 rows in Set (0.00 sec)

Data:
Mysql> SELECT * FROM demo order by ID;
+----+------------------------+
| ID | site |
+----+------------------------+
| 1 | http://www.CodeBit.cn |
| 2 | http://YITU.org |
| 3 | http://www.ShuoWen.org |
| 4 | http://www.CodeBit.cn |
| 5 | http://www.ShuoWen.org |
+----+------------------------+
5 rows in Set (0.00 sec)

When you do not create a table or create INDEX permissions, you can use the following method:

If you want to delete older duplicate records, you can use the following statement:
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.CodeBit.cn |
| 2 | http://YITU.org |
| 3 | http://www.ShuoWen.org |
+----+------------------------+
3 Rows in Set (0.00 sec)

If you want to delete newer duplicate records, you can use the following statement:
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://YITU.org |
| 4 | http://www.CodeBit.cn |
| 5 | http://www.ShuoWen.org |
+----+------------------------+
3 Rows in Set (0.00 sec)

You can use the following statement to confirm the duplicate records that will be deleted:
Mysql> SELECT a.*
-> from demo A, demo b
-> WHERE a.id > b.ID
-> and (a.site = B.site);
+----+------------------------+
| ID | site |
+----+------------------------+
| 1 | http://www.CodeBit.cn |
| 3 | http://www.ShuoWen.org |
+----+------------------------+
2 rows in Set (0.00 sec)

If you have permission to create an index, you can use the following method:

To create a unique key index on a table:

 
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.CodeBit.cn | 
| 2 | http://YITU.org | 
| 3 | http://www.ShuoWen.org | 
+----+------------------------+ 
3 rows in Set (0.00 sec) 

After a duplicate record is deleted, you can delete the index if necessary:

 
Mysql> ALTER TABLE demo DROP index Ukey; 
Query OK, 3 rows affected (0.37 sec) 
Records:3 duplicates:0 warnings:0 

If you have permission to create a table, you can use the following method:

Create a new table, and then insert the data that is not duplicated in the original table into the new table:

 
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 show 

tables; 
+----------------+ 
| Tables_in_test | 
+----------------+ 
| demo | 
| demo_new | 
+----------------+ 
2 rows in Set (0.00 sec) 

mysql> SELECT * to demo order by ID; 
+----+------------------------+ 
| id | site | 
+----+------------------------+ 
| 1 | http://www.CodeBit.cn | 
| 2 | http://YITU.org | 
| 3 | http://www.ShuoWen.org | 
| 4 | http://www.CodeBit.cn | 
| 5 | http://www.ShuoWen.org | 
+----+------------------------+ 
5 rows in Set (0.00 sec) 

mysql> select * from Demo_new; 
+----+------------------------+ 
| id | site | 
+----+------------------------+ 
| 1 | http://www.CodeBit.cn | 
| 2 | http://YITU.org | 
| 3 | http://www.ShuoWen.org | 
+----+------------------------+ 
3 rows in Set (0.00 sec) 

Then, back up the original table and rename the new table to the current table:

 
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 * to demo order by ID; 
+----+------------------------+ 
| id | site | 
+----+------------------------+ 
| 1 | http://www.CodeBit.cn | 
| 2 | http://YITU.org | 
| 3 | http://www.ShuoWen.org | 
+----+------------------------+ 
3 rows in Set (0.00 sec) 

Note: Tables created in this manner will lose the index information of the original table!

 
Mysql> desc Demo; 
+-------+------------------+------+-----+---------+-------+ 
| Field | Type | Null | Key | Default | Extra | 
+-------+------------------+------+-----+---------+-------+ 
| id | int (one) unsigned | NO | | 0 | | | 
site | varchar (100) | NO | | | | 
+-------+------------------+------+-----+---------+-------+ 
2 rows in Set (0.00 sec) 

If you want to keep the same information as the original table, you can use the show create table demo; To view the creation statement of the original table, and then create a new table using the creation statement of the original table, and then use the INSERT ... SELECT statement to insert the data, and then rename the table.

Of course, if you want to avoid duplicate records, the best way is not to insert duplicate data, you can refer to another article in this site: MySQL inserts when the record does not exist