수집 프로그램이나 방문 기록 테이블을 오래 돌리다 보면 같은 값이 수십 번씩 쌓여 있는 걸 보게 됩니다. UNIQUE 키를 미리 걸어 뒀으면 좋았겠지만, 이미 쌓인 데이터는 정리부터 해야 하죠. 같은 값을 가진 행 중 하나만 남기고 나머지를 지우는 방법을 제가 실제로 쓰던 쿼리로 남겨 둡니다.

ROW_NUMBER로 순번을 매겨서 지우기

그누보드 방문자 테이블(g5_visit)에서 같은 브라우저 정보(vi_agent)로 들어온 기록을 하나씩만 남기는 쿼리입니다.

delete from g5_visit where vi_id in (
    select vi_id
    from
        (select vi_id,
            row_number() over (partition by vi_agent, vi_agent order by vi_id desc) as row_num
        from g5_visit) a
    where a.row_num > 1
    );

partition by 로 같은 값끼리 묶고, 묶음 안에서 vi_id 큰 순서대로 1, 2, 3… 번호를 붙입니다. 번호가 1보다 큰 행, 즉 가장 최근 것을 뺀 나머지를 지우는 구조입니다. desc 를 asc 로 바꾸면 반대로 가장 오래된 기록이 남습니다.

서브쿼리를 한 번 더 감싼 데는 이유가 있습니다. MySQL은 DELETE 대상 테이블을 같은 문장의 서브쿼리에서 바로 읽으면 오류(1093)를 내는데, 파생 테이블(a)로 한 번 감싸면 결과가 임시로 만들어져서 통과됩니다. 그리고 row_number() 같은 윈도 함수는 MySQL 8.0 이상, MariaDB 10.2 이상에서만 됩니다.

셀프 조인으로 지우기

윈도 함수를 못 쓰는 버전이면 같은 테이블을 두 번 조인하는 방식을 씁니다.

DELETE n1 FROM app_word n1, app_word n2 WHERE n1.uid > n2.uid AND n1.word = n2.word

word 가 같은 두 행을 짝지어서 uid 가 큰 쪽(n1)을 지웁니다. 결과적으로 같은 값 중 uid 가 가장 작은 행 하나만 남습니다. 중복 기준이 여러 컬럼이면 조건을 이어 붙이면 됩니다. 아래는 단어와 분류(ctg)가 모두 같을 때만 중복으로 보고, 지운 뒤 uid 를 1부터 다시 매기는 묶음입니다.

DELETE n1 FROM app_word n1, app_word n2 WHERE n1.uid > n2.uid AND n1.word = n2.word AND n1.ctg = n2.ctg;    // 중복제거
ALTER TABLE app_word AUTO_INCREMENT=1;    // index 초기화
SET @COUNT = 0;    //
UPDATE app_word SET uid = @COUNT:=@COUNT+1;

리퍼러 로그 테이블에서 User-Agent 기준으로 같은 작업을 한 것도 같은 형태입니다.

DELETE n1 FROM app_referer n1, app_referer n2 WHERE n1.uid > n2.uid AND n1.HTTP_USER_AGENT = n2.HTTP_USER_AGENT;
ALTER TABLE app_referer AUTO_INCREMENT=1;
SET @COUNT = 0;
UPDATE app_referer SET uid = @COUNT:=@COUNT+1;

주의: 위 코드의 // 중복제거 같은 주석은 메모용으로 붙인 것이라 MySQL에서 그대로 실행하면 문법 오류가 납니다. 실행할 때는 지우거나 -- 중복제거 처럼 바꿔 주세요.

실행 전에 확인할 것

셀프 조인 방식은 중복이 많을수록 짝지어지는 행이 늘어나 느려집니다. 수십만 행 테이블이라면 비교 컬럼에 인덱스가 있는지 먼저 보세요. 그리고 DELETE는 되돌릴 수 없으니, 같은 조건으로 SELECT n1.* 를 먼저 돌려서 지워질 행을 눈으로 확인하는 습관이 안전합니다.

번호 재정렬은 uid 를 외부에서 참조하는 테이블이 없을 때만 해야 합니다. 그리고 InnoDB는 AUTO_INCREMENT 를 현재 최댓값보다 작게 줄 수 없어서, 번호를 다시 매기기 전에 ALTER를 실행하면 카운터가 옛 최댓값 다음으로 남습니다. 새로 들어오는 행이 이어진 번호를 받게 하려면 UPDATE 다음에 ALTER를 한 번 더 실행하면 됩니다. UPDATE 쪽에는 ORDER BY uid 를 붙여 두면 기존 순서대로 번호가 매겨져서 결과가 확실해집니다.