MySQL ALTER TABLE AUTO_INCREMENT — 자동증가 완전 정리

 

 

Web Development · MySQL

MySQL ALTER TABLE AUTO_INCREMENT —
자동증가 완전 정리

AUTO_INCREMENT 시작값 · 증가 간격 · ALTER TABLE · SET @@auto_increment_increment

시작 오늘 분석할 코드

-- AUTO_INCREMENT 시작값을 1000으로 변경 ALTER TABLE testTbl3 AUTO_INCREMENT=1000; -- 증가 간격을 3으로 설정 (1000, 1003, 1006...) SET @@auto_increment_increment=3; -- 데이터 삽입 시 확인 INSERT INTO testTbl3 VALUES(NULL,'강호동',10); INSERT INTO testTbl3 VALUES(NULL,'이수근',20); INSERT INTO testTbl3 VALUES(NULL,'유재석',30); -- id 결과: 1000, 1003, 1006

AUTO_INCREMENT는 INSERT 시 자동으로 증가하는 정수 ID를 생성한다. 시작값은 ALTER TABLE로, 증가 간격은 @@auto_increment_increment 시스템 변수로 조정할 수 있다.

---

1 AUTO_INCREMENT 기본 개념

AUTO_INCREMENT

INT 등 정수 컬럼에 설정 — INSERT 시 이전 최댓값 + 증가간격으로 자동 할당

시작값 (ALTER TABLE)

ALTER TABLE tbl AUTO_INCREMENT=N 으로 다음 삽입 ID 시작값 설정

증가 간격 (@@auto_increment_increment)

SET @@auto_increment_increment=N — 기본 1, 3이면 1,4,7,10...

NULL 삽입

AUTO_INCREMENT 컬럼에 NULL 또는 0을 삽입하면 다음 자동 번호가 배정

-- 테이블 생성 시 AUTO_INCREMENT 설정 CREATE TABLE testTbl3 ( id INT AUTO_INCREMENT PRIMARY KEY, name CHAR(4) NOT NULL, dept INT NOT NULL ); -- 현재 AUTO_INCREMENT 시작값 확인 SHOW TABLE STATUS LIKE 'testTbl3'\G -- Auto_increment: 1 (기본값) -- 시작값 1000으로 변경 ALTER TABLE testTbl3 AUTO_INCREMENT = 1000; -- 간격 3으로 설정 (세션 단위) SET @@auto_increment_increment=3;

---

2 ALTER TABLE AUTO_INCREMENT 동작 원리

ALTER TABLE AUTO_INCREMENT=N은 "다음에 삽입될 ID를 N부터 시작하라"는 설정이다. 이미 N보다 큰 값이 테이블에 있으면 설정이 무시된다.

-- 1) 테이블이 비어있을 때: 1000부터 시작 ALTER TABLE testTbl3 AUTO_INCREMENT=1000; INSERT INTO testTbl3 VALUES(NULL,'첫번째',1); -- id = 1000 -- 2) 기존 최댓값 1500이 있으면 ALTER TABLE 1000은 무시됨 ALTER TABLE testTbl3 AUTO_INCREMENT=1000; -- 이미 id=1500이 있으면 다음 id는 1501 (1000이 아님) -- 3) 간격 설정 후 삽입 결과 SET @@auto_increment_increment=3; INSERT INTO testTbl3 VALUES(NULL,'A',1),(NULL,'B',2),(NULL,'C',3); -- id: 1000, 1003, 1006

ALTER TABLE AUTO_INCREMENT=N은 현재 최댓값보다 작게 설정해도 반영되지 않는다. 번호를 되감으려면 테이블을 비운 후(TRUNCATE) 설정해야 한다. TRUNCATE는 AUTO_INCREMENT도 1로 초기화한다.

---

3 관련 명령어 종합

