MySQL의 auto_increment 값이 증가하는 시점

사내에서 테이블 데이터 수집을 위해서 테이블 만들 때 auto increment 컬럼을 pk로 추가해달라는 요청이 있어서 테이블 생성시 꼭 추가하고 있다. 그리고 그 auto increment 컬럼의 값은 실제로 데이터가 저장(insert)될 때만 올라갈 거라고 지레짐작만 하고 있었는데 이번에 어떤 서비스를 빨리 개발해줘야해서 새로운 테이블을 만들어 테스트하다가 auto increment값이 순서대로 올라가지 않는 현상을 보게되었다.

테스트코드는 insert -> select -> update -> select 순으로 동작하도록 구성했고 동일한 테스트를 최소 두 번 이상 돌렸다. 이렇게 돌리니까 처음에는 당연히 동일한 데이터가 없어서 no값은 default로 1로 생성이 되었다. 하지만 똑같은 테스트케이스를 다시 돌리면 duplicate key exception이 발생하면서 내부적으로 auto increment값이 증가하지 않을 줄 알았으나, 예외가 발생하여 데이터를 저장하지 못한다해도 insert가 시도될 때마다 no값이 증가하는 것을 확인할 수 있었다. auto increment값이 증가하는 케이스를 정리하자면 다음과 같다.

1. insert가 시도되면 no가 증가하게 된다.

2. duplication key 예외가 발생한다해도 증가하게 된다.

3. update시에는 증가하지 않았다.

일부 테이블은 많은 양의 insert on duplicate key update 문을 실행하고있는데 auto increment값이 overflow 되지 않을까 염려되어 현재 max no값을 조회해보니 아직 수 년은 버틸 수는 있을 정도였다.

만약 overflow 될정도로 많이 올라간다면 어떻게 해야할까 ??? no 값을 다시 1로 세팅하여 처음부터 시작하도록 할 수 있기는 하다. 아래 쿼리를 실행시키면 다시 1부터 세팅을 해준다.

ALTER TABLE YOUR_TABLE_NAME AUTO_INCREMENT=1;
SET @COUNT = 0;
UPDATE YOUR_TABLE_NAME SET AUTO_INCREMENT_COLUMN_NAME = @COUNT:=@COUNT+1;

하지만 해당 쿼리를 실행하는 동안 lock이 잡힐 것이라서 점검때에나 실행 할 수 있을 것이다.

이 방법 말고 직접 no를 세팅하는 방법도 있을 것 같다. 데이터 저장 시에 no값을 직접 할당해줄 수 있으니 말이다.