How to Find Duplicate Values in MySQL
Identify duplicate rows safely using SQL before performing cleanup or deletion.
Before you start
Find duplicates by single column
SELECT column_name, COUNT(*) as cnt
FROM table_name
GROUP BY column_name
HAVING COUNT(*) > 1;Groups rows by the target column and returns only values that appear more than once.
Find duplicates by multiple columns
SELECT col1, col2, COUNT(*) as cnt
FROM table_name
GROUP BY col1, col2
HAVING COUNT(*) > 1;Useful when duplicates are defined by a combination of values, such as email + date or user_id + product_id.
View full duplicate rows
SELECT *
FROM table_name
WHERE column_name IN (
SELECT column_name
FROM table_name
GROUP BY column_name
HAVING COUNT(*) > 1
);This lets you inspect the actual duplicate rows before taking any action.
Common mistakes
Choosing a column that is not meant to be unique leads to misleading results.
NULL values may behave differently and should be checked separately if needed.
Always review duplicates before deletion or updates.
About this guide
This guide shows how to find duplicate values in MySQL using real-world SQL queries. It focuses on identifying duplicated rows based on one or more columns before performing cleanup or data correction.
How to follow this guide
- Identify which column or combination should be unique.
- Use GROUP BY with HAVING to detect duplicates.
- Review duplicate counts before taking action.
- Optionally expand query to view full duplicate rows.
- Use results as a safe base before deleting or updating data.
Why use this method?
Duplicate data is a common issue caused by missing constraints, import errors, or application bugs. Finding duplicates accurately is the first and most important step before fixing or removing them.
Frequently Asked Questions
What causes duplicate rows in MySQL?
Duplicates are usually caused by missing unique constraints, repeated imports, or application-level validation issues.
Is GROUP BY the best way to find duplicates?
Yes. GROUP BY combined with HAVING COUNT(*) > 1 is the most reliable method.
Can I check duplicates across multiple columns?
Yes. You can include multiple columns in the GROUP BY clause.
Should I delete duplicates immediately?
No. Always review the data first before performing deletion queries.