Computer >> 컴퓨터 >  >> 프로그래밍 >> MySQL

MySQL AUTO_INCREMENT 완벽 가이드: 자동 증가 컬럼의 개념과 활용 규칙

MySQL에서 AUTO_INCREMENT 속성은 새로운 행(row)이 추가될 때마다 고유한 식별자를 자동으로 생성해 주는 기능입니다. 이번 글에서는 AUTO_INCREMENT 컬럼의 동작 원리와 실제 사용 방법을 예제와 함께 자세히 살펴보겠습니다.

기본 예제

먼저 아래 쿼리를 통해 AUTO_INCREMENT가 어떻게 작동하는지 확인해 보겠습니다.

쿼리

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    |
+----+---------+

위 쿼리에서는 INSERT 문에 id 컬럼 값을 지정하지 않았습니다. 그럼에도 불구하고 MySQL이 자동으로 1부터 시작하는 연속된 숫자를 id 컬럼에 할당한 것을 확인할 수 있습니다.

AUTO_INCREMENT의 주요 동작 특징

0부터 시퀀스 시작하기

명시적으로 값 0을 할당하면 숫자 시퀀스를 0부터 시작할 수 있습니다. 단, 이 기능은 SQL 모드에서 'NO_AUTO_VALUE_ON_ZERO'가 활성화되어 있지 않은 경우에만 가능합니다.

NULL 값 할당

컬럼이 NOT NULL로 선언되어 있더라도, 해당 컬럼에 NULL을 할당하면 MySQL이 자동으로 다음 시퀀스 번호를 생성합니다.

시퀀스 재설정

AUTO_INCREMENT 컬럼에 임의의 값을 직접 삽입하면, 해당 컬럼은 그 값으로 설정되고 시퀀스도 재설정됩니다. 이후에는 현재 컬럼의 최댓값보다 큰 값부터 순차적으로 자동 생성됩니다.

마지막 생성 값 조회하기

가장 최근에 자동 생성된 AUTO_INCREMENT 값은 두 가지 방법으로 확인할 수 있습니다.

  • SQL에서 LAST_INSERT_ID() 함수 사용
  • C API 함수인 mysql_insert_id() 사용

또한 기존에 존재하는 AUTO_INCREMENT 컬럼을 UPDATE 하면 AUTO_INCREMENT 시퀀스 역시 함께 재설정된다는 점에 유의해야 합니다.

AUTO_INCREMENT 사용 시 반드시 지켜야 할 규칙

AUTO_INCREMENT 속성을 사용할 때는 다음 네 가지 규칙을 준수해야 합니다.

  1. 테이블당 하나의 컬럼만 허용: 하나의 테이블에는 AUTO_INCREMENT 컬럼이 하나만 존재할 수 있으며, 일반적으로 정수형(Integer) 데이터 타입을 사용합니다.
  2. 인덱스 필수: AUTO_INCREMENT 컬럼은 반드시 인덱싱되어야 합니다. 즉, PRIMARY KEY 또는 UNIQUE 인덱스 중 하나여야 합니다.
  3. NOT NULL 제약 조건 필요: AUTO_INCREMENT 컬럼에는 NOT NULL 제약 조건이 적용되어야 합니다.
  4. 자동 NOT NULL 추가: 컬럼에 AUTO_INCREMENT 속성을 설정하면, MySQL이 자동으로 NOT NULL 제약 조건을 추가합니다.

마무리

AUTO_INCREMENT는 게시글 번호, 회원 ID처럼 고유한 식별자가 필요한 경우 매우 유용한 기능입니다. 위에서 소개한 규칙과 동작 특징을 잘 이해하고 활용하면, 데이터 무결성을 유지하면서 효율적인 PK 설계가 가능합니다.