[MySQL] Auto Increment 컬럼 초기화 방법과 위험성에 대해서

Auto Increment 컬럼이란

---

자동 증가 값을 가지는 컬럼으로  값을 직접 대입할 수 없습니다.

중간에 값을 삭제한다고 해서, 삭제된 값을 재사용 하지 않으며

레코드가 적재될 때마다 1씩 증가하게 됩니다.

Auto Increment 초기화 방법

---

1. Truncate

Truncate 를 하면 Auto Increment 값이 초기화가 됩니다.

CREATE TABLE t1 (
  id          INT(11) NOT NULL AUTO_INCREMENT,
  t1_text     VARCHAR(50),
  PRIMARY KEY(id)
) AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4;
 
 
INSERT INTO t1 (t1_text) VALUES ('TEST');
INSERT INTO t1 (t1_text) VALUES ('MOZI');
 
 
SELECT * FROM t1;
+----+---------+
| id | t1_text |
+----+---------+
|  1 | TEST    |
|  2 | MOZI    |
+----+---------+
2 rows in set (0.00 sec)
 
 
TRUNCATE TABLE t1;
 
 
INSERT INTO t1 (t1_text) VALUES ('TISTORY');
 
 
SELECT * FROM t1;
+----+---------+
| id | t1_text |
+----+---------+
|  1 | TISTORY |
+----+---------+
1 row in set (0.00 sec)

2. ALTER TABLE 구문으로 INCREMENT 값을 재 설정

아래 쿼리와 같은 방식으로 Auto Increment 값을 초기화 할 수 있습니다.

DELETE FROM t1;
 
ALTER TABLE t1 AUTO_INCREMENT = 1;

Auto Increment 컬럼 재시작 시 위험성

---

일반적으로 Auto Increment 컬럼은 값을 1씩 계속 증가시키므로 중복될 일이 없습니다.

1~10까지 10건의 데이터를 적재 후 6~10 까지의 데이터를 삭제했을 때 다음 값은 6이아닌 11입니다.

그러나 MySQL 재시작의 경우에는 다음값이 달라질 수 있습니다.

파일기반 스토리지 엔진인 MyISAM 의 경우

auto_increment 값을 파일에 기록하기 때문에 재부팅을 해도 11 입니다.

메모리기반 스토리지 엔진인 InnoDB 의 경우

재부팅을 할 때 SELECT MAX(num) 구문을 사용하여 auto_increment 값을 새로 저장하기 때문입니다.

따라서 다음 값은 11이 아닌 6이 됩니다.

( 중간 값이 삭제되는 경우에는 아무런 이슈가 없습니다. )

이 증가 값이 다른 테이블들과 종속되는 값을 가지면서 서로 달라지게 되면 문제가 생길수도 있으니 주의하여 사용해야 합니다.

이런 이슈를 방지하기 위해서는 테이블별로 Auto Increment 값을 관리하는 A테이블을 생성 후

재부팅이 되면 A테이블에서 값을 조회하여 ALTER TABLE 구문을 수행해 주어야 합니다.