[mysql] MySQL에서 중복 행 제거

MySQL 테이블에서 복제본을 삭제하는 것은 일반적인 문제이며, 사전에 그러한 복제본을 피하기 위해 제약 조건이 누락 된 결과입니다. 그러나이 공통적 인 문제는 일반적으로 특정 접근 방식이 필요한 특정 요구 사항과 함께 제공됩니다. 접근 방식은 예를 들어 데이터의 크기, 유지해야 할 복제 된 항목 (일반적으로 첫 번째 또는 마지막 항목), 유지할 인덱스가 있는지 또는 추가 작업을 수행할지 여부에 따라 달라야합니다. 복제 된 데이터에 대한 조치.

테이블 UPDATE를 수행 할 때 FROM 원인에서 동일한 테이블을 참조 할 수없는 것과 같이 MySQL 자체에도 몇 가지 특성이 있습니다 (MySQL 오류 # 1093이 발생 함). 이 제한은 임시 테이블과 함께 내부 쿼리를 사용하여 극복 할 수 있습니다 (위의 일부 접근 방식에서 제안한대로). 그러나이 내부 쿼리는 빅 데이터 소스를 처리 할 때 특히 잘 수행되지 않습니다.

그러나 복제본을 제거하는 더 나은 방법이 존재하는데, 이는 효율적이고 신뢰할 수 있으며 다른 요구에 쉽게 적용 할 수 있습니다.

일반적인 아이디어는 새로운 임시 테이블을 작성하는 것입니다. 일반적으로 중복을 피하기 위해 고유 제한 조건을 추가하고 중복을 처리하면서 이전 테이블의 데이터를 새 테이블에 삽입합니다. 이 접근 방식은 간단한 MySQL INSERT 쿼리에 의존하고, 추가 복제를 피하기위한 새로운 제약 조건을 생성하며, 내부 쿼리를 사용하여 복제본과 메모리에 보관해야하는 임시 테이블을 검색 할 필요가 없도록합니다 (따라서 빅 데이터 소스에도 적합).

이것이 달성 될 수있는 방법입니다. 다음과 같은 열 이있는 테이블 employee 가 있다고 가정합니다 .

employee (id, first_name, last_name, start_date, ssn)

중복 ssn 열이 있는 행을 삭제하고 첫 번째 항목 만 찾도록하려면 다음 프로세스를 수행 할 수 있습니다.

-- create a new tmp_eployee table
CREATE TABLE tmp_employee LIKE employee;

-- add a unique constraint
ALTER TABLE tmp_employee ADD UNIQUE(ssn);

-- scan over the employee table to insert employee entries
INSERT IGNORE INTO tmp_employee SELECT * FROM employee ORDER BY id;

-- rename tables
RENAME TABLE employee TO backup_employee, tmp_employee TO employee;

기술 설명

• 1 번 줄 은 직원 테이블 과 정확히 동일한 구조로 새로운 tmp_eployee 테이블을 만듭니다.
• 2 번 줄은 새로운 tmp_eployee 테이블에 UNIQUE 제약 조건을 추가하여 더 이상 중복되지 않도록합니다.
• 3 번 줄 은 중복 된 항목을 무시하고 새 직원 항목을 새 tmp_eployee 테이블에 삽입하여 id로 원래 직원 테이블을 스캔 합니다.
• 4 번째 줄은 새 직원 테이블이 중복없이 모든 항목을 보유하고 이전 데이터의 백업 복사본이 backup_employee 테이블 에 유지 되도록 테이블 이름을 바꿉니다.

⇒ 이 방법을 사용하면 1.6M 레지스터가 200 초 이내에 6k로 변환되었습니다.

Chetan 은이 프로세스에 따라 다음을 실행하여 모든 복제본을 빠르고 쉽게 제거하고 고유 한 제약 조건을 만들 수 있습니다.

CREATE TABLE tmp_jobs LIKE jobs;

ALTER TABLE tmp_jobs ADD UNIQUE(site_id, title, company);

INSERT IGNORE INTO tmp_jobs SELECT * FROM jobs ORDER BY id;

RENAME TABLE jobs TO backup_jobs, tmp_jobs TO jobs;

물론이 프로세스는 중복을 삭제할 때 다른 요구에 맞게 조정될 수 있습니다. 다음은 몇 가지 예입니다.

✔ 첫 번째 항목 대신 마지막 항목을 유지하기위한 변형

때로는 첫 번째 항목 대신 마지막으로 복제 된 항목을 유지해야 할 때가 있습니다.

CREATE TABLE tmp_employee LIKE employee;

ALTER TABLE tmp_employee ADD UNIQUE(ssn);

INSERT IGNORE INTO tmp_employee SELECT * FROM employee ORDER BY id DESC;

RENAME TABLE employee TO backup_employee, tmp_employee TO employee;

• 3 번 라인에서 ORDER BY id DESC 절은 마지막 ID가 나머지 ID보다 우선 순위를 갖도록합니다.

✔ 복제본에 대한 일부 작업 수행에 대한 변형 (예 : 발견 된 복제본 수 계산)

발견 된 중복 항목에 대해 추가 처리를 수행해야하는 경우가 있습니다 (예 : 중복 수 유지).

CREATE TABLE tmp_employee LIKE employee;

ALTER TABLE tmp_employee ADD UNIQUE(ssn);

ALTER TABLE tmp_employee ADD COLUMN n_duplicates INT DEFAULT 0;

INSERT INTO tmp_employee SELECT * FROM employee ORDER BY id ON DUPLICATE KEY UPDATE n_duplicates=n_duplicates+1;

RENAME TABLE employee TO backup_employee, tmp_employee TO employee;

• 3 번 라인에는 새로운 열 n_duplicates 가 생성됩니다.
• 4 번 라인에서 INSERT INTO … ON DUPLICATE KEY UPDATE 쿼리는 중복이 발견 될 때 추가 업데이트를 수행하는 데 사용됩니다 (이 경우 카운터 증가) INSERT INTO … ON DUPLICATE KEY UPDATE 쿼리는 발견 된 중복에 대해 다른 유형의 업데이트를 수행하는 데 사용됩니다.

✔ 자동 증분 필드 ID 재생성에 대한 변형

때때로 우리는 자동 증분 필드를 사용하며 가능한 한 인덱스를 작게 유지하기 위해 중복 삭제를 활용하여 새 임시 테이블에서 자동 증분 필드를 재생성 할 수 있습니다.

CREATE TABLE tmp_employee LIKE employee;

ALTER TABLE tmp_employee ADD UNIQUE(ssn);

INSERT IGNORE INTO tmp_employee SELECT (first_name, last_name, start_date, ssn) FROM employee ORDER BY id;

RENAME TABLE employee TO backup_employee, tmp_employee TO employee;

• 3 번 라인에서, 테이블의 모든 필드를 선택하는 대신 id 필드는 건너 뛰어 DB 엔진이 자동으로 새로운 필드를 생성합니다

✔ 추가 변형

원하는 동작에 따라 많은 추가 수정도 가능합니다. 예를 들어, 다음 쿼리는 두 번째 임시 테이블을 사용하여 1) 첫 번째 항목 대신 마지막 항목을 유지합니다. 그리고 2) 발견 된 사본에 대한 카운터를 증가시킨다; 또한 3) 자동 증분 필드 ID를 재생성하면서 입력 순서는 이전 데이터와 동일하게 유지합니다.

