MySQL 데이터베이스에서 중복 데이터를 삭제하는 방법

소개

데이터베이스 작업 시, 특히 대규모 데이터세트가 지속적으로 수정되거나 추가되는 환경에서는 중복된 데이터가 발생하는 경우가 많습니다. 이는 쿼리 성능의 비효율성, 데이터 불일치 및 부정확한 보고서로 이어질 수 있습니다. 다행스럽게도 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

여기서 id = 1, id = 3, id = 5인 행이 중복됩니다. 우리의 목표는 사본 하나만 보관하면서 해당 항목을 제거하는 것입니다.

중복 제거를 위한 단계별 가이드

1. 중복 기록 식별

첫 번째 단계는 어떤 기록이 중복되었는지 식별하는 것입니다. 이렇게 하려면 고유해야 하는 열을 기준으로 레코드를 그룹화해야 합니다. 이 경우에는 first_name, last_name, email의 조합이 고유해야 한다고 가정합니다.

다음 쿼리를 사용하여 중복 항목을 찾을 수 있습니다.

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

이 쿼리는 first_name, last_name 및 email 열을 기준으로 레코드를 그룹화하고 두 번 이상 발생(예: 중복)된 그룹만 표시합니다.

2. 유지하거나 삭제할 중복 행 선택

중복 항목을 식별한 후 이를 제거하는 방법이 필요합니다. 일반적인 접근 방식은 가장 작거나 가장 큰 ID로 레코드를 유지하고 나머지는 삭제하는 것입니다. 셀프 조인을 사용하여 각 중복 레코드를 보관하려는 레코드와 일치시킬 수 있습니다.

예:

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;

이 쿼리는 다음과 같이 작동합니다.

• 이름, 성, 이메일이 동일한 직원 테이블에 대해 셀프 조인을 수행합니다.
• 가장 작은 ID의 기록을 유지하면서 더 높은 ID(나중에 삽입됨)를 가진 행만 삭제되도록 합니다.

3. 더 복잡한 시나리오에는 임시 테이블 사용

어떤 상황에서는 가장 최근의 가입 날짜나 최고 연봉의 기록을 유지하는 등 어떤 중복 항목을 유지할지 결정하는 데 더 복잡한 조건이 있을 수 있습니다.

이러한 경우 임시 테이블을 사용하여 유지하려는 행을 저장한 다음 나머지는 모두 삭제할 수 있습니다.

예:

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);

이 쿼리의 기능은 다음과 같습니다.

1. first_name, last_name 및 email(이 경우 가장 작은 ID를 가진 레코드)의 각 조합에 대해 하나의 고유 레코드만 포함하는 임시 테이블 temp_employees를 생성합니다.
2. temp_employees 테이블에 없는 원래 직원 테이블의 모든 레코드를 삭제합니다.

이 방법은 단순히 ID를 사용하는 것보다 어떤 복제본을 보관할지 결정하는 기준이 더 복잡할 때 유용합니다.

4. 향후 중복을 방지하기 위해 고유한 제약 조건 추가

중복 항목을 정리한 후에는 중복이 발생하지 않도록 하는 것이 좋습니다. 관련 열에 고유 제약조건을 추가하면 이를 달성할 수 있습니다.

예를 들어, 이름, 성, 이메일이 동일한 향후 행을 방지하려면 다음을 수행하세요.

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

이렇게 하면 중복 레코드를 삽입하려고 시도할 경우 데이터베이스에서 오류가 발생하여 데이터 무결성이 유지됩니다.

성능 고려 사항

대규모 데이터 세트로 작업할 때 중복 삭제는 속도가 느리고 리소스 집약적일 수 있습니다. 성능 최적화를 위한 몇 가지 팁은 다음과 같습니다.

• 색인 생성: 필터링과 관련된 열(예: 이름, 성, 이메일)의 색인이 생성되었는지 확인하세요. 이렇게 하면 프로세스 속도가 크게 빨라질 수 있습니다.
• 일괄 삭제: 많은 수의 행을 삭제하는 경우 긴 잠금을 방지하고 서버의 부하를 줄이기 위해 일괄 삭제를 고려하세요.

일괄 삭제 예시:

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;

모든 중복 항목이 제거될 때까지 이 쿼리를 여러 번 실행할 수 있습니다.

결론

MySQL 데이터베이스에서 중복 레코드를 처리하는 것은 일반적인 작업이며, 잘못 처리할 경우 데이터 손실이나 일관되지 않은 결과가 발생할 수 있습니다. 이 블로그에 설명된 단계를 사용하면 데이터 무결성을 유지하면서 중복 항목을 확실하고 효율적으로 제거할 수 있습니다. 또한 고유한 제약 조건을 추가하면 향후 중복을 방지하여 깔끔하고 안정적인 데이터 세트를 유지할 수 있습니다.

위 내용은 MySQL 데이터베이스에서 중복 데이터를 삭제하는 방법의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!