Auto increment 꼬임 해결

주문번호가 중복됐습니다. 결제가 실패하더군요. DB 기본기 문제가 실무에 치명타를 줍니다.

AUTO_INCREMENT는 테이블의 id 같은 고유 번호를 자동으로 생성하는 기능입니다. 보통은 문제없지만, 서버 크래시, 테이블 복사, 수동 조작 등으로 꼬이는 경우가 있습니다.

증상

INSERT INTO orders (product, price) VALUES ('제품A', 10000);
-- Duplicate entry '1234' for key 'PRIMARY'

분명히 AUTO_INCREMENT인데 중복 에러

원인 파악

-- 현재 AUTO_INCREMENT 값 확인
SHOW TABLE STATUS WHERE Name='orders';

-- 또는
SELECT AUTO_INCREMENT FROM information_schema.TABLES 
WHERE TABLE_SCHEMA='your_db' AND TABLE_NAME='orders';

-- 실제 최대 ID 확인
SELECT MAX(id) FROM orders;

만약 AUTO_INCREMENT가 1000인데 실제 MAX(id)가 1500이라면 꼬인 겁니다.

해결 방법

1. AUTO_INCREMENT 재설정

-- 최대 ID보다 큰 값으로 설정
ALTER TABLE orders AUTO_INCREMENT = 1501;

2. 갭(Gap) 정리

만약 ID가 1, 2, 5, 100… 이렇게 중간이 빈 상태라면:

-- 주의: 기존 ID 전부 변경됨. 외래키 있으면 위험!
SET @count = 0;
UPDATE orders SET id = @count:= @count + 1;
ALTER TABLE orders AUTO_INCREMENT = 1;

단, 외래키로 연결된 경우 절대 비추합니다. 데이터가 꼬여버려요.

3. 임시 해결: REPLACE 또는 ON DUPLICATE KEY

REPLACE INTO orders (id, product, price) VALUES (NULL, '제품A', 10000);
-- 또는
INSERT INTO orders (product, price) VALUES ('제품A', 10000)
ON DUPLICATE KEY UPDATE price=10000;

MyISAM vs InnoDB 차이

• MyISAM: AUTO_INCREMENT 값이 테이블 파일에 저장, 안정적
• InnoDB: 메모리에 저장, 서버 재시작 시 MAX(id)+1로 재계산 => 삭제 후 재시작하면 값이 뒤로 갈 수 있음

서버 크래시 후 복구

InnoDB에서 서버가 비정상 종료되면 AUTO_INCREMENT 값이 리셋될 수 있습니다.

-- 서버 재시작 후 즉시 확인
SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_NAME='orders';

-- 필요시 수동 조정
ALTER TABLE orders AUTO_INCREMENT = 정확한값;

예방 조치

1. PRIMARY KEY는 건드리지 않기: ID를 수동으로 INSERT하지 말 것
2. 외래키 설정: 다른 테이블과 연결돼 있으면 함부로 ID 변경 금지
3. 정기 점검: 주요 테이블의 AUTO_INCREMENT 값 모니터링

실전 사례

어떤 쇼핑몰은 주문 테이블에서 테스트 데이터를 삭제하면서 AUTO_INCREMENT를 1로 리셋했습니다. 그런데 결제 모듈은 주문번호를 기준으로 중복 체크를 했기 때문에, 새 주문이 "이미 결제된 주문"으로 인식되며 결제가 전부 실패했습니다.

해결: AUTO_INCREMENT를 안전한 큰 값(예: 100000)으로 설정

SQL Snippet

-- 모든 테이블의 AUTO_INCREMENT 현황
SELECT TABLE_NAME, AUTO_INCREMENT, 
        (SELECT MAX(id) FROM TABLE_NAME) as MAX_ID
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'your_database' AND AUTO_INCREMENT IS NOT NULL;

DB 기본기 문제가 실무에 치명타를 줍니다. AUTO_INCREMENT는 "자동"이지만 "완벽"은 아닙니다. 정기 점검으로 꼬임 방지하세요. 유지보수는 기본기 관리입니다.