mysql nextval 사용하는 방법 (oracle sequence 기능)

MySQL nextval 사용 방법 (Oracle Sequence)

mysql nextval 사용하는 방법

MySQL의 경우 AUTO INCREMENT를 사용하여 레코드에 대한 값이 중복되지 않고 1씩 증가하도록 할 수 있는데요.

때문에 AUTO INCREMENT는 기본 키(PRIMARY KEY)에 주로 사용됩니다.

하지만 Oracle의 경우 AUTO INCREMENT 기능이 없으며, 해당 역할을 Sequence가 하게 되는데요.

오라클에서 시퀀스(Sequence)는 '유일한 값을 생성해 주는 오라클 객체'이며, '값을 순차적으로 증가시켜 주기 위해서 사용되는 기능'입니다.

 

해당 포스팅은 mysql에서 필요에 의해 oracle의 sequence 기능을 사용해야 하는 경우 oracle의 sequence 기능인 'nextval를 사용하는 방법'에 대해 정리한 내용입니다.

(nextval는 시퀀스의 값을 증가시킬 때 사용하는 기능이며, currval는 현재 시퀀스를 확인할 때 사용하는 기능입니다.)

 

---

mysql에는 oracle의 sequence가 없기 때문에 sequence의 기능인 nextval를 사용할 수 없는데요.

해당 기능이 필요한 경우 아래와 같이 직접 프로시저와 함수를 생성하여 시퀀스 기능을 사용할 수 있습니다.

(mariadb의 경우 MariaDB 10.3 버전부터 Sequence 기능이 도입되었습니다.)

 

1. 시퀀스를 사용할 테이블 생성

CREATE TABLE sequences (name VARCHAR(32), currval BIGINT UNSIGNED) ENGINE = InnoDB;

여기서 name은 시퀀스명으로 사용될 칼럼이며, currval는 해당 시퀀스의 값으로 사용될 칼럼입니다.

 

 

2. 시퀀스를 생성할 프로시저 생성

DELIMITER $$
CREATE PROCEDURE `create_sequence` (IN input_name TEXT)
MODIFIES SQL DATA
DETERMINISTIC
BEGIN
	DELETE FROM sequences WHERE name = input_name;
	INSERT INTO sequences VALUES (input_name, 0);
END $$

*MODIFIES SQL DATA

함수나 프로시저 생성 시 사용할 수 있는 옵션으로 데이터를 쓸 수 있는 명령문(INSERT, UPDATE 또는 DELETE)이 포함되어 있는 경우에 'MODIFIES SQL DATA'가 사용됩니다.

쓰는 명령문이 없고, 읽는 명령문만 포함되어 있을 때는 'READS SQL DATA'가 사용됩니다.

 

*DETERMINISTIC

동일한 입력 매개 변수에 대해 항상 동일한 결과를 생성하는 경우 'DETERMINISTIC'이 사용되며, 그렇지 않은 경우 'NOT DETERMINISTIC'으로 표현됩니다.

 

 

3. 생성된 시퀀스의 다음 값을 가져오는 nextval 함수 생성

DELIMITER $$
CREATE FUNCTION `nextval` (input_name VARCHAR(32))
RETURNS BIGINT UNSIGNED 
MODIFIES SQL DATA
DETERMINISTIC
BEGIN
	DECLARE ret BIGINT UNSIGNED;
    UPDATE sequences SET currval = currval + 1 WHERE name = input_name;
    SELECT currval INTO ret FROM sequences WHERE name = input_name LIMIT 1;
    RETURN ret;
END $$

 

 

4. 시퀀스 생성 및 nextval 사용

CALL create_sequence('test_sequence');

call 명령어를 통해 위에서 만든 시퀀스를 생성하는 프로시저를 사용합니다.

 

SELECT nextval('test_sequence') FROM dual;

생성된 시퀀스에 대한 값은 다음과 같이 불러올 수 있는데요.

이때 FROM dual에서 'dual'의 경우 함수 또는 계산식을 테이블 없이 수행하기 위해 사용되는 일종의 dummy 테이블로 임시 공간으로 볼 수 있습니다.

(JPA에서는 @Query 어노테이션을 통해 nativeQuery로 해당 명령문을 호출하여 sequence 값을 가져올 수 있습니다.)

 

INSERT INTO practice.test
	(code, name) 
VALUES 
	((SELECT nextval('test_sequence') AS code FROM dual), 'name');

실제 해당 시퀀스 값을 insert에 활용할 때는 다음과 같이 사용할 수 있습니다.

 

 

 

< mysql 프로시저 관련 자료 >

2022.02.17 - [Programming/MySQL] - MySQL 프로시저 기본적인 사용법, insert 더미데이터 넣기

 

< 참고 자료 >

https://proudin.tistory.com/entry/MySql에서-Sequence-기능을-사용하는-방법