MySQL에서 동일한 데이터가 여러 번 저장된 경우, 대표 레코드 하나만 남기고 나머지 중복 데이터를 삭제해야 하는 상황이 자주 발생합니다. 이때 DELETE 명령문에 조건을 추가하면 간단하게 해결할 수 있습니다. 핵심은 자기 조인(Self Join)을 활용해 같은 값을 가진 레코드 중 ID가 더 큰(나중에 입력된) 행들을 찾아 삭제하는 것입니다.
1. 테이블 생성하기
먼저 예제로 사용할 테이블을 생성합니다.
mysql> create table DemoTable
(
StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY,
StudentName varchar(40)
);
Query OK, 0 rows affected (0.48 sec)StudentId는 자동 증가(AUTO_INCREMENT) 기본 키로 설정하여, 각 레코드를 고유하게 식별할 수 있도록 했습니다.
2. 샘플 데이터 삽입하기
INSERT 명령어를 사용해 의도적으로 중복된 이름('Carol')을 포함한 데이터를 입력합니다.
mysql> insert into DemoTable(StudentName) values('John');
Query OK, 1 row affected (0.16 sec)
mysql> insert into DemoTable(StudentName) values('Carol');
Query OK, 1 row affected (0.17 sec)
mysql> insert into DemoTable(StudentName) values('Sam');
Query OK, 1 row affected (0.28 sec)
mysql> insert into DemoTable(StudentName) values('Carol');
Query OK, 1 row affected (0.14 sec)
mysql> insert into DemoTable(StudentName) values('David');
Query OK, 1 row affected (0.19 sec)
mysql> insert into DemoTable(StudentName) values('Carol');
Query OK, 1 row affected (0.26 sec)3. 현재 데이터 확인하기
SELECT 문으로 테이블의 전체 데이터를 조회해 보겠습니다.
mysql> select *from DemoTable;
실행 결과는 다음과 같습니다.
+-----------+-------------+ | StudentId | StudentName | +-----------+-------------+ | 1 | John | | 2 | Carol | | 3 | Sam | | 4 | Carol | | 5 | David | | 6 | Carol | +-----------+-------------+ 6 rows in set (0.00 sec)
'Carol'이라는 이름이 총 3번(StudentId 2, 4, 6) 저장되어 있는 것을 확인할 수 있습니다.
4. 중복 레코드 삭제 쿼리 실행
이제 하나의 레코드만 남기고 나머지 중복 데이터를 삭제하는 쿼리입니다.
mysql> delete tbl1 from DemoTable tbl1, DemoTable tbl2 WHERE tbl1.StudentName = tbl2.StudentName AND tbl1.StudentId > tbl2.StudentId; Query OK, 2 rows affected (0.79 sec)
쿼리 동작 원리
이 쿼리는 DemoTable을 tbl1과 tbl2라는 두 개의 별칭으로 자기 조인합니다. 그런 다음 다음 두 조건을 모두 만족하는 행을 삭제합니다.
- StudentName이 서로 같은 경우: 즉, 이름이 중복되는 레코드 쌍
- tbl1의 StudentId가 tbl2보다 큰 경우: 즉, 더 늦게 입력된(ID가 더 큰) 레코드
결과적으로 가장 작은 StudentId를 가진 레코드 하나만 남게 되며, 위 예제에서는 'Carol'의 중복 2개 행(StudentId 4, 6)이 삭제되었습니다.
5. 삭제 결과 확인하기
다시 SELECT 문으로 테이블을 조회해 보겠습니다.
mysql> select *from DemoTable;
실행 결과는 다음과 같습니다.
+-----------+-------------+ | StudentId | StudentName | +-----------+-------------+ | 1 | John | | 2 | Carol | | 3 | Sam | | 5 | David | +-----------+-------------+ 4 rows in set (0.00 sec)
'Carol' 레코드가 하나만 남고 나머지 중복 데이터가 성공적으로 제거된 것을 확인할 수 있습니다.
마무리 및 참고 사항
이 방법은 기본 키처럼 고유한 컬럼이 있는 경우에 효과적입니다. 만약 테이블에 고유 식별자가 없다면, 임시 테이블을 활용하거나 ROW_NUMBER() 윈도우 함수(MySQL 8.0 이상)를 사용하는 방법도 고려할 수 있습니다. 또한 대량의 데이터를 다룰 때는 삭제 전 반드시 백업을 수행하고, 인덱스가 설정된 컬럼으로 조인하면 성능을 크게 개선할 수 있습니다.