MySQL에서 삭제된 행으로 인해 끊긴 자동 증가(AUTO_INCREMENT) 값을 1,3,5 → 1,2,3처럼 다시 정렬하는 방법

MySQL에서 테이블의 일부 행을 삭제하면 자동 증가(AUTO_INCREMENT) 컬럼의 값에 빈 번호가 생기게 됩니다. 예를 들어 1부터 5까지의 데이터가 있던 테이블에서 2번과 4번 행을 삭제하면 1, 3, 5처럼 번호가 건너뛰게 되죠. 이번 글에서는 이렇게 끊긴 번호를 사용자 변수(@variable)를 활용해 1, 2, 3처럼 연속된 값으로 다시 정렬하는 방법을 단계별로 알아보겠습니다.

1. 테스트용 테이블 생성

먼저 자동 증가 컬럼을 가진 테이블을 생성합니다.

mysql> create table DemoTable1955
    (
    UserId int NOT NULL AUTO_INCREMENT,
    PRIMARY KEY(UserId)
    );
Query OK, 0 rows affected (0.00 sec)

2. 샘플 데이터 삽입

INSERT 문을 사용해 레코드 5개를 추가합니다. UserId는 자동 증가되므로 값을 직접 지정하지 않아도 됩니다.

mysql> insert into DemoTable1955 values();
Query OK, 1 row affected (0.00 sec)
mysql> insert into DemoTable1955 values();
Query OK, 1 row affected (0.00 sec)
mysql> insert into DemoTable1955 values();
Query OK, 1 row affected (0.00 sec)
mysql> insert into DemoTable1955 values();
Query OK, 1 row affected (0.00 sec)
mysql> insert into DemoTable1955 values();
Query OK, 1 row affected (0.00 sec)

SELECT 문으로 전체 레코드를 확인해 보겠습니다.

mysql> select * from DemoTable1955;

실행 결과는 다음과 같습니다.

+--------+
| UserId |
+--------+
|      1 |
|      2 |
|      3 |
|      4 |
|      5 |
+--------+
5 rows in set (0.00 sec)

3. 특정 행 삭제 후 상태 확인

이제 2번과 4번 행을 삭제해 보겠습니다.

mysql> delete from DemoTable1955 where UserId IN(2,4);
Query OK, 2 rows affected (0.00 sec)

테이블을 다시 조회하면 아래와 같이 번호가 1, 3, 5로 건너뛰어 있는 것을 확인할 수 있습니다.

+--------+
| UserId |
+--------+
|      1 |
|      3 |
|      5 |
+--------+
3 rows in set (0.00 sec)

4. 사용자 변수로 자동 증가 값 재정렬하기

삭제로 인해 끊긴 번호(1, 3, 5)를 연속된 값(1, 2, 3)으로 바꾸려면 UPDATE 문과 사용자 변수를 함께 사용합니다. @increment_value 변수를 1씩 증가시키면서 UserId를 순서대로 갱신하는 방식입니다.

mysql> update DemoTable1955
    set UserId = (@increment_value := @increment_value + 1)
    order by UserId;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 3  Changed: 2 Warnings: 0

변경 결과를 다시 한번 확인해 보겠습니다.

mysql> select * from DemoTable1955;

실행 결과, 번호가 1, 2, 3으로 연속적으로 정렬된 것을 볼 수 있습니다.

+--------+
| UserId |
+--------+
|      1 |
|      2 |
|      3 |
+--------+
3 rows in set (0.00 sec)

정리 및 주의 사항

이처럼 UPDATE + ORDER BY + 사용자 변수 조합을 사용하면 삭제된 행으로 인해 비어 있는 자동 증가 번호를 손쉽게 압축(re-numbering)할 수 있습니다.

다만 몇 가지 주의할 점이 있습니다. 첫째, 해당 컬럼이 다른 테이블의 외래 키(Foreign Key)로 참조되고 있다면 참조 무결성이 깨질 수 있으므로 반드시 관련 데이터도 함께 수정해야 합니다. 둘째, MySQL 8.0 이상에서는 사용자 변수를 SELECT나 UPDATE에서 할당하는 방식이 권장되지 않으므로, 윈도우 함수인 ROW_NUMBER()를 활용하는 것이 더 안전하고 표준적인 방법입니다. 셋째, 실제 운영 환경에서는 번호 재정렬 작업 전 반드시 데이터를 백업하고 트랜잭션 내에서 실행하는 것이 좋습니다.