How to find duplicate rows in MySQL database table

Written by Peter Gilg on April 22, 2010 - like this:

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.

Listed in MySQL, Web Development

Tags: duplicate rows, MySQL, query