이번 글에서는 MySQL의 AUTO_INCREMENT 속성이 어떻게 동작하는지 예제와 함께 자세히 알아보겠습니다.
AUTO_INCREMENT란?
AUTO_INCREMENT는 새로운 행(row)이 추가될 때마다 고유한 식별자를 자동으로 생성해 주는 속성입니다. 주로 기본 키(Primary Key)와 함께 사용되며, 개발자가 일일이 번호를 지정하지 않아도 MySQL이 순차적인 값을 자동으로 할당해 줍니다.
먼저 아래 쿼리를 살펴보겠습니다.
쿼리
CREATE TABLE tableName (
id MEDIUMINT NOT NULL AUTO_INCREMENT,
name CHAR(30) NOT NULL,
PRIMARY KEY (id)
);
INSERT INTO tableName (name) VALUES
('val1'),('val2'),('val3'),
('val4');
SELECT * FROM tableName;실행 결과
+----+---------+ | id | name | +----+---------+ | 1 | val1 | | 2 | val2 | | 3 | val3 | | 4 | val4 | +----+---------+
위 쿼리에서는 AUTO_INCREMENT가 적용된 'id' 컬럼에 별도의 값을 지정하지 않았습니다. 그 결과 MySQL이 자동으로 1부터 시작하는 연속된 숫자를 'id' 컬럼에 할당한 것을 확인할 수 있습니다.
참고로 값 0을 명시적으로 지정하면 시퀀스가 0부터 시작하도록 만들 수도 있습니다. 단, 이는 'NO_AUTO_VALUE_ON_ZERO' SQL 모드가 활성화되어 있지 않은 경우에만 가능합니다.
AUTO_INCREMENT 사용 시 알아두어야 할 핵심 사항
- NULL 허용: 컬럼이 'NOT NULL'로 선언되어 있더라도, 해당 컬럼에 NULL을 삽입하면 자동으로 숫자 시퀀스가 생성됩니다.
- 값 직접 삽입 시: AUTO_INCREMENT 컬럼에 임의의 값을 직접 삽입하면 해당 값이 설정되고, 이후 시퀀스는 현재 컬럼의 최댓값을 기준으로 다시 순차적으로 자동 생성됩니다.
- 기존 값 수정 가능: 이미 존재하는 AUTO_INCREMENT 컬럼의 값을 UPDATE로 변경할 수 있으며, 이 경우 시퀀스 역시 함께 재설정됩니다.
- 마지막 생성 값 조회: 가장 최근에 자동 생성된 AUTO_INCREMENT 값은 SQL의
LAST_INSERT_ID()함수 또는 C API의mysql_insert_id()함수를 통해 확인할 수 있습니다. - 연결(Connection)별 독립성: 위 두 함수는 각 연결(connection)에 종속적입니다. 따라서 다른 클라이언트 연결에서 INSERT 작업을 수행하더라도 반환값에는 영향을 주지 않습니다.
- 적절한 데이터 타입 선택: 필요한 최대 시퀀스 값을 충분히 저장할 수 있는 범위 내에서 가장 작은 정수 데이터 타입을 사용하는 것이 좋습니다.
- 범위 초과 시 오류 발생: 컬럼이 데이터 타입의 상한값에 도달한 상태에서 새로운 시퀀스 생성을 시도하면 실패하고 오류가 발생합니다.
팁: UNSIGNED 속성 활용하기
AUTO_INCREMENT 값은 음수가 될 수 없으므로, 가능하다면 UNSIGNED 속성을 함께 사용하는 것이 좋습니다. 예를 들어 INT 타입은 SIGNED일 때 약 -21억~21억의 범위를 가지지만, UNSIGNED로 지정하면 0~약 42억까지 표현할 수 있어 시퀀스에 사용할 수 있는 값의 범위가 두 배로 늘어납니다.
이처럼 AUTO_INCREMENT를 올바르게 이해하고 활용하면 기본 키 관리가 한결 편리해지며, 데이터 무결성도 자연스럽게 유지할 수 있습니다.