What's MySQL Sequence?
- 보통 RDB(Relationship Database)에서 PK(Primary Key)는 각 테이블에서 Auto_increment(자동증가) 조건이 걸려있는
id
값을 활용하는 경우가 많다
- 하지만 정규화가 점차 늘어나고 테이블이 늘어나면 여기저기 나도너도
id
컬럼으로 PK를 활용하기에 관리 포인트가 많아 지면 어떤 테이블의 id
값인지 구별하기 힘들어 질 수 있다
- 따라서
id
컬럼 대신 해당 테이블에 관련된 string(문자열)을 포함한 sequence(시퀀스)를 만들어서 관리하면, 테이블의 PK를 한눈에 구별 및 식별하여 관리하기 용이하다
TRIGGER(트리거)방식
- 총의 방아쇠 의미하듯, 해당 테이블에 트리거를 설정하면 데이터들 INSERT 하기 전 트리거가 동작한다
- 사전에 트리거 형식의 함수를 사용함으로서 DBA원하는 시퀀스의 데이터를 넣을 수 있다
What Your Plan?!
- 자동문자열 방식으로 Primary Key를 생성하고자 할 때, MySQL에서 시퀀스(sequnce)방식으로 구현 가능
-sequence
테이블의 id(AUTO_INCEMENT인)
값을 활용
- MySQL
TRIIGER
만들어서 LAPD
함수의 인자로 원하는 문자열
과 sequnence
테이블의id
값을 넣어 자동문자열 시퀀스를 만듬
main_category
테이블에 row가 생길 때 마다 해당되는 컬럼은 자동문자열 방식으로 값이 생김
TRIGGER-1안
create main_category
DROP TABLE IF EXISTS MySQL.tb_main_category;
CREATE TABLE MySQL.`tb_main_category` (
`main_code` varchar(9) CHARACTER SET utf8 NOT NULL COMMENT '카테고리 중분류 코드',
`main_name` varchar(64) CHARACTER SET utf8 DEFAULT NULL COMMENT '카테고리 중분류 명칭',
PRIMARY KEY (`main_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='메인 카테고리 정보';
main_category_seq 테이블 생성
DROP TABLE IF EXISTS MySQL.main_category_seq;
CREATE TABLE MySQL.main_category_seq (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY
);
생성된 트리거 삭제 및 새로운 트리거 생성
DROP TRIGGER IF EXISTS MySQL.main_category_insert_seq;
DELIMITER $$
CREATE TRIGGER MySQL.main_category_insert_seq
BEFORE INSERT ON MySQL.tb_main_category
FOR EACH ROW
BEGIN
INSERT INTO MySQL.main_category_seq VALUES (NULL);
SET NEW.main_code = CONCAT('MACT', LPAD(LAST_INSERT_ID(), 4, '0'));
END$$
DELIMITER ;
INSERT INTO tb_main_category
INSERT INTO MySQL.tb_main_category (main_name) VALUES
('클래스')
,('인문교양')
,('모임');
main_category 데이터 확인
SELECT *
FROM MySQL.tb_main_category;
TRIGGER-2안
- main_category_seq 테이블 생성하지 않음
- trigger 일부 수정
- tb_main_category 테이블의 row 카운트를 하여 다음 id값을 시퀀스로 만듦
생성된 트리거 삭제 및 새로운 트리거 생성
DROP TRIGGER IF EXISTS MySQL.main_category_insert_seq;
DELIMITER $$
CREATE TRIGGER MySQL.main_category_insert_seq
BEFORE INSERT ON MySQL.tb_main_category
FOR EACH ROW
BEGIN
DECLARE seq_id INT;
SET seq_id = (SELECT COUNT(*) FROM tb_category);
SET NEW.category_cd = CONCAT('MACT', LPAD(seq_id+1, 4, '0'));
END$$
DELIMITER ;
INSERT INTO tb_main_category
INSERT INTO MySQL.tb_main_category (main_name) VALUES
('클래스')
,('인문교양')
,('모임');
main_category 데이터 확인
SELECT *
FROM MySQL.tb_main_category;
Function(함수)방식
- Trigger 방식을 사용할 경우, 각 테이블에 Trigger를 적용해야함으로 비효율적일 수 있고, 또한 동시적으로 INSERT 할 때, 병목으로 인하여 LOCK 현상이 발생할 수 있다
- 따라서 sequence(시퀀스)를 관리하는 테이블과 관련 Function을 해당 스키마에서 생성하여, 각 테이블에서 데이터를 INSERT할 때, 함수를 활용하면 효율적이다
create tb_sequence
CREATE TABLE `tb_sequence` (
`seq_name` varchar(4) NOT NULL COMMENT '시퀀스에 사용될 명칭',
`seq_no` int NOT NULL COMMENT '시퀀스 no',
PRIMARY KEY (`seq_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='tb_sequence'
create fn_get_seq_8
DELIMITER ;;
CREATE FUNCTION MySQL.`fn_get_seq_8`(`p_seq_name` VARCHAR(4))
RETURNS varchar(8) CHARSET utf8
BEGIN
DECLARE RTN_VAL VARCHAR(8);
INSERT INTO MySQL.tb_sequence (seq_name, seq_no)
values (p_seq_name, LAST_INSERT_ID(1))
ON DUPLICATE KEY UPDATE seq_no=LAST_INSERT_ID(seq_no+1);
set @ret = row_count();
if @ret = 0 then
set RTN_VAL = '0';
else
SET RTN_VAL = (SELECT CONCAT(p_seq_name, LPAD(LAST_INSERT_ID(),4,'0')));
end if;
RETURN RTN_VAL;
END ;;
DELIMITER ;
create fn_get_seq_12
- 12자리 아이디를 생성하는 sequence 함수
DELIMITER ;;
CREATE FUNCTION MySQL.`fn_get_seq_12`(`p_seq_name` VARCHAR(4))
RETURNS varchar(12) CHARSET utf8
BEGIN
DECLARE RTN_VAL VARCHAR(12);
INSERT INTO MySQL.tb_sequence (seq_name, seq_no)
values (p_seq_name, LAST_INSERT_ID(1))
ON DUPLICATE KEY UPDATE seq_no=LAST_INSERT_ID(seq_no+1);
set @ret = row_count();
if @ret = 0 then
set RTN_VAL = '0';
else
SET RTN_VAL = (SELECT CONCAT(p_seq_name, LPAD(LAST_INSERT_ID(),8,'0')));
end if;
RETURN RTN_VAL;
END ;;
DELIMITER ;
INSERT INTO tb_main_category
INSERT INTO flask_mysql.tb_main_category (main_code, main_name) VALUES
(MySQL.fn_get_seq_8('MACT'), '클래스')
,(MySQL.fn_get_seq_8('MBCL'), '인문교양')
,(MySQL.fn_get_seq_8('MBCL'), '모임');
tb_sequence 데이터 확인
- 각 테이블의 시퀀스와 마지막 id값을 관리하고 있다
main_category 데이터 확인
SELECT *
FROM flask_mysql.tb_main_category;
감사합니다!