쿠폰 발행시스템이나 api키 발행 등에 유연하고 쉽게 사용할 수 있도록 mysql stored function으로 구성해 보았다. 중복체크가 필요할 경우 별도의 프로시저를 만들어 필터링 하면 된다. 시간이 된다면 확장해서 쿠폰 시스템을 만들어 보아야 겠다.
Example
select create_random_key(INT length, BOOLEAN include_upper_case, BOOLEAN include_lower_case, BOOLEAN include_number, TINYINT(2) first_block_type);
Parameter
- length : 난수의 길이 - include_upper_case : 대문자 포함여부 - include_lower_case : 소문자 포함여부 - include_number : 숫자 포함여부 - first_block_type : 첫번째 문자 불가유형(0:미적용, 1:'0'안됨, 2:숫자안됨)
CREATE DEFINER=`root`@`%` FUNCTION `create_random_key`(`p_length` INT, `p_include_upper_case` BOOLEAN, `p_include_lower_case` BOOLEAN, `p_include_number` BOOLEAN, `p_first_block_type` TINYINT(2))
RETURNS varchar(255) CHARSET utf8
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT '렌덤키를 생성'
BEGIN
/** 2014.05.25 - opal95 : create random key
* IN
* - p_length : key total length
* - p_include_upper_case : 대문자 포함여부
* - p_include_lower_case : 소문자 포함여부
* - p_include_number : 숫자 포함여부
* - p_first_block_type : 첫번째 문자 불가유형(0:미적용, 1:'0'안됨, 2:숫자안됨)
* RETURN : 조합된 키값
**/
/* Define */
DECLARE v_return_key VARCHAR(255) DEFAULT '';
DECLARE v_char_store CHAR(1) DEFAULT '';
DECLARE v_done INT DEFAULT 0;
/* Create Temporary Table */
DROP TEMPORARY TABLE IF EXISTS temp_random_key;
CREATE TEMPORARY TABLE temp_random_key(
random_char CHAR(1),
type TINYINT(2)
);
IF p_include_upper_case = true THEN
INSERT INTO temp_random_key (random_char, type) VALUES
/* upper case - 26 */
('A', 1),('B', 1),('C', 1),('D', 1),('E', 1),('F', 1),('G', 1),('H', 1),('I', 1),('J', 1),
('K', 1),('L', 1),('M', 1),('N', 1),('O', 1),('P', 1),('Q', 1),('R', 1),('S', 1),('T', 1),('U', 1),('V', 1),('W', 1),('X', 1),('Y', 1),('Z', 1);
END IF;
IF p_include_lower_case = true THEN
INSERT INTO temp_random_key (random_char, type) VALUES
/* lower case - 26 */
('a', 2),('b', 2),('c', 2),('d', 2),('e', 2),('f', 2),('g', 2),('h', 2),('i', 2),('j', 2),
('k', 2),('l', 2),('m', 2),('n', 2),('o', 2),('p', 2),('q', 2),('r', 2),('s', 2),('t', 2),('u', 2),('v', 2),('w', 2),('x', 2),('y', 2),('z', 2);
END IF;
IF p_include_number = true THEN
INSERT INTO temp_random_key (random_char, type) VALUES
/* number - 10 */
('0', 3),('1', 3),('2', 3),('3', 3),('4', 3),('5', 3),('6', 3),('7', 3),('8', 3),('9', 3);
END IF;
/* Select Random_character */
/* - The first character is not zero */
IF p_first_block_type = 1 THEN
SELECT random_char INTO v_return_key FROM temp_random_key WHERE random_char <> '0' ORDER BY rand() LIMIT 1;
SET p_length = p_length - 1;
/* - The first character is not number */
ELSEIF p_first_block_type = 2 THEN
SELECT random_char INTO v_return_key FROM temp_random_key WHERE type in (1, 2) ORDER BY rand() LIMIT 1;
SET p_length = p_length - 1;
END IF;
/* - The remaining character */
WHILE p_length DO
SELECT random_char INTO v_char_store FROM temp_random_key ORDER BY rand() LIMIT 1;
SET v_return_key = CONCAT(v_return_key, v_char_store);
SET p_length = p_length - 1;
END WHILE;
/* Return Key */
RETURN v_return_key;
END
