[MySQL] INSERT + AUTO_INCREMENT 사용법 완벽 가이드

MySQL에서 가장 널리 사용되는 테이블 키 설정 중 하나는 바로 AUTO_INCREMENT이다.
이는 주로 기본 키(PK)로 사용되며, INSERT할 때마다 자동으로 1씩 증가된 숫자가 할당된다.

하지만 막상 실무에 들어가면 값이 건너뛰거나, 초기화하거나, 강제로 지정해야 하는 다양한 상황이 발생하게 된다.

🔍 AUTO_INCREMENT란?

AUTO_INCREMENT는 MySQL에서 자동으로 고유한 숫자를 생성해주는 속성이다.
주로 id 컬럼에 사용되며, INSERT 시 명시하지 않아도 자동으로 값이 증가한다.

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50)
);

이 테이블에 다음과 같이 데이터를 삽입하면

INSERT INTO users (name) VALUES ('홍길동');

id는 자동으로 1부터 시작해서 증가하게 된다.

반응형

✅ 기본 INSERT + AUTO_INCREMENT 예제

INSERT INTO users (name) VALUES ('김철수'), ('이영희'), ('박민수');

이 경우 id는 각각 1, 2, 3이 자동으로 부여된다.

🧪 AUTO_INCREMENT 값 건너뛰는 경우

실무에서는 간혹 id 값이 건너뛰는 현상이 생긴다. 그 이유는 다음과 같다.

• 중간에 ROLLBACK이 발생한 경우
• 트랜잭션 실패로 INSERT가 취소된 경우
• INSERT IGNORE 또는 INSERT ... ON DUPLICATE KEY UPDATE에서 충돌이 발생한 경우
• 자동 증가 값이 예비로 예약되었다가 사용되지 않은 경우

이는 MySQL의 설계상 의도된 동작이며,
AUTO_INCREMENT 값의 연속성은 보장되지 않는다.

⚙️ AUTO_INCREMENT 초기화(리셋) 하기

테이블의 자동 증가값을 다시 1부터 시작하고 싶다면 다음 쿼리를 사용한다.

ALTER TABLE users AUTO_INCREMENT = 1;

주의할 점은 테이블이 비어있어야만 정상적으로 1부터 시작된다.
데이터가 남아 있으면, 가장 큰 값 다음부터 다시 증가한다.

-- 데이터가 존재하는 경우
ALTER TABLE users AUTO_INCREMENT = 10; -- 강제로 다음 id를 10부터 시작시킴

이 방법은 테스트 데이터 초기화나 테이블 클론 작업 등에 유용하다.

반응형

🧠 AUTO_INCREMENT 값을 직접 삽입할 수 있을까?

가능하다. 다음처럼 id 값을 명시하면 MySQL은 해당 값을 그대로 삽입한다.

INSERT INTO users (id, name) VALUES (100, '정의진');

이후 자동 증가 시작 값도 자동으로 100 이후부터 조정된다.

INSERT INTO users (name) VALUES ('이강민'); -- id는 101이 됨

 

🔐 AUTO_INCREMENT 컬럼은 반드시 PRIMARY KEY일까?

반드시 그런 건 아니지만, MySQL은 PRIMARY KEY 또는 UNIQUE NOT NULL 조건이 있는 컬럼만
AUTO_INCREMENT 설정을 허용한다.

-- 복합키에서도 사용 가능
CREATE TABLE orders (
  user_id INT,
  order_seq INT AUTO_INCREMENT,
  PRIMARY KEY(user_id, order_seq)
) AUTO_INCREMENT = 100;

위 예제처럼 복합 기본키에서의 서브키 역할로도 사용 가능하다.

🔄 테이블 복사 시 AUTO_INCREMENT 유지하기

테이블을 복사할 때 AUTO_INCREMENT 값을 그대로 복사하고 싶다면 다음을 참고한다.

-- 테이블 구조 + 데이터 복사
CREATE TABLE users_copy LIKE users;
INSERT INTO users_copy SELECT * FROM users;

-- AUTO_INCREMENT 값 복사
ALTER TABLE users_copy AUTO_INCREMENT = (SELECT AUTO_INCREMENT
  FROM information_schema.TABLES
  WHERE TABLE_NAME = 'users');

이렇게 하면 기존 테이블의 증가값 설정도 그대로 이어받을 수 있다.

반응형

🧪 실전 예제 – 주문 테이블에 주문 번호 자동 생성

CREATE TABLE orders (
  order_id INT AUTO_INCREMENT PRIMARY KEY,
  product_name VARCHAR(100),
  order_date DATETIME DEFAULT NOW()
);

INSERT INTO orders (product_name) VALUES ('무선 마우스');
INSERT INTO orders (product_name) VALUES ('기계식 키보드');

각 주문은 자동으로 order_id가 증가하며 생성된다.
이 방식은 전자상거래, 물류 시스템, ERP 등 대부분의 실무에서 필수적으로 사용된다.

⚠️ 주의사항

• 하나의 테이블에 하나의 AUTO_INCREMENT 컬럼만 설정 가능하다.
• TRUNCATE TABLE 명령을 사용하면 자동 증가 값도 초기화된다.
• MyISAM은 마지막 AUTO_INCREMENT 값을 디스크에 저장하지만,
  InnoDB는 재시작 시 메모리에서 다시 계산하기 때문에 값이 달라질 수 있다.

📎 공식 문서 참고

MySQL AUTO_INCREMENT 공식 문서
https://dev.mysql.com/doc/refman/8.0/en/example-auto-increment.html