Summary of methods for deleting duplicate records in MySQL Databases [recommended]

Table Structure:
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)

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)

If you do not have the permission to create a table or create an index, you can use the following method:

If you want to delete older duplicate records, you can use the following statement:
Mysql> delete from
-> 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)

To delete a new duplicate record, use the following statement:
Mysql> delete from
-> 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 first confirm the records that will be deleted:
Mysql> SELECT .*
-> 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 the permission to create an index, you can use the following method:

Create a unique key index on the 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 as needed:

 mysql> alter table demo drop index ukey; Query OK, 3 rows affected (0.37 sec) Records: 3 Duplicates: 0 Warnings: 0 

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

Create a new table and insert the data 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 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.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 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) 

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 * 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) 

Note: using this method to create a table will lose the index information of the original table!

 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) 

To maintain consistency with the original table information, you can use show create table demo; to view the statement for creating the original table, use the statement for creating the original table, and then use insert... Insert data using the select statement and rename the table.

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