[Database][MySQL] ID 생성 procedure 및 function 생성 - 유사 Sequence 기능

Oracle에는 Sequence 기능이 있어서 PK 생성에 쉽게 사용할 수 있는데, MySQL에는 해당 기능이 없어 Procedure 와 Function을 사용하여 커스텀 해야한다.

두 가지 방식으로 설명을 할 것인데, 기본적인 작동 방식은 똑같으나

1) 모든 테이블에 일괄적으로 적용되는 Sequence
2) 각 테이블 별로 적용되는 Sequence

두 가지를 함께 소개한다.

1. 시퀀스를 사용할 테이블 생성

# 1번방식(모든 테이블에 일괄적으로 적용되는 Sequence)
CREATE TABLE ID_SEQUENCE (
	CURRENT_VALUE BIGINT UNSIGNED
) ENGINE = InnoDB

# 2번방식(각 테이블 단위로 적용되는 Sequence)
CREATE TABLE ID_SEQUENCE (
	TABLE_NAME VARCHAR(32), 
	CURRENT_VALUE BIGINT UNSIGNED
) ENGINE = InnoDB

2. Procedure 생성

Procedure 생성은 만약 1번방식을 사용한다면 생략해도 된다.

DELIMITER $$
CREATE PROCEDURE `id_gen_sequence` (IN param_name text)
MODIFIES SQL DATA
DETERMINISTIC
BEGIN
	DELETE FROM ID_SEQUENCE WHERE TABLE_NAME = param_name;
    INSERT INTO ID_SEQUENCE VALUES(param_name, 0);
END
$$

여기서 param_name은 해당 Procedure를 호출할 때 넘겨줄 파라미터이다. 위 프로시져를 실행할 때

call id_gen_sequence('TEST_TABLE');

위와 같은 형태로 호출을 하게 되고 해당 TABLE_NAME의 Row가 있다면 삭제되고 없다면 바로 { TABLE_NAME: 'TEST_TABLE', CURRENT_VALUE: 0 }인 Row가 생성된다.

참고로, DELIMITER 키워드는 Procedure 생성 쿼리 내에 세미콜론(;)이 포함되어 있기 때문에 쿼리가 종료되는 것을 방지하기 위함이다. DETERMINISTIC 키워드는 입력값이 동일하면
리턴값도 받드시 동일한 함수가 반복 호출될때 이전에 호출한 값이 호출되면 함수가 호출되지 않고 바로 값을 되돌려 줘서 함수호출 부하를 줄이는 것이다.

3. Function 생성

# 1번 (모든 테이블에 일괄적으로 적용되는 Sequence)
DELIMITER $$
CREATE FUNCTION `nextval` ()
RETURNS BIGINT UNSIGNED
MODIFIES SQL DATA
DETERMINISTIC
BEGIN
	DECLARE ret BIGINT UNSIGNED;
	UPDATE ID_SEQUENCE SET CURRENT_VALUE = CURRENT_VALUE + 1;
    SELECT CURRENT_VALUE INTO ret FROM ID_SEQUENCE;
    RETURN ret;
END
$$

# 2번 (각 테이블 별로 적용되는 Sequence)
DELIMITER $$
CREATE FUNCTION `nextval`(param_name VARCHAR(32))
RETURNS BIGINT UNSIGNED
MODIFIES SQL DATA
DETERMINISTIC
BEGIN
	DECLARE ret BIGINT UNSIGNED;
    UPDATE ID_SEQUENCE SET CURRENT_VALUE = CURRENT_VALUE +1 WHERE TABLE_NAME = param_name;
    SELECT CURRENT_VALUE INTO ret FROM ID_SEQUENCE WHERE TABLE_NAME = param_name LIMIT 1;
    RETURN ret;
END
$$

위 Function 을 호출하면

# 1번
SELECT nextval() FROM DUAL

# 2번
SELECT nextval('TEST_TABLE') FROM DUAL

와 같이 호출할 수 있다.

---

추가로 PK를 BIGINT타입으로 하지 않고 VARCHAR로 하는 경우도 있는데 이럴 경우 FUNCTION을 아래와 같이 수정하여 적용할 수 있다.

예를 들어 VARCHAR(20)에 테이블명을 파라미터로 받아 비는 자리를 '0'으로 채워서 생성하려면

# 1번
DELIMITER $$
CREATE FUNCTION `nextval` ()
RETURNS VARCHAR(20)
MODIFIES SQL DATA
DETERMINISTIC
BEGIN
	DECLARE RET VARCHAR(20);
	UPDATE ID_SEQUENCE SET CURRENT_VALUE = CURRENT_VALUE + 1;
    SELECT lpad(CURRENT_VALUE, 20, '0') INTO ret FROM ID_SEQUENCE;
    RETURN ret;
END
$$

# 2번
DELIMITER $$
CREATE FUNCTION `nextval` (param_name CHAR(5))
RETURNS VARCHAR(20)
MODIFIES SQL DATA
DETERMINISTIC
BEGIN
	DECLARE RET VARCHAR(20);
	UPDATE ID_SEQUENCE SET CURRENT_VALUE = CURRENT_VALUE + 1;
    SELECT CONCAT(param_name, lpad(CURRENT_VALUE, 20, '0')) INTO ret FROM ID_SEQUENCE;
    RETURN ret;
END
$$

위와 같이 생성할 수 있다.

위 예시는 그냥 간단하게 작성한거라 param_name을 5글자로 고정해두었다.