[MaraiaDB] 식별 코드 (serial code) 생성 프로시저 만들기

"AUTO_INCREMENT" 속성을 갖는 일련번호는 중복되지 않아서, 테이블 내부의 Primary key를 만들때 사용하면 좋으나, 이를 API 등에 그대로 사용하면, 악용할 여지가 크기 때문에 각 레코드별로 외부에서 임의로 추측할 수 없는 값을 이용해야만 합니다.

 

GUID() 혹은 UUID()가 좋은 방법이 될 수 있습니다. 외부에서 임의로 추측하여 찾을 수 없다는 장점이 있지만, 너무 길어서 외우는 것은 불가능할 정도고 적어 두기도 불편할 만큼 긴 문자열입니다.

4c302295-a2fe-11eb-8130-fa154e32888f
fa154e32-888f-4c30-2295-a2fe11eb7132

그래서 저는 별도의 식별 코드를 위하여 UUID() 함수를 이용하여, 각 레코드별로 고유한 값이 할당되도록 하고, 이 고유한 값을 이용하여 CRC32(), HEX() 두 함수를 이용하여, 중복되지 않는 식별 코드가 만들어지도록 하였습니다. 엄격하게는 4byte 데이터를 그냥 16진 문자열로 변환한 것이기 때문에 8자리 문자열이지만, 웬만해서는 중복되지 않고 8자라 외우기도 가능하고, 적어두기도 크게 무리가 가지 않을 길이입니다.

 

예제 테이블을 다음과 같이 구성합니다.

CREATE OR REPLACE TABLE PRODUCT
(
    product_no        INT NOT NULL AUTO_INCREMENT
    , product_name    VARCHAR(250) NOT NULL
    , product_uuid    VARCHAR(40) NOT NULL
    , serial_code    CHAR(8) NOT NULL

    , PRIMARY KEY ( product_no )
);

식별코드 생성을 위하여 다음과 같이 저장 프로시저를 작성하였습니다.

DELIMITER $$

CREATE OR REPLACE PROCEDURE sp_generate_product( IN p_need_count INT )
    LANGUAGE SQL
    NOT DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT '입력한 개수만큼 PRODUCT를 생성한 후, 이를 반환합니다.'
BEGIN


DECLARE v_uuid VARCHAR(60);
DECLARE v_crc32 VARCHAR(8);
DECLARE v_product_no INT;


DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
    ROLLBACK;
    DROP TABLE IF EXISTS T_PRODUCT_LIST;
END;


CREATE OR REPLACE TEMPORARY TABLE T_PRODUCT_LIST
(
    product_no    INT NOT NULL
);


START TRANSACTION;


FOR v_index IN 1..p_need_count DO

    SET v_uuid = UUID();
    SET v_crc32 = HEX(CRC32(v_uuid));

    INSERT INTO PRODUCT ( product_name, product_uuid, serial_code )
    VALUES ('product', v_uuid, v_crc32);

    SET v_product_no = LAST_INSERT_ID();
    INSERT INTO T_PRODUCT_LIST VALUES ( v_product_no );

END FOR;

COMMIT;

SELECT
    L.product_no, P.product_name, P.product_uuid, P.serial_code
FROM T_PRODUCT_LIST AS L
    INNER JOIN PRODUCT AS P ON P.product_no = L.product_no
ORDER BY 1;


DROP TABLE IF EXISTS T_PRODUCT_LIST;


END$$

DELIMITER ;

생성된 식별 코드 목록을 그대로 반환하도록 하였습니다.

CALL sp_generate_product(3);

"1"    "product"    "e6b10634-d187-11eb-8b7e-439f21f40e15"    "74545522"
"2"    "product"    "e6b16ab4-d187-11eb-8b7e-439f21f40e15"    "A08744BB"
"3"    "product"    "e6b17075-d187-11eb-8b7e-439f21f40e15"    "78156055"

 

참고 자료

• "MariaDB - CREATE PROCEDURE":https://mariadb.com/kb/en/create-procedure/
• "MariaDB - START TRANSACTION":https://mariadb.com/kb/en/start-transaction/
• "MySQL START TRANSACTION , COMMIT":https://blog.naver.com/luvtoma/221164710293
• "MariaDB - DECLARE HANDLER":https://mariadb.com/kb/en/declare-handler/