Delete duplicate records of a MySQL table

Sometimes there are some wrong databases, and some duplicate records are generated in the table. It is troublesome to delete Redundant records. Today, we can see a clever solution, taking advantage of the MySQL extension feature, we simply completed this task.

 

Why make this such a challenge?

Assuming your example:
Create Table bad_table2 (
Id int not null unique auto_increment,
Name varchar (20) not null
);

Insert into bad_table2 (ID, name) Values
(1, 'things fall apart '),
(2, 'things fall apart '),
(3, 'the famished road '),
(4, 'things fall apart '),
(5, 'the famished road '),
(6, 'Thirteen centers '),
(7, 'Thirteen centers ');

I can remove duplicates easily with the following line:
Alter ignore table bad_table2 add unique index 'unique _ Index' (name );

And then remove the added index.
Alter table bad_table2 drop index 'unique _ Index ';

Viola, dups gone.

 

There are two key points here: the unique index and the ignore keyword. MySQL interprets ignore as follows:

 

ignore is an extension of MySQL compared with standard SQL. If there are duplicate keywords in the new table, or a warning is reported after strict mode is started, ignore is used to control the run of alter
table. If no ignore is specified, the copy operation is abandoned when a duplicate keyword error occurs, and the previous step is returned. If ignore is specified, for rows with duplicate keywords, only the first row is used, other conflicting rows are deleted . In addition, correct the error value so that it is as close as possible to the correct value.