[mySQL] AUTO_INCREMENT 값 초기화와 번호 다시 매기기

테스트 데이터를 넣었다 지웠다 하다 보면 uid 같은 자동 증가 번호가 1, 2, 7, 15, 40… 처럼 띄엄띄엄 남습니다. 동작에는 문제가 없지만, 관리 화면에 번호가 그대로 보이거나 엑셀로 뽑아 넘길 때는 1부터 깔끔하게 이어져 있는 편이 보기 좋습니다. 이럴 때 쓰는 쿼리입니다.

ALTER TABLE company AUTO_INCREMENT=1;    # 결과값이 없습니다. (빈 레코드 리턴).
SET @COUNT = 0;    # 결과값이 없습니다. (빈 레코드 리턴).
UPDATE company SET uid = @COUNT:=@COUNT+1;    # 결과값이 없습니다. (빈 레코드 리턴).

주석으로 붙은 "결과값이 없습니다"는 phpMyAdmin에서 실행했을 때 나오는 메시지입니다. 세 문장 모두 SELECT가 아니라서 돌려줄 행이 없다는 뜻이지 실패한 게 아닙니다. 실제로 몇 행이 바뀌었는지는 "영향받은 행" 숫자로 확인하면 됩니다.

세 줄이 하는 일

첫 줄은 테이블의 자동 증가 시작값을 1로 돌리라는 명령입니다. 두 번째 줄에서 세션 변수 @COUNT 를 0으로 만들고, 세 번째 줄의 UPDATE가 행마다 @COUNT:=@COUNT+1 을 계산하면서 그 값을 uid 에 넣습니다. := 는 변수에 값을 대입하면서 그 값을 돌려주는 연산자라서, 행을 하나 처리할 때마다 1, 2, 3… 이 차례로 들어갑니다.

실행 순서에 주의

InnoDB에서는 AUTO_INCREMENT 를 지금 들어 있는 최댓값보다 작게 지정해도 무시되고, "최댓값 + 1"로 맞춰집니다. 그래서 위 순서대로 ALTER를 먼저 실행하면 번호를 다시 매기기 전의 큰 값이 기준이 되어, 다음에 추가되는 행은 여전히 옛 번호 뒤를 이어받습니다. 새 행까지 이어진 번호를 받게 하려면 UPDATE를 끝낸 뒤 ALTER TABLE company AUTO_INCREMENT=1; 을 한 번 더 실행해 주세요. 그러면 정리된 최댓값 다음 번호로 맞춰집니다.

UPDATE에는 ORDER BY uid 를 붙여 두는 걸 권합니다. 순서를 지정하지 않으면 어떤 순서로 번호가 매겨질지 보장되지 않아서, 기존의 앞뒤 관계가 바뀔 수 있습니다.

이런 테이블에는 쓰지 말 것

번호를 다시 매기면 기존 uid 가 전부 바뀝니다. 다른 테이블이 이 번호를 외래키나 참조값으로 들고 있다면 연결이 다 어긋납니다. 회원 번호나 주문 번호처럼 바깥에 노출된 번호도 마찬가지입니다. 이 쿼리는 다른 곳에서 참조하지 않는 단독 테이블, 아니면 운영 전 정리 단계에서만 쓰는 게 맞습니다.

데이터를 전부 지우고 번호도 1부터 다시 시작하고 싶은 거라면 TRUNCATE TABLE company; 한 줄이면 됩니다. 테이블을 비우면서 자동 증가 값도 초기화됩니다. 단, DELETE와 달리 되돌릴 수 없고 외래키로 참조되는 테이블에는 실행이 막히니 대상 테이블을 한 번 더 확인하고 실행하세요.