unique id 발급을 위행 만든 테이블 및 펑션
성능은 검증되지 않았습니다....ㅠ
필요하신 분들은 사용하세요~
- sequence table
CREATE TABLE `TB_IS_GEN_SEQ_ID` (
`site_id` VARCHAR(20) NOT NULL DEFAULT '' COMMENT '사이트아이디',
`gen_type` VARCHAR(8) NOT NULL COMMENT '증가유형',
`gen_length` INT(11) NOT NULL COMMENT '자릿수',
`prefix` VARCHAR(4) NOT NULL COMMENT '접두사',
`increment` BIGINT(20) NULL DEFAULT NULL COMMENT '증가값',
PRIMARY KEY (`site_id`, `gen_type`)
)
COMMENT='시퀀스 아이디 발급 테이블'
COLLATE='utf8_general_ci'
ENGINE=InnoDB
- function
CREATE DEFINER=`iotsid`@`%` FUNCTION `FN_GEN_SEQ_ID`(`p_site_id` varchar(20), `p_gen_type` varchar(8)
)
RETURNS varchar(20) CHARSET utf8
LANGUAGE SQL
DETERMINISTIC
MODIFIES SQL DATA
SQL SECURITY INVOKER
COMMENT ''
BEGIN
DECLARE len INT;
DECLARE pre VARCHAR(10);
DECLARE inc BIGINT;
UPDATE TB_IS_GEN_SEQ_ID
SET increment = increment + 1
WHERE site_id = p_site_id
AND gen_type = p_gen_type;
SELECT gen_length, prefix, increment
INTO len, pre, inc
FROM TB_IS_GEN_SEQ_ID
WHERE site_id = p_site_id
AND gen_type = p_gen_type;
RETURN CONCAT(pre, LPAD(inc, len-CHAR_LENGTH(pre), 0));
END
-- 수정 --
DELIMITER $$
DROP FUNCTION IF EXISTS FN_GEN_SEQ_ID$$
CREATE FUNCTION `FN_GEN_SEQ_ID`(`p_site_id` varchar(20), `p_gen_type` varchar(8))
RETURNS varchar(20) CHARSET utf8
LANGUAGE SQL
DETERMINISTIC
MODIFIES SQL DATA
SQL SECURITY INVOKER
COMMENT ''
BEGIN
DECLARE len INT;
DECLARE pre VARCHAR(10);
DECLARE inc BIGINT;
IF EXISTS( SELECT 1 FROM TB_IS_GEN_SEQ_ID WHERE site_id = p_site_id AND gen_type = p_gen_type )
UPDATE TB_IS_GEN_SEQ_ID
SET increment = increment + 1
WHERE site_id = p_site_id
AND gen_type = p_gen_type;
ELSE
INSERT INTO TB_IS_GEN_SEQ_ID (gen_type, site_id, gen_length, prefix, increment)
VALUES (p_gen_type, p_site_id, 10, LEFT(UPPER(p_gen_type), 4), 1);
END IF;
SELECT gen_length, prefix, increment
INTO len, pre, inc
FROM TB_IS_GEN_SEQ_ID
WHERE site_id = p_site_id
AND gen_type = p_gen_type;
RETURN CONCAT(pre, LPAD(inc, len-CHAR_LENGTH(pre)-CHAR_LENGTH(inc), 0));
END$$
DELIMITER;