[MariaDB] MariaDB 10.1, 10.2 시퀀스 기능 구현

cafe24에 포팅 작업을 하면서 DB는 MariaDB로 알고 비교적 최근 버전의 안정화 버전을 사용을 해서 포팅 작업을 진행했습니다. 원래는 포팅 하려는 시스템의 환경과 소프트웨어의 버전을 정확하게 파악하고 작업해야 하는데, 그냥 호스팅 서비스에서 그렇게 오래 전의 버전을 사용할 거라 생각하지 못하고 진행한 게 불찰이었습니다.

 

cafe24의 개별 언어별 호스팅 서비스에서 지원하는 MariaDB 버전은 MariaDB 10.1이었습니다. 이 버전은 2014년 6월에 최초 배포된 버전입니다. 물론 마이너 버전은 최근 22년 5월에 배포되었습니다만 공식 지원 자체가 22년 5월에 중단되기도 했습니다.

 

제목에서도 언급했지만 MariaDB에서 시퀀스 기능은 10.3 버전부터 지원하고 있습니다. 덕분에 Oracle에서 시퀀스로 구현한 항목을 최신 버전에서는 비교적 간단하게 포팅했었는데, cafe24 포팅에서는 시퀀스 기능을 어떻게 처리하여야 할지 고민하다가 시퀀스 기능을 간단히 구현하는 것으로 방향을 잡고 작업했습니다.

---

1. MariaDB 10.3 시퀀스 기능

MariaDB 10.3에서 시퀀스는 일종의 특수한 테이블로 구현되어있어서 SHOW TABLES로 조회가 가능합니다. 생성은 DDL구문인 CREATE SEQUENCE문을 사용하고, 시퀀스 객체 사용은 ANSI SQL과 Oracle 문법을 모두 지원합니다.

시퀀스 다음 값 가져오기

-- 방법 1
NEXT VALUE FOR sequence_name

-- 방법 2
NEXTVAL(sequence_name)

-- 방법 3
-- Oracle Mode (SQL_MODE=ORACLE)
sequence_name.NEXTVAL

시퀀스 마지막 값 가져오기

-- 방법 1
PREVIOUS VALUE FOR sequence_name

-- 방법 2
LASTVAL(sequence_name)

-- 방법 3
-- Oracle Mode (SQL_MODE=ORACLE)
sequence_name.CURRVAL

 

보다 상세한 내용은 아래 링크에 있으니 읽어보시면 됩니다.

https://mariadb.com/kb/ko/sequence-overview/

---

2. 시퀀스 구현 방향

구글링에도 비슷하게 구현한 것이 있었고, MariaDB도 시퀀스를 특수한 테이블로 관리한다는 사실을 알고는 테이블을 사용하여 시퀀스를 구현하기로 했습니다. 시퀀스를 관리하는 테이블을 생성하고 시퀀스 생성과 접근은 Function을 만들어서 관리하는 것으로 방향을 잡았습니다.

 

2.1 시퀀스 테이블

 

CREATE TABLE sequences (
	seq_name  VARCHAR(100) PRIMARY KEY,  -- 시퀀스 명
	seq_value BIGINT,                    -- 시퀀스 현재 값
	seq_start BIGINT,                    -- 시퀀스 시작 값
	seq_step  INT,                       -- 시퀀스 증감
	reg_dt	  DATETIME     DEFAULT NOW() -- 시퀀스 등록일시
);

 

기존 MariaDB 10.3의 테이블 설계를 보면 좋았었는데, 삭제하고 10.1로 다운그레이드 하면서 귀차니즘에 대략 위와 같이 생성했습니다. 실제로 제가 필요한 것은 1씩 증가만 하는 시퀀스만 필요하지만 혹시나 하고, 시작 값, 현재 값, 증감 설정 기능 정도를 추가한 테이블입니다. 

 

2.2 시퀀스 생성

시퀀스 생성은 생성을 담당하는 PROCEDURE를 작성하였습니다.

 

사실 테이블로 관리하기 때문에 INSERT문을 사용해도 되지만, 어차피 DDL을 사용할 수 없기에 등록은 조금 더 직관적인(?) 함수를 만들어 관리하기로 했습니다.

 

--
-- Procedure : CREATE_SEQUENCE
--
DROP PROCEDURE IF EXISTS CREATE_SEQUENCE;

DELIMITER // 
CREATE PROCEDURE CREATE_SEQUENCE(
    IN in_seq_name VARCHAR(100),
    IN in_seq_start BIGINT,
    IN in_seq_step  INT)
    MODIFIES SQL DATA
    DETERMINISTIC
