MariaDB - AUTO_INCREMENT 확인 및 초기화 방법

DB 관리를 하다 보면 연속된 숫자 ID를 자동으로 생성해 주는 AUTO_INCREMENT 속성을 자주 사용하게 됩니다.

시퀀스란?

시퀀스(Sequence)는 데이터베이스에서 자동으로 생성되는 일련의 숫자들을 의미합니다.

MariaDB에서는 AUTO_INCREMENT 속성을 사용하여 테이블의 기본 키 값으로 이러한 시퀀스를 생성해 사용합니다.

새로운 레코드가 삽입될 때마다 자동으로 증가하여, 각 행에 고유한 식별자를 부여합니다.

AUTO_INCREMENT의 필요성

• 고유성 보장 : 각 레코드에 고유한 식별자를 자동으로 부여하여 데이터 고유성을 보장합니다.
• 데이터 무결성 유지 : 중복된 값이 들어가는 것을 방지하여 데이터 무결성을 유지합니다.

AUTO_INCREMENT 사용 시 발생할 수 있는 문제점

• 갭 발생 : 수동으로 레코드를 삭제하거나 트랜잭션 실패 등의 이유로 시퀀스 값에 갭이 생길 수 있습니다.
• 동시성 문제 : 동시에 여러 트랜잭션이 시퀀스를 요청하면 경합 조건이 발생할 수 있습니다.
• 초기화 문제 : 시퀀스 값을 초기화할 때 적절히 처리하지 않으면 데이터 무결성이 깨질 수 있습니다.

InnoDB에서의 AUTO_INCREMENT

1. 트랜잭션 지원 : InnoDB는 트랜잭션을 지원하며 커밋되기 전까지 AUTO_INCREMENT 값이 할당되지 않습니다.

2. AUTO_INCREMENT 잠금 모드 : innodb_autoinc_lock_mode 변수를 통해 잠금 모드를 설정할 수 있습니다.

• 0 (Traditional) : 각 INSERT 문이 실행될 때 테이블에 대해 AUTO_INCREMENT 락을 잡습니다.
• 1 (Concurrent) : 각 INSERT 문에 대해 AUTO_INCREMENT 락을 잡습니다. (기본값)
• 2 (Interleaved) : 대량 삽입 작업에서 AUTO_INCREMENT 락을 사용하지 않습니다.

3. 재시작 후 값 유지 : 마지막으로 사용된 AUTO_INCREMENT 값을 데이터베이스 재시작 후에도 유지합니다.

MyISAM에서의 AUTO_INCREMENT

1. 트랜잭션 미지원 : MyISAM은 트랜잭션을 지원하지 않습니다.

2. 동작 방식 : 테이블 수준에서 AUTO_INCREMENT 값을 관리하며 INSERT 문이 실행될 때마다 값을 증가시킵니다.

3. 재시작 후 값 초기화 : MyISAM은 데이터베이스 재시작 후 테이블에 가장 큰 현재 값부터 다시 계산합니다.

AUTO_INCREMENT 값 확인 및 초기화

# 테이블의 현재 AUTO_INCREMENT 값을 확인 하는 쿼리
SHOW TABLE STATUS LIKE '테이블명'\G

# AUTO_INCREMENT 값 초기화 쿼리
ALTER TABLE 테이블명 AUTO_INCREMENT = 초기값;

TRUNCATE를 사용한 초기화

# TRUNCATE 명령어로 테이블의 모든 데이터를 삭제하면 AUTO_INCREMENT 값 초기화 됨
TRUNCATE TABLE 테이블명;

AUTO_INCREMENT 값 재정렬

1. 기존 데이터 백업

CREATE TABLE tmp_users AS SELECT * FROM users;

 

2. 원본 테이블 초기화

TRUNCATE TABLE users;

 

3. 데이터 재 삽입

INSERT INTO users (name, ...) SELECT name, ... FROM temp_users ORDER BY id;

** INSERT 문에 AUTO_INCREMENT 컬럼을 포함하지 않아야 함!!!

 

4. 임시 테이블 삭제

DROP TABLE tmp_users;