CREATE TABLE tmp_employee LIKE employee;

ALTER TABLE tmp_employee ADD UNIQUE(ssn);

ALTER TABLE tmp_employee ADD COLUMN n_duplicates INT DEFAULT 0;

INSERT INTO tmp_employee SELECT * FROM employee ORDER BY id DESC ON DUPLICATE KEY UPDATE n_duplicates=n_duplicates+1;

CREATE TABLE tmp_employee2 LIKE tmp_employee;

INSERT INTO tmp_employee2 SELECT (first_name, last_name, start_date, ssn) FROM tmp_employee ORDER BY id;

DROP TABLE tmp_employee;

RENAME TABLE employee TO backup_employee, tmp_employee2 TO employee;

답변

DELETE t1 FROM my_table t1, my_table t2 WHERE t1.id < t2.id AND t1.my_field = t2.my_field AND t1.my_field_2 = t2.my_field_2 AND ...

-- Create temporary table

CREATE TABLE temp_table LIKE table1;

-- Add constraint
ALTER TABLE temp_table ADD UNIQUE(title, company,site_id);

-- Copy data
INSERT IGNORE INTO temp_table SELECT * FROM table1;

-- Rename and drop
RENAME TABLE table1 TO old_table1, temp_table TO table1;
DROP TABLE old_table1;