MySQL 시퀀스(Sequences) 자동 증가 키 관리의 모든 것

MySQL은 기본적으로 Oracle이나 PostgreSQL처럼 내장된 시퀀스 기능을 제공하지 않지만, 사용자 정의 테이블과 프로시저, 함수 등을 통해 시퀀스 기능을 구현할 수 있습니다. 본 문서는 MySQL 환경에서 시퀀스를 구현하고, 이를 안정적으로 사용하는 방법에 대해 2000단어 이상 분량으로 심층적으로 다루며, 실제 운영 환경에서도 바로 적용할 수 있도록 구체적인 예제와 함께 설명합니다.

MySQL 시퀀스 기능을 위한 전제 조건과 구조 설계

시퀀스 테이블 생성 – 핵심 기반 설계

시퀀스 기능을 사용하기 위해서는 먼저 시퀀스를 저장할 테이블이 필요합니다. 이 테이블은 시퀀스명을 키로 하여 각 시퀀스의 현재값(CURRVAL)을 저장합니다.

CREATE TABLE sequences (
    name VARCHAR(32) NOT NULL PRIMARY KEY,
    currval BIGINT UNSIGNED NOT NULL DEFAULT 0
) ENGINE=InnoDB;

이 구조의 주요 포인트는 다음과 같습니다:

• name: 각 시퀀스를 식별하는 고유 키입니다.
• currval: 해당 시퀀스의 현재값으로, nextval 호출 시 증가되는 값입니다.
• InnoDB 엔진: 트랜잭션과 동시성 처리를 보장하므로 안정적인 시퀀스 처리를 위해 필수적입니다.

이 테이블은 시퀀스 기능의 핵심이므로 반드시 제약조건과 트랜잭션 처리를 고려하여 설계해야 합니다.

 

반응형

시퀀스를 생성하는 저장 프로시저 생성하기

create_sequence 프로시저 구현

다음은 원하는 이름으로 시퀀스를 생성하는 저장 프로시저입니다.

DELIMITER $$

CREATE PROCEDURE `create_sequence` (IN the_name VARCHAR(32))
MODIFIES SQL DATA
DETERMINISTIC
BEGIN
    DELETE FROM sequences WHERE name = the_name;
    INSERT INTO sequences (name, currval) VALUES (the_name, 0);
END $$

DELIMITER ;

• IN 파라미터: 시퀀스 이름을 동적으로 받아 처리합니다.
• DELETE 후 INSERT: 이미 동일한 이름의 시퀀스가 존재할 경우 삭제 후 재생성하여 중복 방지합니다.
• 초기값 0 설정: 시퀀스는 일반적으로 0 또는 1부터 시작하며 필요시 다른 초기값으로 조정 가능.

반응형

시퀀스 값을 반환하는 사용자 정의 함수(nextval) 구현

nextval 함수(MySQL에서의 오라클 시퀀스 대체 방법)

다음은 nextval('시퀀스명') 형태로 호출 가능한 사용자 정의 함수입니다.

DELIMITER $$

CREATE FUNCTION `nextval` (the_name VARCHAR(32))
RETURNS BIGINT UNSIGNED
MODIFIES SQL DATA
DETERMINISTIC
BEGIN
    DECLARE ret BIGINT UNSIGNED;
    UPDATE sequences SET currval = currval + 1 WHERE name = the_name;
    SELECT currval INTO ret FROM sequences WHERE name = the_name LIMIT 1;
    RETURN ret;
END $$

DELIMITER ;

• 트랜잭션 보호: InnoDB 엔진과 함께 사용하면 동시성에서도 안정적인 값을 보장합니다.
• 함수 호출 방식: 오라클의 시퀀스명.nextval 대신 nextval('시퀀스명')을 사용합니다.

이 방식은 MySQL에서 Oracle 스타일의 시퀀스를 구현하는 가장 보편적이고 안정적인 대안입니다.

반응형

시퀀스 초기화 및 테스트

시퀀스 생성 절차

CALL create_sequence('Test');

이 명령어는 'Test'라는 이름의 시퀀스를 생성하며, currval 값을 0으로 초기화합니다.

이후 nextval('Test')를 호출하면 순차적으로 1, 2, 3…의 값이 반환됩니다.

nextval 함수 호출 예시

