MySQL - Find Duplicate Records

MySQL - How to Find and Manage Duplicate Records

Duplicate records in a database can lead to data integrity issues, incorrect analytics, and performance degradation. Identifying and managing these duplicates is a critical database maintenance task. This tutorial explores modern and effective methods for finding and handling duplicate data in MySQL.

While the best strategy is to prevent duplicates from being created using constraints like PRIMARY KEY and UNIQUE, they can still appear due to application bugs, data imports, or legacy system migrations. We’ll cover two primary techniques for detection: the traditional GROUP BY with HAVING clause and the more powerful window function ROW_NUMBER().

Scenario Setup

Let’s work with a products table. Notice that some products have the same name and category, which we’ll consider as duplicates for our examples.

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    category VARCHAR(50) NOT NULL,
    price DECIMAL(10, 2),
    stock_quantity INT
);

INSERT INTO products (product_name, category, price, stock_quantity) VALUES
('Laptop', 'Electronics', 1200.00, 50),
('Keyboard', 'Electronics', 75.00, 200),
('Laptop', 'Electronics', 1250.00, 35), -- Duplicate name and category
('Mouse', 'Electronics', 25.00, 500),
('T-Shirt', 'Apparel', 20.00, 300),
('T-Shirt', 'Apparel', 22.50, 250),   -- Duplicate name and category
('Jeans', 'Apparel', 60.00, 150);

Method 1: Using GROUP BY and HAVING

This is the classic approach to find which values are duplicated. It works by grouping rows based on the columns you suspect have duplicates and then using the HAVING clause to filter for groups with a count greater than one.

Concept

• The GROUP BY clause aggregates rows that have the same values in specified columns into summary rows.
• The COUNT() aggregate function counts the number of rows in each group.
• The HAVING clause filters these groups, keeping only those where the count is greater than 1.

Example: Find Duplicates by product_name and category

This query identifies which combinations of product_name and category appear more than once and shows their count.

SELECT
    product_name,
    category,
    COUNT(*) AS occurrences
FROM
    products
GROUP BY
    product_name, category
HAVING
    COUNT(*) > 1;

Output

product_name	category	occurrences
Laptop	Electronics	2
T-Shirt	Apparel	2

Limitation: This method is great for identifying which values are duplicated, but it doesn’t easily return the full details (like id, price, etc.) of all the individual duplicate rows.

Method 2: Using Window Functions (ROW_NUMBER)

Available since MySQL 8.0, window functions provide a more powerful and flexible way to handle duplicates. The ROW_NUMBER() function can assign a unique rank to each row within a partition (a group of rows), allowing you to easily identify and select the duplicates.

Concept

• The PARTITION BY clause divides the rows into partitions (groups) based on specified columns (e.g., product_name, category). This is similar to GROUP BY but doesn’t collapse the rows.
• The ROW_NUMBER() function assigns a sequential integer to each row within its partition.
• Any row assigned a ROW_NUMBER() greater than 1 is a duplicate within its partition.

Example: Find and List All Duplicate Rows

We use a Common Table Expression (CTE) to first number the rows. Then, we select from the CTE to find all rows where the number is greater than 1.

WITH NumberedProducts AS (
    SELECT
        *,
        ROW_NUMBER() OVER (PARTITION BY product_name, category ORDER BY id) as row_num
    FROM
        products
)
SELECT id, product_name, category, price, stock_quantity
FROM NumberedProducts
WHERE row_num > 1;

Output

This query returns the complete records for the duplicate entries, making it easy to inspect or delete them.

id	product_name	category	price	stock_quantity
3	Laptop	Electronics	1250.00	35
6	T-Shirt	Apparel	22.50	250

Practical Application: Deleting Duplicates

The ROW_NUMBER() method is especially useful for deleting duplicates while keeping one version of the record (usually the first one, based on the ORDER BY clause inside OVER).

-- Note: Always back up your data before running a DELETE statement.
-- You can test what will be deleted by running the SELECT part first.

DELETE p
FROM products AS p
INNER JOIN (
    SELECT
        id,
        ROW_NUMBER() OVER (PARTITION BY product_name, category ORDER BY id) as row_num
    FROM
        products
) AS numbered_products ON p.id = numbered_products.id
WHERE numbered_products.row_num > 1;

Best Practices & Performance

• Prevention is better than cure: Use UNIQUE constraints on columns that should not have duplicate values to prevent issues at the source.
• Indexing: Ensure that the columns used in GROUP BY or PARTITION BY clauses are indexed. This can dramatically improve the performance of duplicate-finding queries on large tables.
• Choose the Right Method: Use GROUP BY for quick summaries of duplicate values. Use ROW_NUMBER() when you need to inspect, update, or delete the full duplicate rows.

