Removing duplicate rows in MySQL

February 17, 2011 MySQL

Getting rid of duplicate table rows in mysql seems to be a very often issue. And there are few well-known solutions like e.g. creating temporary table and copying there only unique ones with group by and then drop the old and rename the newly one. But this solution is bad because we lose auto_increment counters and we might not have all grant privileges to do that. Another one is creating unique index on the field(s) that are considered to be the “key”. But it’s not always possible, assume you have a column varchar(1024) and it is in utf-8 so it’s more than 1024 bytes – the maximum allowed key length in mysql.

I found a nice article https://www.xaprb.com/blog/2006/10/11/how-to-delete-duplicate-rows-with-sql/ and the first option worked for me. The idea is that you select unique records in inner query (with gouping) , then join it on the same table again but with condition minimum PK of inner != PK of outer one, that gives us the list of redundant rows which we just delete.

In my case I have title varchar(1024) column that is considered to be unique key. So the query would look like:


Leave a Reply

Your email address will not be published