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

• 8자리 코드를 생성하는 sequence 함수

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;