Finding Duplicates Using a Client Program

You can also execute these queries from your application code to find duplicates programmatically. Here are examples using modern best practices.

Example Code

Python
NodeJS
Java
PHP



import mysql.connector
from mysql.connector import errorcode

# Best practice: use a dictionary for connection arguments
config = {
    'user': 'your_user',
    'password': 'your_password',
    'host': '127.0.0.1',
    'database': 'your_database'
}

query = """
    SELECT
        product_name, category, COUNT(*) AS occurrences
    FROM products
    GROUP BY product_name, category
    HAVING COUNT(*) > 1
"""

try:
    # Use a 'with' statement for automatic resource management
    with mysql.connector.connect(**config) as connection:
        with connection.cursor(dictionary=True) as cursor:
            cursor.execute(query)
            duplicates = cursor.fetchall()

            if duplicates:
                print("Found duplicate records:")
                for row in duplicates:
                    print(f"- Product: {row['product_name']}, Category: {row['category']}, Occurrences: {row['occurrences']}")
            else:
                print("No duplicate records found.")

except mysql.connector.Error as err:
    if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
        print("Authentication error")
    elif err.errno == errorcode.ER_BAD_DB_ERROR:
        print("Database does not exist")
    else:
        print(err)



const mysql = require('mysql2/promise');

// Use a connection pool for better performance and resource management
const pool = mysql.createPool({
    host: '127.0.0.1',
    user: 'your_user',
    password: 'your_password',
    database: 'your_database',
    waitForConnections: true,
    connectionLimit: 10,
    queueLimit: 0
});

const findDuplicates = async () => {
    const query = `
        SELECT
            product_name, category, COUNT(*) AS occurrences
        FROM products
        GROUP BY product_name, category
        HAVING COUNT(*) > 1
    `;

    let connection;
    try {
        connection = await pool.getConnection();
        const [rows] = await connection.execute(query);

        if (rows.length > 0) {
            console.log('Found duplicate records:');
            rows.forEach(row => {
                console.log(`- Product: ${row.product_name}, Category: ${row.category}, Occurrences: ${row.occurrences}`);
            });
        } else {
            console.log('No duplicate records found.');
        }
    } catch (error) {
        console.error('An error occurred while finding duplicates:', error);
    } finally {
        if (connection) connection.release(); // Release the connection back to the pool
        await pool.end(); // Close the pool when done
    }
};

findDuplicates();



import java.sql.*;

public class FindDuplicates {
    // Use constants for connection details
    private static final String DB_URL = "jdbc:mysql://localhost:3306/your_database";
    private static final String USER = "your_user";
    private static final String PASS = "your_password";

    public static void main(String[] args) {
        String query = """
            SELECT
                product_name, category, COUNT(*) AS occurrences
            FROM products
            GROUP BY product_name, category
            HAVING COUNT(*) > 1
        """;

        // Use try-with-resources for automatic resource management
        try (Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(query)) {

            boolean found = false;
            while (rs.next()) {
                if (!found) {
                    System.out.println("Found duplicate records:");
                    found = true;
                }
                String productName = rs.getString("product_name");
                String category = rs.getString("category");
                int occurrences = rs.getInt("occurrences");
                System.out.printf("- Product: %s, Category: %s, Occurrences: %d%n", productName, category, occurrences);
            }

            if (!found) {
                System.out.println("No duplicate records found.");
            }

        } catch (SQLException e) {
            // Basic error handling
            e.printStackTrace();
        }
    }
}



<?php
// Database configuration
$host = '127.0.0.1';
$db   = 'your_database';
$user = 'your_user';
$pass = 'your_password';
$charset = 'utf8mb4';

// Set up DSN (Data Source Name)
$dsn = "mysql:host=$host;dbname=$db;charset=$charset";
$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
];

$query = "
    SELECT
        product_name, category, COUNT(*) AS occurrences
    FROM products
    GROUP BY product_name, category
    HAVING COUNT(*) > 1
";

try {
    // Use PDO for database connections
    $pdo = new PDO($dsn, $user, $pass, $options);

    $stmt = $pdo->query($query);
    $duplicates = $stmt->fetchAll();

    if ($duplicates) {
        echo "Found duplicate records:\n";
        foreach ($duplicates as $row) {
            echo "- Product: {$row['product_name']}, Category: {$row['category']}, Occurrences: {$row['occurrences']}\n";
        }
    } else {
        echo "No duplicate records found.\n";
    }

} catch (\PDOException $e) {
    // In a real application, you would log this error, not display it to the user.
    throw new \PDOException($e->getMessage(), (int)$e->getCode());
}

?>