자동으로 증가하는 값 만들기

MariaDB

작성일 2026-10-27 최근 수정일 2026-10-27

MariaDB의 AUTO_INCREMENT는 새 행을 추가할 때 정수 식별값을 자동으로 배정하는 열 속성입니다. 보통 정수 열에 AUTO_INCREMENT와 PRIMARY KEY를 지정하고 INSERT에서 그 열을 생략해 사용합니다. 이 값은 행을 구분하는 식별자이며 삭제·취소 없이 항상 연속되는 순번을 보장하지는 않습니다.

AUTO_INCREMENT 열의 조건과 테이블 만들기

AUTO_INCREMENT 공식 설명에서 한 테이블에는 AUTO_INCREMENT 열을 하나만 둘 수 있고 해당 열은 인덱스의 키여야 합니다. PRIMARY KEY만 가능한 것은 아니지만, 행 식별자라면 기본 키 또는 고유성 제약과 함께 쓰는 것이 목적에 맞습니다. InnoDB의 복합 인덱스에서는 이 열이 인덱스의 첫 열이어야 합니다.

다음은 실습용 learning 데이터베이스에 CREATE·INSERT·SELECT·DELETE·ALTER 권한이 있는 계정으로 실행합니다. learning은 미리 준비하고, 아래 테이블 이름이 이미 있으면 덮어쓰지 말고 다른 실습 이름을 정합니다. 예제는 InnoDB, 증가 간격과 시작 오프셋이 모두 1인 독립 환경을 기준으로 합니다. 복제·클러스터 설정을 예제 숫자에 맞추려고 바꾸지는 마세요.

USE learning;
SELECT @@session.auto_increment_increment AS increment_step,
       @@session.auto_increment_offset AS increment_offset;
