Delete all duplicate rows in MySQL

In this post, I demonstrate for removing all rows except for one in MySQL.

Before a few days ago, I was working in one of our production report and found that there are a lot of duplicate records.
This table has more than 2,00,00,000 records and I had to remove duplicate base upon two varchar column and require to store one record for each group.

I had  prepared a demo with a different solution because I had to also take care about performance.
That Solution was,

  • Using Self – Join to check every next row with current row and create a delete flag for duplicate rows.
  • Swapping of the table, copy only unique records in the temp table and restore back in the original table after deleting of duplicate records.

But this didn’t work for me because this solution requires huge amount of DML operation which will degrade performance.

After some research, I found that MySQL has one feature called ALTER IGNORE.
Using ALTER IGNORE you can apply unique key on those columns which are required to check duplicate and remove. Once you execute this DDL command, then it creates unique key constraint and removes all duplicate records from database.

This is really a very good option in MySQL and also work for me.
Let me demonstrate this,

First, create demo table and data for varchar datatype:

SET old ALTER table behavior:


Find result:

MySQL Alter Ignore Statement

As you can see in above result, all duplicate records has been removed. I have explained this example, using VARCHAR data type.

If your column has a BLOB data type, then you cannot directly insert any unique key constraint on it.
If you have BLOB data type columns like TEXT so temporary you have to create one MD5 column and use this MD5 column in ALTER IGNORE.

Below is a demonstration for deleting BLOB duplicate records.

First, create demo table and data for TEXT datatype:

Create MD5 column for EmpID(Integer) + EmpName(Text):
Now check the result:
Remove MD5 column:
Anvesh Patel

Leave a Reply

2 Comments on "Delete all duplicate rows in MySQL"

Notify of
Sort by:   newest | oldest | most voted

Hi Anvesh,

Please explain a bit more about below queries.

what does this query :
SET SESSION old_alter_table=1;

TABLE Employee.tbl_DuplicateEmployee_VarcharDatatype
ADD UNIQUE INDEX idx_tbl_DuplicateEmployee_VarcharDatatype