SELECT nextval('Test') FROM DUAL;

• DUAL 테이블 사용 이유: 오라클 호환 스타일이며, MySQL에서도 SELECT 문에 무관하게 사용할 수 있음.
• 반환값: 'Test' 시퀀스의 다음 숫자 반환.

만약 안될우 아래와 같이 해봅니다.

SELECT `nextval`('INTEREST_SEQ') AS GUID

 

시퀀스를 활용한 실무 예제

사용자 테이블에 시퀀스 번호 자동 부여

INSERT INTO user_info (
    num,
    user_id
) VALUES (
    (SELECT nextval('Test') FROM DUAL),
    '아이디'
);

• num 컬럼: PK 또는 고유한 사용자 번호로 활용 가능.
• 시퀀스 적용의 장점: 중복 없는 번호 자동 생성 가능, AUTO_INCREMENT 없이 관리 가능.

특히 다중 테이블 간 참조 키를 제어하거나 커스터마이징된 번호 체계를 운영할 때 유리합니다.

반응형

고급 기능 확장

시퀀스에 초기값 및 증가폭 옵션 부여하기

보다 유연한 시퀀스 사용을 위해 아래와 같이 테이블과 프로시저를 확장할 수 있습니다.

시퀀스 테이블 구조 확장

CREATE TABLE sequences (
    name VARCHAR(32) NOT NULL PRIMARY KEY,
    currval BIGINT UNSIGNED NOT NULL DEFAULT 0,
    increment_by INT NOT NULL DEFAULT 1
) ENGINE=InnoDB;

프로시저 개선

DELIMITER $$

CREATE PROCEDURE `create_sequence` (IN the_name VARCHAR(32), IN start_val BIGINT, IN increment_val INT)
MODIFIES SQL DATA
DETERMINISTIC
BEGIN
    DELETE FROM sequences WHERE name = the_name;
    INSERT INTO sequences (name, currval, increment_by) VALUES (the_name, start_val, increment_val);
END $$

DELIMITER ;

nextval 함수 개선

DELIMITER $$

CREATE FUNCTION `nextval` (the_name VARCHAR(32))
RETURNS BIGINT UNSIGNED
MODIFIES SQL DATA
DETERMINISTIC
BEGIN
    DECLARE ret BIGINT UNSIGNED;
    UPDATE sequences SET currval = currval + increment_by WHERE name = the_name;
    SELECT currval INTO ret FROM sequences WHERE name = the_name;
    RETURN ret;
END $$

DELIMITER ;

이 방식은 다음과 같은 장점을 제공합니다:

• 시퀀스 증가 폭 지정 가능
• 시작값 설정 가능
• 보다 유연한 시퀀스 전략 구현

반응형

운영 환경에서의 활용 팁 및 안정성 확보 전략

트랜잭션 처리와 동시성 이슈 방지

• InnoDB의 락 기반 트랜잭션을 활용하여 UPDATE 시 동시 접근을 제어.
• 애플리케이션 레벨에서 SERIALIZABLE 트랜잭션 설정 시 충돌 가능성 최소화.

에러 및 예외 처리 강화

• 시퀀스가 존재하지 않을 경우의 예외 처리 로직 포함 권장
• IF NOT EXISTS 조건문과 예외 핸들링 코드 추가로 안정성 확보 가능

IF EXISTS (SELECT 1 FROM sequences WHERE name = the_name) THEN
    -- 정상 로직
ELSE
    -- 예외 처리
END IF;

반응형

결론

MySQL에서는 내장 시퀀스 기능이 없기 때문에 사용자 정의 방식으로 직접 구현해야 합니다. 본 문서에서 제시한 방식은 단순한 숫자 증가뿐 아니라, 초기값, 증가값 지정 등 다양한 커스터마이징을 가능하게 하며 실제 운영 환경에서도 충분히 활용 가능합니다. 오라클의 nextval 스타일을 대체할 수 있도록 SQL 문법을 정제하였고, 함수 및 프로시저로 완전한 시퀀스 기능을 제공합니다.

MySQL에서 시퀀스를 안전하고 유연하게 구현하고자 한다면, 이 문서에서 설명한 테이블-프로시저-함수 구조는 반드시 익혀야 할 핵심 전략입니다.