[MySQL] 중복 데이터 찾기 및 제거

MySQL로 중복 데이터 처리하기

데이터 예시

- SQL Fiddle에서 데이터 확인

id	first_name	last_name	email
1	Carine	Schmitt	carine.schmitt@verizon.net
2	Jean	King	jean.king@me.com
3	Peter	Ferguson	peter.ferguson@google.com
4	Janine	Labrune	janine.labrune@aol.com
˙˙˙	˙˙˙	˙˙˙	˙˙˙

※ 출처 : MySQLTutorial

 

중복 데이터 확인 (중복 행)

▶ 쿼리 실행

SELECT first_name, last_name, email, COUNT(*) as cnt
FROM contacts
GROUP BY email, first_name, last_name
HAVING COUNT(email) > 1 AND COUNT(first_name) > 1 AND COUNT(last_name) > 1;

 

▶ 쿼리 결과

first_name	last_name	email	cnt
Janine	Labrune	janine.labrune@aol.com	4
Jean	King	jean.king@me.com	2
Roland	Keitel	roland.keitel@yahoo.com	3
Susan	Nelson	susan.nelson@comcast.net	3

GROUP BY와 HAVING을 통해 중복된 컬럼의 개수를 확인하면 됩니다. 행에 대한 중복을 확인하는 과정이므로 컬럼 전부를 조건으로 걸었습니다.

 

중복 데이터 삭제

구분 데이터 컬럼 : id

▶ 쿼리 실행

DELETE t1 FROM contacts t1 
JOIN contacts t2
ON t1.first_name=t2.first_name AND t1.last_name=t2.last_name AND t1.email=t2.email
WHERE t1.id > t2.id;

SELECT * FROM contacts;

 

▶ 쿼리 결과

id	first_name	last_name	email
1	Carine	Schmitt	carine.schmitt@verizon.net
2	Jean	King	jean.king@me.com
3	Peter	Ferguson	peter.ferguson@google.com
4	Janine	Labrune	janine.labrune@aol.com
5	Susan	Nelson	susan.nelson@comcast.net
6	Jonas	Bergulfsen	jonas.bergulfsen@mac.com
10	Zbyszek	Piestrzeniewicz	zbyszek.piestrzeniewicz@att.net
11	Roland	Keitel	roland.keitel@yahoo.com
12	Julie	Murphy	julie.murphy@yahoo.com
14	Kwai	Lee	kwai.lee@google.com

id를 제외하고 중복된 행들이 제거되어 조회된 것을 확인할 수 있습니다.

JOIN 조건에 걸린 행들 즉, 중복된 행들을 제거한 후 조회한다는 의미입니다.

 

Distinct 및 Group by로 중복 제거

- GROUP BY 활용

▶ 쿼리 실행

SELECT * from contacts
group by first_name, last_name, email
order by id;

 

- Distinct 활용

▶ 쿼리 실행

SELECT DISTINCT first_name, last_name, email from contacts;

Distinct를 활용할 경우, 중복 컬럼을 조합하여 고유한 경우만 조회해줍니다. 따라서, id는 조회 x

하지만, group by와 order by를 활용할 경우 GROUP BY로 특정한 컬럼을 조합하여 중복 제거된 전체 컬럼을 보여줍니다.

 

id라는 구분 컬럼이 필요한 경우에는 delete나 group by를 사용하고 필요 없을 때는 distinct로 간단히 조회하면 됩니다.

 

References

1. How to Delete Duplicate Rows in MySQL - MySQL
2. SQL로 중복 데이터 확인 및 삭제하기 - yahwang
3. Finding Duplicate Rows in SQL - SILOTA

 

※ window function이나 postgreSQL을 활용하시는 경우에는 위의 2번이나 3번 Reference를 참고하시면 됩니다.