명령	대상	효과	지속 범위
ALTER TABLE tbl AUTO_INCREMENT=N	테이블	다음 삽입 ID 시작값 설정	영구 (테이블 속성)
SET @@auto_increment_increment=N	세션/전역	증가 간격 설정 (기본 1)	세션 (@@) 또는 전역 (@@global.)
SET @@auto_increment_offset=N	세션/전역	증가 시작 오프셋 (클러스터용)	세션 또는 전역
TRUNCATE TABLE tbl	테이블	모든 행 삭제 + AI=1로 초기화	영구
SHOW TABLE STATUS	테이블	현재 Auto_increment 값 확인	조회만

---

 

반응형

 

4 실습 — testTbl3으로 AI 제어 흐름

testTbl3에서 AUTO_INCREMENT 시작값과 간격을 조정하는 전체 흐름을 단계별로 추적한다.

-- 1단계: 테이블 생성 (AI 기본 = 1부터 1씩) CREATE TABLE testTbl3 ( id INT AUTO_INCREMENT PRIMARY KEY, name CHAR(4) NOT NULL, dept INT NOT NULL ); -- 2단계: AI 시작값 1000으로 변경 ALTER TABLE testTbl3 AUTO_INCREMENT = 1000; -- SHOW TABLE STATUS LIKE 'testTbl3'\G → Auto_increment: 1000 -- 3단계: 증가 간격 3으로 세션 설정 SET @@auto_increment_increment = 3; -- 4단계: 행 삽입 (AI 컬럼에 NULL) INSERT INTO testTbl3 VALUES(NULL,'강호동',10); -- id = 1000 INSERT INTO testTbl3 VALUES(NULL,'이수근',20); -- id = 1003 INSERT INTO testTbl3 VALUES(NULL,'유재석',30); -- id = 1006 -- 5단계: 확인 SELECT * FROM testTbl3; -- 결과: 1000/강호동/10, 1003/이수근/20, 1006/유재석/30

AI=1000 설정 + 간격 3 조합은 ID를 1000, 1003, 1006 순으로 만든다. 실무에서는 특정 ID 범위를 예약하거나 클러스터 서버 간 ID 충돌 방지 목적으로 사용한다.

---

5 ALTER TABLE 다른 사용법

ALTER TABLE은 AUTO_INCREMENT 외에도 테이블 구조를 다양하게 수정할 수 있는 DDL 명령이다.

명령	예시	효과
AUTO_INCREMENT	ALTER TABLE t AUTO_INCREMENT=1000	다음 ID 시작값 설정
ADD COLUMN	ALTER TABLE t ADD COLUMN age INT	컬럼 추가
DROP COLUMN	ALTER TABLE t DROP COLUMN age	컬럼 삭제
MODIFY COLUMN	ALTER TABLE t MODIFY COLUMN name VARCHAR(20)	컬럼 타입/속성 변경
ADD CONSTRAINT	ALTER TABLE t ADD CONSTRAINT pk PRIMARY KEY (id)	제약조건 추가
RENAME TO	ALTER TABLE old_name RENAME TO new_name	테이블 이름 변경

---

6 자주 하는 실수

실수	증상	해결
기존 최댓값보다 낮은 값으로 설정	ALTER TABLE 설정이 무시됨	TRUNCATE 후 설정 또는 더 큰 값으로
@@auto_increment_increment 세션 설정	다른 세션에서는 반영 안 됨	SET GLOBAL @@global.auto_increment_increment=N
AUTO_INCREMENT를 PK가 아닌 컬럼에	오류 또는 제약 조건 위반	AUTO_INCREMENT는 반드시 PRIMARY KEY(또는 UNIQUE)
TRUNCATE로 AI 초기화 의도	FK 참조 테이블이 있으면 TRUNCATE 오류	FK 비활성화 후 TRUNCATE 또는 DELETE + ALTER

핵심 한 줄 요약

AUTO_INCREMENTINSERT 시 자동으로 1씩(기본) 증가하는 정수 ID

ALTER TABLE AI=N다음 삽입 ID 시작값을 N으로 변경 — 현재 최댓값보다 커야 반영

@@auto_increment_increment증가 간격 설정 — SET @@auto_increment_increment=3 → 1,4,7...

1000부터 3씩 증가ALTER TABLE AI=1000 + SET @@...=3 → 1000, 1003, 1006

