How to Delete Duplicate Data from a MySQL Database

소개

데이터베이스 작업 시, 특히 대규모 데이터세트가 지속적으로 수정되거나 추가되는 환경에서는 중복된 데이터가 발생하는 경우가 많습니다. 이는 쿼리 성능의 비효율성, 데이터 불일치 및 부정확한 보고서로 이어질 수 있습니다. 다행스럽게도 MySQL은 이러한 중복 항목을 식별하고 제거할 수 있는 강력한 도구를 제공합니다.

이 블로그에서는 MySQL 데이터베이스에서 중복 레코드를 감지하고 중복 레코드를 제거하는 효율적이고 포괄적인 접근 방식을 안내해 드리겠습니다. 여기에 나와 있는 방법은 대부분의 관계형 데이터베이스 시스템에 적용 가능하지만, 이 튜토리얼에서는 MySQL에 중점을 둘 것입니다.

중복을 구성하는 것은 무엇입니까?

코드를 살펴보기 전에 중복의 조건을 정의하는 것이 중요합니다. 많은 경우 중복은 모든 열의 값이 동일한 단순한 레코드가 아닙니다. 종종 중복 항목은 키 열의 하위 집합에 동일한 값을 가질 수 있습니다. 예를 들어 users 테이블에서 두 레코드의 이메일은 동일하지만 사용자 이름 또는 signup_date와 같은 다른 필드가 다를 수 있습니다.

단순화를 위해 이 튜토리얼에서는 중복 항목이 모든 열(또는 열의 하위 집합)이 일치하는 행이라고 가정합니다.

다음 직원 테이블 예를 살펴보세요.

id	first_name	last_name	email	salary
1	John	Doe	john@example.com	60000
2	Jane	Smith	jane@example.com	65000
3	John	Doe	john@example.com	60000
4	Alex	Johnson	alex@example.com	72000
5	John	Doe	john@example.com	60000

Here, the rows with id = 1, id = 3, and id = 5 are duplicates. Our goal is to remove them while keeping only one copy.

Step-by-Step Guide to Remove Duplicates

1. Identify Duplicate Records

The first step is to identify which records are duplicated. To do this, we need to group the records by the columns that should be unique. In this case, let's assume the combination of first_name, last_name, and email should be unique.

You can use the following query to find duplicates:

SELECT first_name, last_name, email, COUNT(*)
FROM employees
GROUP BY first_name, last_name, email
HAVING COUNT(*) > 1;

This query groups the records based on the columns first_name, last_name, and email, and shows only the groups with more than one occurrence, i.e., duplicates.

2. Select the Duplicate Rows to Keep or Delete

After identifying duplicates, we need a method to remove them. A common approach is to keep the record with the smallest or largest id and delete the others. We can do this using a self-join to match each duplicate record with the one we want to keep.

Example:

DELETE e1
FROM employees e1
JOIN employees e2
ON e1.first_name = e2.first_name
AND e1.last_name = e2.last_name
AND e1.email = e2.email
AND e1.id > e2.id;

This query works as follows:

• It performs a self-join on the employees table where the records have the same first_name, last_name, and email.
• It ensures that only rows with a higher id (meaning they were inserted later) will be deleted, while keeping the record with the smallest id.

3. Use Temporary Table for More Complex Scenarios

In some situations, you may have more complex conditions to determine which duplicate to keep, such as retaining the record with the latest signup_date or the highest salary.

In such cases, you can use a temporary table to store the rows you want to keep and then delete everything else.

Example:

CREATE TEMPORARY TABLE temp_employees AS
SELECT * FROM employees e1
WHERE e1.id IN (
    SELECT MIN(id)
    FROM employees
    GROUP BY first_name, last_name, email
);

DELETE FROM employees
WHERE id NOT IN (SELECT id FROM temp_employees);

Here’s what this query does:

1. Creates a temporary table temp_employees containing only one unique record for each combination of first_name, last_name, and email (in this case, the record with the smallest id).
2. Deletes all records from the original employees table that are not present in the temp_employees table.

This method is useful when the criteria for deciding which duplicate to keep is more sophisticated than simply using the id.

4. Add Unique Constraints to Prevent Future Duplicates

Once you've cleaned up the duplicates, it’s a good idea to prevent them from reoccurring. You can achieve this by adding a unique constraint to the relevant columns.

For example, to prevent any future rows with the same first_name, last_name, and email:

ALTER TABLE employees
ADD CONSTRAINT unique_employee
UNIQUE (first_name, last_name, email);

This ensures that the database will throw an error if an attempt is made to insert a duplicate record, thus maintaining data integrity.

Performance Considerations

When working with large datasets, deleting duplicates can be slow and resource-intensive. Here are some tips to optimize performance:

• Indexing: Make sure that the columns involved in filtering (like first_name, last_name, email) are indexed. This can significantly speed up the process.
• Batch Deletion: If you are deleting a large number of rows, consider doing it in batches to avoid long locks and reduce the load on the server.

Example of batch deletion:

DELETE e1
FROM employees e1
JOIN employees e2
ON e1.first_name = e2.first_name
AND e1.last_name = e2.last_name
AND e1.email = e2.email
AND e1.id > e2.id
LIMIT 1000;

You can run this query multiple times until all duplicates are removed.

Conclusion

Dealing with duplicate records in a MySQL database is a common task that, if handled incorrectly, can lead to data loss or inconsistent results. Using the steps outlined in this blog, you can confidently and efficiently remove duplicates while preserving the integrity of your data. Moreover, by adding unique constraints, you can ensure that future duplicates are prevented, helping maintain clean, reliable datasets.

The above is the detailed content of How to Delete Duplicate Data from a MySQL Database. For more information, please follow other related articles on the PHP Chinese website!