BEGIN
    -- 기존에 시퀀스가 있다면 삭제 후 추가
    DELETE FROM sequences WHERE seq_name = in_seq_name;
	
    INSERT INTO sequences 
    VALUES(in_seq_name, in_seq_start - in_seq_step, 
           in_seq_start, in_seq_step, SYSDATE());
END
//
DELIMITER ;

 

MariaDB에서 PROCEDURE 및 FUNCTION 작성은 좀 애를 먹었습니다. SQL 구문에 특별한 문제가 없어 보이는데 계속 오류가 나더군요. 원인은 DELIMITER 였습니다. DELIMITER를 설정하지 않으면 계속 오류가 납니다. PROCEDURE, FUNCTION 구문 실행 전에 꼭 설정하고 END구문 이후 다시 원래대로 설정해야 문제없이 SQL 구문이 실행됩니다.

 

그리고 CREATE_SEQUENCE Procedure에서 시퀀스 값(seq_value)은 시작 값(seq_start)에서 스텝 값(seq_step)을 빼서 저장합니다. 이유는 시퀀스 다음 값을 조회할 때 연산을 간소화하기 위해서입니다. 시퀀스 다음 값 조회(NEXTVAL)는 무조건 테이블의 시퀀스 값을 STEP만큼 증가하고 반환합니다. 그래서 현재 시퀀스 값을 처음 저장할 때 미리 스텝을 빼서 저장하여, 처음 시퀀스 다음 값 조회(NEXTVAL) 시 시작 값과 일치하도록 만들었습니다.

 

-- CREATE SEQUENCE member_seq START WITH 10000 INCREMENT BY 1;
--   CREATE_SEQUENCE('sequence_name', start, step)
--     STEP ex) 1, -1, 10, -10 
CALL CREATE_SEQUENCE('member_seq', 10000, 1);

 

호출은 CALL procedure_name으로 가능합니다.

 

2.3 시퀀스 값 조회

시퀀스 값 조회는 MariaDB의 Function 스타일을 참조했습니다.

시퀀스 다음 값 가져오기는 NEXTVAL(), 시퀀스 현재 값 가져오기는 LASTVAL()을 Function으로 구현하였습니다.

 

시퀀스 다음 값 가져오기

--
-- Function : NEXTVAL
--
DELIMITER //
CREATE or REPLACE FUNCTION NEXTVAL(in_seq_name VARCHAR(100)) 
    RETURNS BIGINT
BEGIN
    DECLARE rval BIGINT;
	
    UPDATE sequences 
       SET seq_value=seq_value + seq_step 
     WHERE seq_name = in_seq_name;
	
    SELECT seq_value INTO rval FROM sequences 
     WHERE seq_name = in_seq_name;	
	
    RETURN rval;
END
//
DELIMITER ;

 

로직은 간단합니다. 시퀀스 테이블에서 현재 값(seq_value)을 step(seq_step)만큼 증가시킨 다음, 현재 값(seq_value)을 반환합니다. 

 

시퀀스 현재 값 가져오기

--
-- Function : LASTVAL
--
DELIMITER //
CREATE or REPLACE FUNCTION LASTVAL(in_seq_name VARCHAR(100))
    RETURNS BIGINT
BEGIN
    DECLARE rval BIGINT;
	
    SELECT seq_value INTO rval FROM sequences 
     WHERE seq_name = in_seq_name;	
	
    RETURN rval;
END
//
DELIMITER ;

 

시퀀스 현재 값은 테이블에서 현재 값(seq_value)을 읽어 반환합니다.

 

-- 사용 방법
SELECT NEXTVAL('sequence_name');
SELECT LASTVAL('sequence_name');

 

사용 방법은 위와 같습니다.

 

한 가지 주의할 점은 시퀀스 명(sequence_name)은 문자열로 전달해야 합니다. MariaDB 10.3에서 시퀀스 테이블을 접근할 때는 마치 DB 객체처럼 따옴표 없이 호출합니다만, 여기서 구현한 방법은 시퀀스 명을 문자열로 전달하여 조회하기 때문에 따옴표를 추가해야만 합니다.

---

3. 작업을 마치며...

보다 정교한 구현 방법들이 있을 것으로 생각하지만, 제가 필요한 것은 좀 더 간소화한 버전의 시퀀스 기능이어서 위와 같이 구현하였습니다.

 

그리고 부득이한 경우가 아니라면 최신 안정화 버전의 MariaDB 환경에서 작업할 것을 추천합니다.