How to delete duplicates on a MySQL table?

Removing duplicate entries from a MySQL table is a common database management task that can be accomplished through several methods. The following outlines an effective approach, detailing the steps and a specific example.

Step 1: Define the Criteria for Duplicates

First, you need to define what constitutes a duplicate. For example, if we have a table named employees, we can define duplicates based on the email field, as email addresses should be unique.

Step 2: Use a Temporary Table

A safe and common approach is to use a temporary table to handle duplicates. The method is as follows:

1. Select Unique Records into a Temporary Table: We can ensure only one record per group by selecting the minimum (or maximum) ID after grouping. This is achieved using GROUP BY and the MIN function.

   sql
   CREATE TABLE temp_employees AS
   SELECT MIN(id) AS id, email
   FROM employees
   GROUP BY email;

2. Delete All Records from the Original Table: After saving the unique records in the temporary table, we can safely delete all data from the original table.

   sql
   DELETE FROM employees;

3. Restore Data from the Temporary Table: Now, the temporary table contains records without duplicates, and we can insert these records back into the original table.

   sql
   INSERT INTO employees(id, email)
   SELECT id, email FROM temp_employees;

4. Drop the Temporary Table: Finally, after restoring the data, clean up the temporary table.

   sql
   DROP TABLE temp_employees;

Step 3: Prevent Future Duplicates

To prevent duplicates from occurring again in the future, consider adding a unique index on the field that requires uniqueness.

sql
ALTER TABLE employees ADD UNIQUE (email);

Example

Suppose we have an employees table with fields id and email. Some email values are duplicated. Following the above method, we first create a temporary table containing unique email values, then clear the original table, restore data from the temporary table, and finally add a unique index on the email field to prevent future duplicates.

This method has the advantage of being operationally safe, effectively preventing data loss during deletion, and solving the problem fundamentally by adding a unique index. The disadvantage is that it requires additional space to create the temporary table and may slightly affect performance when handling large datasets. However, this is typically a worthwhile compromise.