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:

Please share your ideas and opinions about this topic with me, your contribution will add true value to this topic.
If anyone has doubts on this topic then please do let me know by leaving comments or send me an email.

If you like this post, then please share it with others.
Please follow, I will share my experience towards the success of Database Research and Development Activity.

I put up a post every day, please keep reading and learning.
Discover Yourself, Happy Blogging !
Anvesh M. Patel.

More from

Leave a Reply

Be the First to Comment!

Notify of