MySQL에서 모든 데이터를 삭제한 후 기본 키(AUTO_INCREMENT)를 1부터 다시 시작하는 방법

MySQL에서 테이블의 모든 데이터를 삭제한 뒤 기본 키가 다시 1부터 시작되도록 초기화하고 싶다면, 아래 두 가지 명령어를 사용하면 됩니다.

alter table 테이블명 AUTO_INCREMENT=1;
truncate table 테이블명;

위의 두 단계를 순서대로 실행하면, 이후 삽입되는 데이터의 기본 키 값은 1부터 시작하게 됩니다.

예제 테이블 생성하기

개념을 쉽게 이해하기 위해 실제 예제 테이블을 만들어 보겠습니다. 테이블 생성 쿼리는 다음과 같습니다.

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

AUTO_INCREMENT 속성이 적용된 UserId 컬럼을 기본 키로 설정했습니다. 이제 insert 명령어로 몇 개의 레코드를 추가해 보겠습니다.

mysql> insert into resettingPrimaryKeyDemo values();
Query OK, 1 row affected (0.15 sec)
mysql> insert into resettingPrimaryKeyDemo values();
Query OK, 1 row affected (0.10 sec)
mysql> insert into resettingPrimaryKeyDemo values();
Query OK, 1 row affected (0.08 sec)
mysql> insert into resettingPrimaryKeyDemo values();
Query OK, 1 row affected (0.12 sec)

select 문으로 테이블의 모든 레코드를 조회해 보겠습니다.

mysql> select *from resettingPrimaryKeyDemo;

실행 결과는 다음과 같습니다. 기본 키가 1부터 4까지 순차적으로 증가한 것을 확인할 수 있습니다.

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

기본 키를 1로 재설정하기

이제 기본 키를 1로 초기화하는 쿼리를 실행합니다. 먼저 ALTER TABLE로 AUTO_INCREMENT 값을 1로 설정한 후, TRUNCATE로 테이블의 전체 데이터를 삭제합니다.

mysql> alter table resettingPrimaryKeyDemo AUTO_INCREMENT=1;
Query OK, 0 rows affected (0.14 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> truncate table resettingPrimaryKeyDemo;
Query OK, 0 rows affected (0.89 sec)

테이블이 비었는지 확인해 보겠습니다.

mysql> select *from resettingPrimaryKeyDemo;
Empty set (0.00 sec)

테이블이 비워진 것을 확인했으니, 다시 몇 개의 레코드를 삽입해 보겠습니다.

mysql> insert into resettingPrimaryKeyDemo values();
Query OK, 1 row affected (0.12 sec)
mysql> insert into resettingPrimaryKeyDemo values();
Query OK, 1 row affected (0.10 sec)
mysql> insert into resettingPrimaryKeyDemo values();
Query OK, 1 row affected (0.10 sec)

이제 테이블을 조회하여 기본 키가 1부터 다시 시작되는지 확인합니다.

mysql> select *from resettingPrimaryKeyDemo;

실행 결과는 다음과 같습니다. 기본 키가 1부터 정상적으로 시작되는 것을 확인할 수 있습니다.

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

참고 사항

TRUNCATE TABLE은 테이블의 모든 데이터를 삭제할 뿐만 아니라 AUTO_INCREMENT 카운터도 자동으로 초기화하기 때문에, 사실상 TRUNCATE만 실행해도 기본 키는 1부터 다시 시작됩니다. 반면 DELETE FROM 테이블명으로 데이터를 삭제하면 AUTO_INCREMENT 값이 유지되므로, 이 경우에는 반드시 ALTER TABLE ... AUTO_INCREMENT=1 구문을 함께 사용해야 합니다. 또한 TRUNCATE는 롤백이 불가능하므로, 중요한 데이터가 있는 테이블에는 신중하게 사용해야 합니다.