MySQL에서 AUTO_INCREMENT 값을 낮출 수 있을까?
MySQL에서 AUTO_INCREMENT 값을 기존보다 낮은 값으로 재설정하려면 사용 중인 스토리지 엔진에 따라 결과가 달라집니다. 핵심부터 말씀드리면, InnoDB 엔진을 사용 중이라면 AUTO_INCREMENT 값을 현재 최대값보다 낮게 설정할 수 없습니다. 이 경우 테이블 엔진을 MyISAM으로 변경해야 합니다.
참고: MyISAM 엔진은 더 낮은 값으로 설정하는 것을 허용합니다. 이 글에서는 MyISAM 엔진을 기준으로 설명합니다.
공식 문서의 내용
카운터는 이미 사용된 값과 같거나 그보다 작은 값으로 초기화할 수 없습니다. MyISAM의 경우, 지정한 값이 AUTO_INCREMENT 컬럼의 현재 최대값보다 작거나 같으면 해당 값은 '현재 최대값 + 1'로 재설정됩니다. InnoDB의 경우, 지정한 값이 컬럼의 현재 최대값보다 작으면 오류는 발생하지 않지만 현재 시퀀스 값은 변경되지 않습니다.
위 내용을 정리하면, MyISAM에서 일부 ID를 삭제한 후 AUTO_INCREMENT를 다시 설정하면 남아 있는 마지막 ID(삭제 후 남은 레코드 기준) 다음 번호부터 새로운 ID가 시작됩니다.
실습 예제: MyISAM으로 AUTO_INCREMENT 값 낮추기
1단계: MyISAM 엔진으로 테이블 생성
먼저 MyISAM 엔진을 사용하는 테이블을 생성합니다:
mysql> create table DemoTable (Id int NOT NULL AUTO_INCREMENT PRIMARY KEY)ENGINE='MyISAM'; Query OK, 0 rows affected (0.23 sec)
2단계: 레코드 삽입
INSERT 명령어로 여러 개의 레코드를 삽입합니다:
mysql> insert into DemoTable values(); Query OK, 1 row affected (0.04 sec) mysql> insert into DemoTable values(); Query OK, 1 row affected (0.03 sec) mysql> insert into DemoTable values(); Query OK, 1 row affected (0.03 sec) mysql> insert into DemoTable values(); Query OK, 1 row affected (0.02 sec) mysql> insert into DemoTable values(); Query OK, 1 row affected (0.05 sec) mysql> insert into DemoTable values(); Query OK, 1 row affected (0.08 sec)
3단계: 저장된 데이터 확인
SELECT 명령어로 테이블의 레코드를 조회합니다:
mysql> select *from DemoTable;
실행 결과는 다음과 같습니다:
+----+ | Id | +----+ | 1 | | 2 | | 3 | | 4 | | 5 | | 6 | +----+ 6 rows in set (0.00 sec)
4단계: 특정 ID 삭제
이제 Id가 4, 5, 6인 레코드를 삭제합니다:
mysql> delete from DemoTable where Id=4 or Id=5 or Id=6; Query OK, 3 rows affected (0.06 sec)
다시 전체 레코드를 조회해 보겠습니다:
mysql> select *from DemoTable;
ID 삭제 후의 출력 결과입니다:
+----+ | Id | +----+ | 1 | | 2 | | 3 | +----+ 3 rows in set (0.00 sec)
5단계: AUTO_INCREMENT 값 재설정
이제 새로운 AUTO_INCREMENT 값을 설정합니다. MyISAM 엔진에서 AUTO_INCREMENT 값을 낮추는 쿼리는 다음과 같습니다.
원래라면 현재 AUTO_INCREMENT 값은 7부터 시작해야 하지만, MyISAM 엔진을 사용하고 있기 때문에 값이 현재 최대값인 3에 1을 더한 값, 즉 3 + 1 = 4로 재설정됩니다. 따라서 새로운 ID는 4부터 시작하게 됩니다.
mysql> alter table DemoTable auto_increment=4; Query OK, 3 rows affected (0.38 sec) Records: 3 Duplicates: 0 Warnings: 0
6단계: 결과 확인
레코드를 다시 삽입한 후 전체 데이터를 조회하여 AUTO_INCREMENT가 4부터 시작하는지 확인합니다:
mysql> insert into DemoTable values(); Query OK, 1 row affected (0.03 sec) mysql> insert into DemoTable values(); Query OK, 1 row affected (0.06 sec) mysql> insert into DemoTable values(); Query OK, 1 row affected (0.02 sec)
전체 레코드를 조회하는 쿼리입니다:
mysql> select *from DemoTable;
실행 결과, 새로운 ID가 4부터 시작되는 것을 확인할 수 있습니다:
+----+ | Id | +----+ | 1 | | 2 | | 3 | | 4 | | 5 | | 6 | +----+ 6 rows in set (0.00 sec)
정리
- InnoDB: AUTO_INCREMENT 값을 현재 최대값보다 낮게 설정해도 오류는 없지만 실제로는 변경되지 않습니다.
- MyISAM: 낮은 값으로 설정하면 '현재 최대값 + 1'로 자동 재설정되어 ID 번호를 재활용할 수 있습니다.
따라서 삭제된 ID 번호를 다시 사용하고 싶다면 MyISAM 엔진을 활용하거나, InnoDB 환경이라면 테이블을 새로 생성하고 데이터를 옮기는 방법을 고려해야 합니다.