MySQL에서 중복 행 삭제
이 기사에서는 MySQL의 테이블에 있는 중복 행을 삭제하는 다양한 방법을 보여줍니다. 이 작업을 수행하기 위한 네 가지 접근 방식이 있습니다.
DELETE JOIN문을 사용하여 중복 행 삭제- 중첩 쿼리를 사용하여 중복 행 삭제
- 임시 테이블을 사용하여 중복 행 삭제
ROW_NUMBER()함수를 사용하여 중복 행 삭제
다음 스크립트는 4개의 열(custid, first_name, last_name 및 email)이 있는 customers 테이블을 생성합니다.
CREATE TABLE customers (custid INT, first_name VARCHAR(50) NOT NULL, last_name VARCHAR(50) NOT NULL, email VARCHAR(255) NOT NULL );
CREATE TABLE customers (custid INT, first_name VARCHAR(50) NOT NULL, last_name VARCHAR(50) NOT NULL, email VARCHAR(255) NOT NULL );
데모를 위해 customers 테이블에 샘플 데이터 값을 삽입하십시오.
INSERT INTO customers
VALUES (110,'Susane','Mathew','sussane.mathew@gmail.com'),
(124,'Jean','Carl','jean.carl@gmail.com'),
(331,'Peter','cohelo','peter.coh@google.com'),
(114,'Jaine','Lora','jaine.l@abs.com'),
(244,'Junas','sen','jonas.sen@mac.com');
INSERT INTO customers
VALUES (113,'Jaine','Lora','jaine.l@abs.com'),
(111,'Susane','Mathew','sussane.mathew@gmail.com'),
(665,'Roma','Shetty','roma.sh11@yahoo.com'),
(997,'Beatrice','shelon','beatrice.ss22@yahoo.com'),
(332,'Peter','cohelo','peter.coh@google.com');
참고: 이를 사용하여 삭제 후 값을 다시 삽입할 수 있습니다.
다음은 customers 테이블의 모든 데이터를 반환하는 쿼리입니다.
SELECT * FROM customers order by custid;
테이블에서 중복 레코드를 찾기 위해 customers 테이블에서 아래에 언급된 쿼리를 실행합니다.
SELECT custid, COUNT(custid) FROM customers GROUP BY custid HAVING COUNT(custid) > 1;
보시다시피 결과에 중복된 고객 ID가 있는 3개의 행이 있습니다.
DELETE JOIN 문을 사용하여 중복 행 삭제
delete statement와 함께 INNER JOIN을 사용하면 MySQL의 테이블에서 중복 행을 제거할 수 있습니다.
다음 쿼리는 고객 ID가 가장 낮은 중복 레코드가 있는 모든 행을 선택하여 중첩 쿼리의 개념을 사용합니다. 발견되면 custid가 가장 낮은 중복 레코드를 삭제합니다.
Delete from customers where custid IN (Select c1.custid FROM customers as c1
INNER JOIN customers as c2 ON c1.custid < c2.custid AND c1.email = c2.email);
고객 테이블은 이 쿼리에서 두 번 참조됩니다. 따라서 별칭 c1 및 c2를 사용합니다.
출력은 다음과 같습니다.
3개의 행이 삭제되었음을 나타냅니다.
SELECT custid, COUNT(custid) FROM customers GROUP BY custid HAVING COUNT(custid) > 1;
이제 이 쿼리는 중복 행이 삭제되었음을 의미하는 빈 집합을 반환합니다.
select 쿼리를 사용하여 customers 테이블의 데이터를 확인할 수 있습니다.
SELECT * FROM customers;
중복 행을 삭제하고 가장 낮은 custid를 유지하려는 경우 동일한 쿼리를 사용할 수 있지만 다음 명령문과 같이 조건이 약간 변형됩니다.
Delete from customers where custid IN (Select c1.custid FROM customers as c1
INNER JOIN customers as c2 ON c1.custid > c2.custid AND c1.email = c2.email);
다음 출력은 중복 행을 제거한 후 customers 테이블의 데이터를 보여줍니다.
중첩 쿼리를 사용하여 중복 행 삭제
이제 중첩 쿼리를 사용하여 중복 행을 제거하는 단계별 절차를 살펴보겠습니다. 이것은 문제를 해결하는 비교적 간단한 접근 방식입니다.
먼저 이 쿼리를 사용하여 테이블에서 고유한 레코드를 선택합니다.
Select * from (select max(custid) from customers group by email);
그런 다음 아래와 같이 where 절과 함께 delete 쿼리를 사용하여 테이블에서 중복 행을 삭제합니다.
Delete from customers where custid not in
(select * from (select max(custid) from customers group by email));
출력은 다음과 같습니다.
임시 테이블을 사용하여 중복 행 삭제
이제 임시 테이블을 사용하여 중복 행을 제거하는 단계별 절차를 살펴보겠습니다.
- 먼저 원본 테이블과 동일한 구조로 새 테이블을 생성해야 합니다.
- 이제 원본 테이블의 고유한 행을 임시 테이블에 삽입합니다.
- 원래 테이블을 삭제하고 임시 테이블의 이름을 원래 테이블로 바꿉니다.
1단계: CREATE TABLE 및 LIKE 키워드를 사용한 테이블 생성
테이블의 전체 구조를 복사하는 구문은 다음과 같습니다.
CREATE TABLE destination_table LIKE source;
따라서 동일한 고객 테이블이 있다고 가정하고 아래에 주어진 쿼리를 작성합니다.
CREATE TABLE temporary LIKE customers;
2단계. 임시 테이블에 행 삽입
아래 주어진 쿼리는 고객의 고유한 행을 복사하여 임시 테이블에 씁니다. 여기서는 이메일로 그룹화합니다.
INSERT INTO temporary SELECT * FROM customers GROUP BY email;
3단계. 원래 고객 테이블을 삭제하고 이름을 고객으로 변경하여 원래 테이블 역할을 할 임시 테이블을 만듭니다.
DROP TABLE customers;
ALTER TABLE temporary RENAME TO customers;
출력은 다음과 같습니다.
이 접근 방식은 데이터 값에 대해 작업하는 것보다 테이블 구조를 변경해야 하므로 시간이 많이 걸립니다.
ROW_NUMBER() 함수를 사용하여 중복 행 삭제
ROW_NUMBER() 함수는 MySQL 버전 8.02에 도입되었습니다. 따라서 8.02 이상의 MySQL 버전을 실행하는 경우 이 접근 방식을 사용할 수 있습니다.
이 쿼리는 ROW_NUMBER() 함수를 사용하여 각 행에 숫자 값을 할당합니다. 중복 이메일의 경우 행 번호는 1보다 큽니다.
SELECT custid, email, ROW_NUMBER() OVER ( PARTITION BY email ORDER BY email ) AS row FROM customers;
위의 코드 조각은 중복 행의 ID 목록을 반환합니다.
SELECT custid
FROM ( SELECT custid, ROW_NUMBER() OVER (PARTITION BY email ORDER BY email) AS row FROM customers) t WHERE row > 1;
중복 값이 있는 고객 목록을 가져오면 아래와 같이 where 절에 하위 쿼리가 있는 delete 문을 사용하여 이를 삭제할 수 있습니다.
DELETE FROM customers
WHERE custid IN
(SELECT custid FROM
(SELECT custid, ROW_NUMBER() OVER
(PARTITION BY email ORDER BY email) AS row FROM customers) t
WHERE row > 1);
출력은 다음과 같습니다.