NULL 삽입AI 컬럼에 NULL 삽입 → 다음 자동 번호 배정

0도 NULL처럼AI 컬럼에 0 삽입도 NULL처럼 다음 번호 배정

기존 최댓값 이하 설정최댓값=1500이면 ALTER TABLE AI=1000은 무시 (최소값 이상만 반영)

TRUNCATE 초기화TRUNCATE TABLE = 행 모두 삭제 + AUTO_INCREMENT = 1로 초기화

DELETE는 초기화 안 됨DELETE FROM tbl — 행만 삭제, AUTO_INCREMENT 그대로 유지

LAST_INSERT_ID()마지막 INSERT에서 배정된 AUTO_INCREMENT 값 반환

세션 vs 전역SET @@...=3은 현재 세션만 / SET @@global....=3은 전체 서버

SHOW TABLE STATUSAuto_increment 필드로 현재 다음 배정 값 확인 가능

AI + PK 필수AUTO_INCREMENT 컬럼은 PRIMARY KEY 또는 UNIQUE여야 함

AI 데이터 타입INT(최대 약 21억), BIGINT(더 큰 범위) — 범위 초과 시 오류

클러스터 서버 활용서버1: offset=1, 서버2: offset=2 + increment=2 → 홀짝 분배

ALTER TABLE AI 문법ALTER TABLE 테이블명 AUTO_INCREMENT = 숫자;

SET 시스템 변수SET @@auto_increment_increment = N; (세션 단위)

INSERT 실패와 AIINSERT 실패해도 AI 카운터 증가 가능 — ID 빈 구간 발생

testTbl3 예시AI=1000 설정 + 간격 3 → 강호동 1000, 이수근 1003, 유재석 1006

핵심 한 줄ALTER TABLE AI=N으로 시작값, @@increment로 간격 — 두 설정의 조합

AI 컬럼 조건INT NOT NULL AUTO_INCREMENT PRIMARY KEY — 최소 구성

INFORMATION_SCHEMASELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='...'

AI 최솟값ALTER TABLE AI=N에서 N이 현재 최댓값+1보다 작으면 무시됨

세션 종료 후@@auto_increment_increment=3 세션 설정은 세션 종료 시 1(기본)로 돌아감

@@offset@@auto_increment_offset=N — increment 간격 내에서 시작 위치 조정

AUTO_INCREMENT 없이 PKPK는 AUTO_INCREMENT 없이도 설정 가능 — 수동 값 지정 시

복합 PK + AI복합 PRIMARY KEY에는 AUTO_INCREMENT 사용 불가 — 단일 컬럼 PK만

AI 초기화 방법1) TRUNCATE (행도 삭제) 2) DELETE + ALTER TABLE AI=1 (행 유지 후 초기화)

AI로 중복 방지AI PK는 자동으로 고유값 — 수동으로 ID 지정할 필요 없음

INSERT 0 처리VALUES(0, '이름', 10) → 0은 AI로 처리 → 다음 자동 번호 배정

클러스터 예시서버1: offset=1, increment=2 → 1,3,5... / 서버2: offset=2 → 2,4,6...

AI 설계 원칙비즈니스 의미 없는 순수 식별자 용도로만 — 가입순서/등록순 의미 부여 금지

데이터 복구백업 복원 후 AI 값이 충돌하면 ALTER TABLE로 복원 후 최댓값+1로 설정

DDL vs DMLALTER TABLE은 DDL — 트랜잭션 롤백 안 됨 (MySQL InnoDB 기준)

UNSIGNED 활용INT UNSIGNED AUTO_INCREMENT → 0 이상 약 42억까지 범위 2배 확장

AI와 비즈니스 키회원번호(AI)와 주민번호(비즈니스 키)를 분리 — AI는 내부 식별자

구현 요약ALTER TABLE testTbl3 AUTO_INCREMENT=1000; SET @@...=3; INSERT NULL;

ON DUPLICATE와 AION DUPLICATE UPDATE 분기에서도 AI 카운터 증가 — 빈 구간 발생

