MySQL - Delete Duplicate Records

안녕하세요, 미래의 데이터베이스 마법사 여러분! 오늘 우리는 MySQL의 세계로 흥미로운 여정을 떠납니다. 특히 귀찮은 중복 레코드를 어떻게 지우는지에 대해 집중적으로 배울 것입니다. 여러분의 친절한 이웃 컴퓨터 교사로서, 저는 이 과정을 단계별로 안내해 드리겠습니다. 이해가 될 때까지 함께 고민해 나가요. 그럼 가상의帚를 들고, 데이터베이스를 청소해봅시다!

The MySQL Delete Duplicate Records

중복 레코드를 지우는细部에 들어가기 전에, 이 작업이 왜 중요한지 잠시 이해해 보겠습니다. 상상해 보세요. 도서관 데이터베이스를 관리하고 있을 때, 어떻게 하면 동일한 책의 여러 항목이 생기게 되겠습니까? 이는 공간 낭비뿐만 아니라 혼란과 오류를 초래할 수 있습니다. 여기서 우리의 중복 레코드 지우기 연산이 유용하게 쓰입니다!

What are Duplicate Records?

중복 레코드는 데이터베이스 테이블에 있는 항목으로, 한 개 이상의 열에서 동일한 값을 가집니다. 우리의 도서관 예시에서, 이는 동일한 ISBN 번호, 저자, 그리고 제목을 가진 책일 수 있습니다.

Find Duplicate Values

중복 레코드를 지우기 전에 우선 찾아야 합니다. 이는 "차이를 찾아내는" 게임의 역입니다! 중복을 식별하는 몇 가지 방법을 보겠습니다.

Using GROUP BY and HAVING Clauses

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

이 쿼리는 지정된 열에 따라 레코드를 그룹화하고 각 값이 몇 번 나타나는지 계산합니다. HAVING 절은 count가 1보다 큰 그룹을 필터링하여 중복 값을 보여줍니다.

예를 들어, 우리 도서관에서 중복 책을 찾는다면:

SELECT title, author, COUNT(*) as count
FROM books
GROUP BY title, author
HAVING count > 1;

이렇게 하면 데이터베이스에 두 번 이상 나타나는 모든 책 제목과 저자를 보여줍니다.

Using Self JOIN

다른 방법으로 중복을 찾는 것은 자체 조인을 사용하는 것입니다:

SELECT t1.*
FROM table_name t1
JOIN table_name t2
WHERE t1.id < t2.id
AND t1.column_name = t2.column_name;

이 쿼리는 테이블을 자신과 조인하여 각 레코드를 모든 다른 레코드와 비교합니다. 가장 높은 ID를 가진 레코드를 제외한 모든 중복 레코드를 반환합니다.

Delete Duplicate Records

이제 우리는 중복을 찾았으니, 이들을 안녕히 보내는 시간입니다. 이 작업을 수행하는 여러 가지 방법이 있으며, 각각의 장단점이 있습니다. 이제 그것을 탐구해 보겠습니다!

Using DELETE with Subquery

DELETE t1 FROM table_name t1
INNER JOIN table_name t2
WHERE t1.id < t2.id
AND t1.column_name = t2.column_name;

이 쿼리는 가장 높은 ID를 가진 레코드를 제외한 모든 중복 레코드를 지웁니다. 마치 음악에 맞춰 앉는 놀이에서 마지막에 남은 레코드가 남는 것과 같습니다!

Using CREATE TABLE and INSERT

다른 접근 방식은 새로운 테이블을 만들어 고유한 레코드를 삽입한 후 원래 테이블을 교체하는 것입니다:

CREATE TABLE temp_table AS
SELECT DISTINCT * FROM original_table;

DROP TABLE original_table;

ALTER TABLE temp_table RENAME TO original_table;

이 방법은 마치 좋아하는 플레이리스트를 새로 만들어서 각 곡의 하나의 버전만 유지하는 것과 같습니다.

Using ROW_NUMBER()

보다 고급 사용자를 위해서는 ROW_NUMBER() 함수를 사용할 수 있습니다:

DELETE FROM table_name
WHERE id NOT IN (
SELECT id
FROM (
SELECT id,
ROW_NUMBER() OVER (
PARTITION BY column_name
ORDER BY id
) AS row_num
FROM table_name
) t
WHERE t.row_num = 1
);

이 함수는 동일한 값 그룹 내의 각 레코드에 행 번호를 할당하고, 각 그룹의 첫 번째 행을 제외한 모든 행을 지웁니다.

Delete Duplicate Records Using Client Program

occasionally, it's easier to handle duplicate deletion outside of MySQL. Here's a simple Python script that can help:

import mysql.connector

def delete_duplicates(connection, table_name, column_name):
cursor = connection.cursor()

# Find and delete duplicates
query = f"""
DELETE t1 FROM {table_name} t1
INNER JOIN {table_name} t2
WHERE t1.id < t2.id
AND t1.{column_name} = t2.{column_name}
"""

cursor.execute(query)
connection.commit()

print(f"Deleted {cursor.rowcount} duplicate records.")

# Usage example
connection = mysql.connector.connect(
host="localhost",
user="yourusername",
password="yourpassword",
database="yourdatabase"
)

delete_duplicates(connection, "books", "isbn")

connection.close()

이 스크립트는 MySQL 데이터베이스에 연결하고, 지우기 쿼리를 실행하고, 몇 개의 중복이 제거되었는지 보고합니다. 마치 데이터베이스를 청소하는 개인 비서가 있는 것과 같습니다!

Conclusion

축하합니다! 이제 MySQL에서 중복 레코드를 찾고 지우는 여러 가지 방법을 배웠습니다. 깨끗하고 중복이 없는 데이터베이스를 유지하는 것은 데이터의完璧성과 효율적인 운영에 매우 중요합니다.

여기서 다루었던 방법의 요약입니다:

Method	Pros	Cons
GROUP BY and HAVING	간단히 이해할 수 있음	중복을 찾는 데만 사용
Self JOIN	유연하고 여러 열을 비교할 수 있음	큰 테이블에서 느림
DELETE with Subquery	작은 테이블에서 효율적	매우 큰 테이블에서 느림
CREATE TABLE and INSERT	원본 데이터를 보존	일시적으로 추가 저장 공간 필요
ROW_NUMBER()	매우 유연하고 강력	문법이 더 복잡
Client Program	커스터마이즈된 논리 포함 가능	추가 프로그래밍 필요

적절한 방법을 선택하십시오. 데이터베이스 크기와 특정 요구에 맞는 방법을 선택하세요. 그리고 중복 지우기 작업을 수행하기 전에 항상 데이터를 백업하십시오. 즐겁게 중복 제거하세요!

Credits: Image by storyset