채번 시 고려사항

오라클은 시퀀스란 오브젝트가 있어서 이를 이용하여 채번을 관리한다. 반면에 MariaDB는 시퀀스란 오브젝트는 따로 존재하지 않는다. 대신에 테이블별 특정 컬럼에 auto_increment라는 옵션을 사용하여 채번을 관리할 수 있다. 이 경우엔 단순히 INSERT 되는 interface 테이블에 설정해서 사용한다. 오라클처럼 일련번호를 발급하고 이를 활용하는 경우엔 함수를 만들어 구현한다.

auto_increment 방식

이 방식은 새로 입력되는 ROW에 대해 유일한 값을 자동으로 부여하는 옵션을 가진다. 테이블 생성 시, 컬럼에 AUTO_INCREMENT 옵션을 정의하는데 한번 증가하면 자동적으로 줄어들진 않는다. 기본 값은 1부터, 1씩 증가하며 따로 설정이 가능하다.

create table tb_autoinc (
   seq_no int auto_increment,
   primary key (seq_no) );

insert into tb_autoinc values (null),(null); -- 1,2 입력

insert into tb_autoinc values (10); -- 입력 이후 값은 10 보다 커짐

insert into tb_autoinc values (null),(null); -- 11,12 입력

insert into tb_autoinc values (5);  -- 기존값보다 작으면 이후 값에 영향 없음

insert into tb_autoinc values (null),(null); -- 13,14입력

결과 :

실무 활용 예시로 부모 테이블에 AUTO_INCREMENT 컬럼으로 PK 생성후 INSERT 시 자식 테이블에 LAST_INSERT_ID()를 활용하여, 입력된 값을 자동으로 할당 받아서 INSERT가 가능하다.

LAST_INSERT_ID() : 세션 내 가장 최근 증가한 AUTO_INCREMENT 값을 반환하는 함수.
-- 기본 테이블에 DISK_ID(AUTO_INCREMENT) 및 디스크 기본정보 입력
insert into disk_m values (null, 디스크명, 디스크관리부서코드, ...);

-- 상세 테이블에 해당 DISK_ID값 입력 및 디스크 상세정보 입력
insert into disk_d values (null, last_insert_id(), 설치일자, 디스크타입코드, ...);

함수를 활용한 채번방법

오라클처럼 일련번호를 발급받아서 문자열과 조합하여 id를 생성하는 등 특별한 규칙을 부여하여 사용하는 경우엔 해당 방법을 사용한다. 오라클의 시퀀스와 사용방식이 유사하여 많은 이들이 이를 채택하는데 AUTO_INCREMENT 방식보다는 빠르진 않고, 객체 함수가 많아지게 되는 단점이 존재한다. 직접 사용해보면 스토리지 엔진을 myisam으로 하여 생성하는 것이 보다 바람직한 방법이다. myisam 엔진의 특성은 DML 수행하게 되면 바로 COMMIT되는 특성을 지니고 있다. 이는 오라클의 시퀀스와 유사한 메커니즘이고, 해당 방식으로 수행해야 채번 시에 경합을 최소화할 수 있다.

1. 채번용 테이블 생성
CREATE TABLE TB_SEQ_NAME
(
  seq_cur_value bigint unsigned NOT NULL DEFAULT 1 COMMENT '시퀀스 현재값'
  ,LAST_UPDATE_DATE DATETIME null default now()
  ,PRIMARY KEY (seq_cur_value)
)
ENGINE=MyISAM
ROW_FORMAT=Compact
COMMENT='TB_SEQ_NAME 시퀀스 테이블'
DEFAULT CHARSET=utf8
DEFAULT COLLATE=utf8_bin;

2. 채번값 초기화
insert into TB_SQ_LD_ORDNO values (1, now());

3. NEXTVAL 채번함수 생성
DELIMITER ;;
CREATE FUNCTION SEQ_NAME_NEXTVAL()
 RETURNS bigint
 LANGUAGE SQL
 NOT DETERMINISTIC
 MODIFIES SQL DATA
 SQL SECURITY INVOKER
 COMMENT ''
MainBlock: BEGIN

UPDATE TB_SEQ_NAME
SET seq_cur_value = (@v_current_value:= (seq_cur_value+1 )), `LAST_UPDATE_DATE`=now() ;

RETURN @v_current_value;

END MainBlock
;;

4. CURR 채번함수 생성
-- 시퀀스 현재값
delimiter ;;

CREATE FUNCTION SEQ_NAME_CURRVAL()
RETURNS int
LANGUAGE SQL
NOT DETERMINISTIC
MODIFIES SQL DATA
SQL SECURITY INVOKER
MainBlock: BEGIN

    DECLARE cur_val int;

    SELECT
        seq_cur_value INTO cur_val
    FROM
        TB_SEQ_NAME
    ;

    RETURN cur_val;

END MainBlock;;

delimiter ;

5. 사용 예제
insert into employees  values( SEQ_NAME_NEXTVAL(), '홍길동', '남', '02.1111.2222');