How to delete duplicate database records in mysql

This article uses an example to illustrate how to delete duplicate database records in mysql. If you need it, refer to the example.

Demo data

Table Structure:

 

The Code is as follows:

Copy code

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

 

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:

 

The Code is as follows:	Copy code
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.bKjia. c0m | | 2 | | 3 | http://www.zhutiai.com | + ---- + ------------------------ + 3 rows in set (0.00 sec)

To delete a new record, use the following statement:

 

The Code is as follows:	Copy code
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 | | 4 | http://www.bKjia. c0m | | 5 | http://www.zhutiai.com | + ---- + ------------------------ +

3 rows in set (0.00 sec)
You can use the following statement to first confirm the records that will be deleted:

 

The Code is as follows:	Copy code
Mysql> SELECT .* -> 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)

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

Create a unique key index on the table:

 

The Code is as follows:	Copy code
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)

After a duplicate record is deleted, you can delete the index as needed:

The Code is as follows:	Copy code
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:

 

The Code is as follows:

Copy code

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)

Back up the original table and rename the new table to the current table:

 

The Code is as follows:

Copy code

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)

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

 

 

The Code is as follows:	Copy code
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.