[MySQL] AUTO_INCREMENT 초기화하고 기존 데이터 ID를 1부터 다시 매기기

개발하면서 테스트 데이터를 넣었다 지웠다 하다 보면 ID가 1, 2, 3 다음에 갑자기 57, 58로 튀는 테이블이 생깁니다. 기능상 문제는 없지만 화면에 번호가 그대로 노출되거나, 실서비스 오픈 전에 깔끔하게 정리하고 싶을 때는 번호를 다시 매기고 싶어지죠.

시작 번호만 바꾸는 경우

다음에 들어갈 번호를 지정하는 건 한 줄입니다.

ALTER TABLE [테이블명] AUTO_INCREMENT=1;

테이블을 완전히 비운 상태라면 이걸로 끝입니다. 문제는 데이터가 남아 있을 때입니다. 이미 들어 있는 ID보다 작은 값으로는 내려가지 않습니다. InnoDB 기준으로는 오류를 내지 않고 조용히 "현재 최대값 + 1"로 맞춰 버리기 때문에, 명령은 성공했는데 번호는 그대로인 것처럼 보입니다. 그래서 남아 있는 행의 번호부터 당겨 놓아야 합니다.

남은 데이터의 ID를 1부터 다시 채우기

MySQL 사용자 변수를 하나 두고, UPDATE 하면서 1씩 올려 가며 넣는 방법입니다.

ALTER TABLE [테이블명] AUTO_INCREMENT=1;
SET @COUNT = 0;
UPDATE [테이블명] SET [AUTO_INCREMENT 열 이름] = @COUNT:=@COUNT+1;

@COUNT:=@COUNT+1 은 행마다 변수를 1 늘리고 그 값을 바로 대입하는 식입니다. 실행하고 나면 모든 행의 ID가 1, 2, 3 … 순서로 다시 매겨집니다.

실행 순서와 정렬을 챙겨야 하는 이유

위 쿼리를 그대로 돌릴 때 걸리는 지점이 두 군데 있습니다.

첫째, UPDATE 가 어떤 순서로 행을 훑을지는 보장되지 않습니다. 기존 순서를 지키고 싶다면 ORDER BY 를 붙여야 하고, ID 가 기본 키라면 작은 번호부터 처리해야 아직 바뀌지 않은 다른 행과 번호가 겹쳐 중복 키 오류가 나는 일도 피할 수 있습니다.

둘째, 맨 앞의 ALTER 는 아직 번호가 정리되기 전에 실행되므로, 그 시점의 최대값 기준으로 다음 번호가 잡힙니다. 정리가 끝난 뒤 다음 번호를 "마지막 번호 + 1"로 맞추려면 UPDATE 다음에 ALTER 를 한 번 더 실행하는 편이 확실합니다.

이 두 가지를 반영하면 아래처럼 됩니다(원문 쿼리에 순서와 정렬만 보탠 것입니다).

SET @COUNT = 0;
UPDATE [테이블명] SET [AUTO_INCREMENT 열 이름] = @COUNT:=@COUNT+1 ORDER BY [AUTO_INCREMENT 열 이름];
ALTER TABLE [테이블명] AUTO_INCREMENT=1;

마지막 ALTER 에 1을 넣어도 앞서 말한 동작 때문에 실제로는 "현재 최대값 + 1"로 맞춰집니다.

손대기 전에 확인할 것

번호를 다시 매긴다는 건 기존 ID가 바뀐다는 뜻입니다. 다른 테이블이 이 ID를 참조하고 있다면(주문 테이블의 회원 번호, 댓글 테이블의 게시글 번호 같은 것) 연결이 전부 어긋납니다. 외래 키가 걸려 있으면 UPDATE 자체가 막히기도 하고요. 그래서 이 작업은 오픈 전 테스트 데이터 정리처럼 참조 관계가 없거나 함께 지워도 되는 상황에서만 쓰는 게 좋습니다. 실데이터라면 먼저 CREATE TABLE ... SELECT 로 사본을 떠 두고 진행하세요.