테이블 복사 후 AICREATE TABLE LIKE 후 AI 설정 별도 필요 — 복사된 테이블 AI=1

간격 실무 의미간격=3은 서버 3대가 ID 1,4,7 / 2,5,8 / 3,6,9로 나눠가짐

AUTO_INCREMENT 확인SHOW TABLE STATUS WHERE Name='testTbl3' → Auto_increment 열

UNSIGNED INT 범위INT UNSIGNED: 0 ~ 4,294,967,295 (약 42.9억)

AI PK 재설정테이블 비움 → ALTER TABLE AI=원하는값 → 다시 시작

주의 사항@@auto_increment_increment 세션 설정은 세션 종료 시 초기화됨

AI와 BIGINTBIGINT AUTO_INCREMENT: 최대 9.2×10^18 — 거의 무한 수준

AI 컬럼 포함 INSERTINSERT INTO tbl (name, dept) VALUES ('이름', 10) — AI 컬럼 생략 가능

AI 시작값 설정 최적화임포트 전 AI=최댓값+1로 설정 → 기존 수동 ID와 충돌 방지

ALTER TABLE vs CREATE테이블 생성 시 AUTO_INCREMENT=1000 절로도 설정 가능

핵심 3종AI 시작값=ALTER TABLE AI=N / 간격=@@increment / 확인=SHOW STATUS

실무 패턴데이터 범위 분리 — 레코드 1~999 예약 / 1000부터 사용자 데이터

AI 간격 정리기본 1, 클러스터 = 서버 수, 범위 예약 = 큰 값 설정 후 운용

testTbl3 핵심 시나리오AI=1000 + 간격 3 → 1000, 1003, 1006 — 학습용 AI 제어 패턴

AI 요약시작값+간격 두 가지 제어 — ALTER TABLE로 시작값, SET으로 간격

DDL 중요AUTO_INCREMENT는 테이블 생성·변경 시 DDL로 제어 — DML과 무관

INSERT NULL 의미AI 컬럼에 NULL → 다음 자동 번호 배정 — 수동 지정 불필요

AUTO_INCREMENT 한 줄AI = 반복 삽입에서 PK를 자동 증가시키는 MySQL 편의 기능

ALTER + SET 조합ALTER TABLE tbl AI=1000 + SET @@...=3 → 1000, 1003, 1006, 1009...

AI 불연속 ID롤백·실패로 빈 구간은 정상 — ID 연속성 보장은 AI 목적 아님

AI 사용 금지 상황외부 키(사원번호, 학번)처럼 의미 있는 식별자는 AI 대신 수동

ALTER TABLE 주의ALTER TABLE은 DDL — 큰 테이블에서 락(잠금) 발생 가능, 주의 필요

AUTO_INCREMENT 전체 흐름CREATE → ALTER TABLE AI=N → SET @@...=K → INSERT NULL → 확인

간격의 기본값서버 재시작 또는 세션 종료 후 @@auto_increment_increment=1로 복귀

SHOW 확인 명령SHOW VARIABLES LIKE '%auto_increment%' — 현재 시작값/간격 한번에 확인

테이블 생성 시 AI 설정CREATE TABLE t (...) AUTO_INCREMENT=1000; — 생성과 동시에 시작값 지정

핵심 구문 완성ALTER TABLE testTbl3 AUTO_INCREMENT=1000; SET @@auto_increment_increment=3;

주요 변수@@auto_increment_increment=증가값 / @@auto_increment_offset=오프셋

이 파일의 핵심 공식AI=1000 + 간격 3 → 1000, 1003, 1006 = 시작(1000) + n×간격(3)

Tags

#MySQL #ALTER TABLE #AUTO_INCREMENT #자동증가 #DDL #시작값 #증가간격 #SQL #데이터베이스 #웹개발 #티스토리

▼ 티스토리 태그 입력란 복사용

MySQL, ALTER TABLE, AUTO_INCREMENT, 자동증가, DDL, 시작값, 증가간격, SQL, 데이터베이스, 웹개발, 티스토리