[MySQL] MySQL에서 Sequence 기능 구현하기

개인 프로젝트 진행중, 데이터베이스를 ORACLE이 아닌 MySQL를 사용중 문제가 생겼다.

INTERESTS 라는 관심사 테이블에 PRIMARY KEY가 아닌 곳에 자동생성키를 사용하려고 하니 auto increment 기능이 안먹혔다. ORACLE에서는 sequence 기능으로 자동생성키를 넣을 수 있었는데 MySQL에서 사용하려고 찾아보니, 살짝 복잡했다.

1. 우선 ORACLE에서 Sequnce는 어떻게 사용했는가?

-- 시퀀스 생성
CREATE SEQUENCE [시퀀스명]
 	increment by 1	-- 증감값
 	start with 1	-- 시작값
	NOCACHE
-- MINVALUE 1
-- MAXVALUE 9999
-- NOCYCLE
-- NOORDER;

-- 시퀀스 사용방법
insert into [시퀀스를 사용할 테이블]
values ([시퀀스명].nextval, ~~~, ~~~)

2. MySQL에서 Sequence 기능 구현하기

-- 시퀀스로 사용할 테이블
create TABLE SEQUENCES(
    name varchar(32),
    currval BIGINT unsigned
)
engine = innoDB;


-- 시퀀스로 사용할 프로시저 생성
-- 'IN' 으로 시퀀스 명을 받음
-- call [프로시저명]('[시퀀스명]')
delimiter $$
    create procedure `create_sequence` (IN the_name text)
    modifies sql data
    deterministic
    begin
        delete from SEQUENCES where name = the_name;
        insert into SEQUENCES values(the_name, 0);
    end;


-- 생성한 시퀀스(테이블)의 다음 값을 가져오는 함수
delimiter $$
    create function `nextval` (the_name VARCHAR(32))
    RETURNS BIGINT unsigned
    MODIFIES SQL DATA
    Deterministic
    begin
        declare ret BIGINT unsigned;
        update SEQUENCES set currval = currval +1 where name = the_name;
        select currval into ret from SEQUENCES where name = the_name limit 1;
        return ret;
    end;

사용하기

call create_sequence('INTEREST_SEQ');   -- 'INTEREST_SEQ' 라는 이름을 가진 시퀀스 생성

insert into INTERESTS(interest_no, category_code, name)
values (
        (select nextval('INTEREST_SEQ') from dual),
        '10000000',
        '아웃도어/여행'
       );

잘 들어갔다.