Delete duplicate records in MySQL

In our work, we often have the need to delete some duplicate data. Duplicate data may be caused by the program bug

Okay, let's see how to delete these duplicates.

The idea of deleting duplicate data is like this. Check to see if the result grouped according to this duplicate field is greater than 1.

If there is a situation greater than 1, it means that there is duplicate data!

1) create a database

Create database ceshi

2) create a table

CREATE TABLE T1 (id int (10) unsigned NOT NULL AUTO_INCREMENT, name varchar (20) not null, createdAt timestamp NOT NULL DEFAULT current_timestamp (), updatedAt timestamp NOT NULL DEFAULT current_timestamp () ON UPDATE current_timestamp (), PRIMARY KEY (id)) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4

3) insert data

Insert into T1 values (null,'',now (), now ()), (null,'',now (), now ()), (null,'xixi',now (), now ()), (null,'heihei',now (), now ()), (null,'hoho',now (), now ()), (null,'hoho',now (), now ()), (null,'sasa',now (), now ()), (null,'',now (), now ()) (null,'sasa',now (), now ()), (null,'xixi',now (), now ())

4) View data

Select * from T1

5) grouping according to the minimum id and the repeating field name, assuming that our requirement is to finally retain the data of the minimum id and delete the other duplicates

Select min (id), name from T1 group by name having count (name) > 1 order by id

6) if you associate it with an alias, the data is duplicated and the last one to be deleted

Select a.* from T1 a, (select *, min (id) from T1 group by name having count (name) > 1) as b where a.name=b.name and a.id > b.id

7) remember to back up the data first

Create table bak_t1_20190621 like t1

Insert into bak_t1_20190621 select * from T1

8) Delete data

Delete a from T1 a, (select *, min (id) from T1 group by name having count (name) > 1) as b where a.name=b.name and a.id > b.id

9) finally check that there is no duplicate data.