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

2020-05-12 06:18:37
OfStack

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 have permission to create a table or create an index, you can do the following:

If you want to delete an older duplicate record, 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 a newer duplicate record, 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 confirm duplicate records to be deleted with the following statement:
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 do the following:

Create a 1-key index on the table:

class="brush: sql">
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 the duplicate record has been deleted, the index can be dropped if necessary:

class="brush: sql">
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 do the following:

Create a new table, and then insert non-repeating data from the original table into the new table:

class="brush: sql">
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)

Then backup the original table and rename the new table to the current table:

class="brush: sql">
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: a table created this way will lose the index information of the original table!

class="brush: sql">
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)

You can use show create table demo if you want to keep track of information 1 in the original table. To view the creation statement of the original table, then create a new table using the creation statement of the original table, then use insert... The select statement inserts the data and then renames 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 on this site: MySQL when the record does not exist to insert