CREATE TABLE auto_id_demo (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(40) NOT NULL,
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
SHOW CREATE TABLE auto_id_demo;

두 설정값이 1이면 아래의 1·2·3 증가 예측을 그대로 확인할 수 있습니다. INT UNSIGNED는 음수가 없는 정수 범위이며 필요한 최대 데이터 수를 고려해 자료형을 선택합니다. 번호를 다 썼다고 자동으로 더 큰 자료형으로 전환되지는 않습니다. 이름처럼 의미 있는 업무 값은 별도 열에 두고 id는 안정적인 식별자로 사용합니다.

열을 생략해 자동 생성하고 생성된 값 받기

INSERT INTO auto_id_demo (name) VALUES ('연필');
SELECT LAST_INSERT_ID() AS new_id;

INSERT INTO auto_id_demo (name) VALUES ('공책'), ('지우개');
SELECT LAST_INSERT_ID() AS first_id_of_batch;

SELECT id, name FROM auto_id_demo ORDER BY id;

첫 INSERT 뒤 new_id는 1입니다. 두 행을 한 문장으로 추가한 뒤 first_id_of_batch는 2이며, 전체 행은 id=1·2·3이 됩니다. LAST_INSERT_ID()는 최근 INSERT가 생성한 첫 자동 값을 같은 접속에서 가져옵니다. 여러 행의 마지막 id를 뜻하지 않습니다.

같은 서버의 다른 접속이 행을 추가해도 이 접속의 LAST_INSERT_ID()를 덮어쓰지는 않습니다. 애플리케이션에서도 INSERT를 수행한 바로 그 연결에서 드라이버의 생성 키 기능이나 LAST_INSERT_ID()를 사용하세요. MAX(id)+1을 먼저 계산해 넣거나 다른 연결에서 MAX(id)를 조회하면 동시 작업과 섞일 수 있습니다. 증가 간격·동시 실행·실패에 따라 값이 달라지므로 첫 id와 행 수만으로 모든 id를 항상 계산할 수 있다고 가정하지 않습니다.

AUTO_INCREMENT 열에 NULL 또는 DEFAULT를 지정하는 방식도 자동 생성을 요청합니다. 0은 NO_AUTO_VALUE_ON_ZERO SQL 모드가 꺼져 있으면 자동 생성 요청으로 동작하지만, 켜져 있으면 숫자 0 자체를 저장하려고 합니다. 이 차이를 피하려면 보통 INSERT의 열 목록에서 id를 생략하는 방법이 명확합니다.

직접 큰 값을 넣었을 때 다음 번호

INSERT INTO auto_id_demo (id, name) VALUES (100, '직접 지정');
INSERT INTO auto_id_demo (name) VALUES ('자동 생성');
SELECT LAST_INSERT_ID() AS generated_id;
SELECT id, name FROM auto_id_demo ORDER BY id;

앞 예제를 순서대로 실행했다면 generated_id는 101입니다. 명시적으로 큰 값을 넣으면 이후 자동 생성에도 영향을 줍니다. 반대로 아직 쓰지 않은 작은 값을 명시적으로 넣는다고 증가 위치가 그 값으로 내려오는 것은 아닙니다. 이미 존재하는 기본 키를 지정하면 중복 키 오류가 나므로 이 동작을 기존 행 수정용으로 사용하지 마세요.

삭제와 ROLLBACK으로 번호가 비는 이유

DELETE FROM auto_id_demo WHERE id = 2;
START TRANSACTION;
INSERT INTO auto_id_demo (name) VALUES ('취소할 행');
SELECT LAST_INSERT_ID() AS rolled_back_id;
ROLLBACK;
INSERT INTO auto_id_demo (name) VALUES ('취소 뒤 추가');
SELECT LAST_INSERT_ID() AS next_id;
SELECT id, name FROM auto_id_demo ORDER BY id;

이 흐름에서는 취소할 행에 102가 배정되지만 ROLLBACK으로 그 행은 사라지고, 다음 행에는 103이 배정됩니다. 삭제한 2도 빈자리로 남아 최종 id는 1·3·100·101·103입니다. InnoDB 자동 증가 처리 문서처럼 카운터의 지속성과 트랜잭션의 데이터 취소는 별개입니다. 실패한 삽입 등도 번호 공백의 원인이 될 수 있습니다.

따라서 MAX(id)는 행 개수가 아니며, 번호가 빠졌다는 사실만으로 데이터 손실이라고 단정할 수 없습니다. 생성 순서와 트랜잭션 확정 순서도 같다고 가정하지 않습니다. 목록 화면의 연속 순번이 필요하면 조회 결과에 별도로 표시하고, 참조 중인 기본 키를 다시 매겨 빈 번호를 채우지는 마세요. 엄격한 무공백 업무 번호가 요구된다면 AUTO_INCREMENT만으로 그 요구가 해결되는지 따로 설계해야 합니다.

시작값 변경과 초기화의 적용 범위

AUTO_INCREMENT FAQ는 ALTER TABLE의 AUTO_INCREMENT 옵션으로 다음 생성 위치를 조정하는 방법을 설명합니다. 이 명령은 기존 행의 id를 다시 쓰는 명령이 아닙니다. 아래는 앞의 실습 테이블에서 이어서 실행합니다.

ALTER TABLE auto_id_demo AUTO_INCREMENT = 1000;
INSERT INTO auto_id_demo (name) VALUES ('높인 시작값');
SELECT LAST_INSERT_ID() AS raised_id;

ALTER TABLE auto_id_demo AUTO_INCREMENT = 1;
INSERT INTO auto_id_demo (name) VALUES ('낮은 값 요청 뒤');
SELECT LAST_INSERT_ID() AS actual_id;

이 조건에서 raised_id는 1000이고 actual_id는 1001입니다. 기존 최대 id=1000이 있는 테이블에서 AUTO_INCREMENT=1이라고 지정해도 1부터 새로 중복 생성하지 않습니다. 이미 사용 중인 행을 남겨 둔 채 초기화할 수 있다는 뜻으로 이해하면 안 됩니다.

비어 있는 별도 실습 테이블에서는 다음과 같이 확인할 수 있습니다. 기존 업무 테이블을 비우는 명령이 아니라, 바로 아래에서 새로 만드는 auto_reset_demo에만 실행합니다.

CREATE TABLE auto_reset_demo (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(40) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 AUTO_INCREMENT=50;
INSERT INTO auto_reset_demo (name) VALUES ('초기값 확인');
SELECT id FROM auto_reset_demo;
DELETE FROM auto_reset_demo;
ALTER TABLE auto_reset_demo AUTO_INCREMENT = 1;
INSERT INTO auto_reset_demo (name) VALUES ('빈 테이블 재시작');
SELECT id FROM auto_reset_demo;

처음 조회는 50, 마지막 조회는 1입니다. DELETE만 실행하는 것과 빈 상태에서 카운터를 별도로 낮추는 작업을 구분하세요. TRUNCATE TABLE도 테이블을 비우며 카운터를 초기화하는 데 사용되지만, 외래 키 등 제약과 암시적 커밋 특성이 있어 DELETE·ROLLBACK과 같은 것으로 다루면 안 됩니다. 초기화로 과거 id가 재사용되면 외부 파일·로그·다른 시스템의 참조와 충돌할 수 있으므로 운영 데이터의 번호 정리 수단으로 사용하지 않습니다.

예상과 다르게 증가할 때 확인 순서

SHOW CREATE TABLE에서 자료형·AUTO_INCREMENT·키 정의와 엔진을 확인합니다. 단순히 PRIMARY KEY만 지정한 열은 값을 자동 생성하지 않습니다.
자동 증가 간격·오프셋과 SQL 모드를 확인합니다. 0의 처리나 복제 환경의 증가 규칙을 일반적인 1씩 증가하는 실습과 혼동하지 않습니다.
직접 지정한 큰 값, 실패·취소된 삽입, 다른 접속의 동시 작업이 있었는지 확인합니다. 번호가 비었다는 이유만으로 행을 재번호 매기지 않습니다.
애플리케이션의 생성 키 조회가 INSERT와 같은 연결에서 이루어지는지 확인합니다. 실패한 INSERT 뒤의 이전 생성 키를 새 행의 키처럼 사용하지 않습니다.

MariaDB