'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.', 'howto' => [ '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' => '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.', 'faq' => [ [ 'q' => 'What causes duplicate rows in MySQL?', 'a' => 'Duplicates are usually caused by missing unique constraints, repeated imports, or application-level validation issues.' ], [ 'q' => 'Is GROUP BY the best way to find duplicates?', 'a' => 'Yes. GROUP BY combined with HAVING COUNT(*) > 1 is the most reliable method.' ], [ 'q' => 'Can I check duplicates across multiple columns?', 'a' => 'Yes. You can include multiple columns in the GROUP BY clause.' ], [ 'q' => 'Should I delete duplicates immediately?', 'a' => 'No. Always review the data first before performing deletion queries.' ] ] ]; include_once $_SERVER['DOCUMENT_ROOT'] . '/inc/guides/related-guides-helper.php'; include_once $_SERVER['DOCUMENT_ROOT'] . '/inc/guides/related-tools-helper.php'; $relatedGuides = getAutoRelatedGuides($guideSlug, $guideCategory, 5); $relatedTools = getRelatedToolsForGuide($guideSlug, 5); include_once $_SERVER['DOCUMENT_ROOT'] . "/inc/header.html"; ?>

Guides

Step-by-step practical fixes for everyday technical problems.

Database Guides

How to Find Duplicate Values in MySQL

Identify duplicate rows safely using SQL before performing cleanup or deletion.

Before you start

Always confirm which column should be unique.

Never delete duplicates without reviewing them first.

Take a backup if the data is critical.

Find duplicates by single column

SQL

SELECT column_name, COUNT(*) as cnt
FROM table_name
GROUP BY column_name
HAVING COUNT(*) > 1;

What this does

Groups rows by the target column and returns only values that appear more than once.

Find duplicates by multiple columns

SQL

SELECT col1, col2, COUNT(*) as cnt
FROM table_name
GROUP BY col1, col2
HAVING COUNT(*) > 1;

Use case

Useful when duplicates are defined by a combination of values, such as email + date or user_id + product_id.

View full duplicate rows

SQL

SELECT *
FROM table_name
WHERE column_name IN (
	SELECT column_name
	FROM table_name
	GROUP BY column_name
	HAVING COUNT(*) > 1
);

Important

This lets you inspect the actual duplicate rows before taking any action.

Common mistakes

Wrong column selection

Choosing a column that is not meant to be unique leads to misleading results.

Ignoring NULL values

NULL values may behave differently and should be checked separately if needed.

Skipping validation

Always review duplicates before deletion or updates.