How to find duplicate rows in MySQL database table
I’ve been asked the question “How can I return duplicate rows only from a MySQL db table” many times, so I’ve decided to post a writeup here in a short article. After all, it seems such a common requirement. Any DBA undoubtedly has come across the need to find duplicates in a table.
If you’re really concerned about duplicate rows, or even more profound, if you’re code logic depends on unique entries in a mysql table, you really should implement a unique index on the field(s) in question.
The query to find duplicate rows in mysql it is not something intuitive or readily documented (at least it seems), but the solution is short and very simple.
Find all unique rows in mysql
SELECT DISTINCT column1 FROM table1
Find duplicate rows in mysql
SELECT DISTINCT column1 FROM table1 GROUP BY column1 HAVING COUNT(column1) > 1
And by increasing the having count, you can retrieve records with multiple occurrences.
Placing this query into a delete or update script as a sub query, you can easily delete duplicate records or update duplicate ones by setting a unique flag.
If you know of another solution to find duplicate rows in a MySQL database, drop us a comment.