Sequence Overview

소개

시퀀스는 CREATE SEQUENCE문에서 지정된 대로 일련의 숫자값을 생성하는 객체입니다.

CREATE SEQUENCE는 시퀀스를 생성하고, 생성된 시퀀스는 NEXT VALUE FOR sequence_name으로 호출될 때 마다 새로운 값을 생성합니다.일련번호가 생성되는 방식을 원하는대로 제어하고 싶을 경우에 AUTO INCREMENT를 대신해 사용할 수 있습니다 시퀀스는 값을 캐싱하기 떄문에 경우에 따라 AUTO INCREMENT보다 훨씬 빠를 수 있습니다.(CREATE SEQUENCE문의 CACHE 값 한도만큼 캐싱합니다. 기본값은 1000입니다.) 또 다른 장점은 사용된 모든 시퀀스의 생성된 마지막 값에 접근할 수 있도록 하여, LAST_INSERT_ID()의 한계점 중 하나를 해결했다는 것입니다.

시퀀스 생성

CREATE SEQUENCE문은 시퀀스를 생성하는 데 사용됩니다. 다음은 100에서 시작하여 매번 10 씩 증가하는 시퀀스의 예시입니다.

CREATE SEQUENCE s START WITH 100 INCREMENT BY 10;

다음처럼 CREATE SEQUENCE문은 ,기본값과 함께, SHOW CREATE SEQUENCE STATEMENT문을 이용해 볼 수 있습니다:

SHOW CREATE SEQUENCE s\G
*************************** 1. row ***************************
       Table: s
Create Table: CREATE SEQUENCE `s` start with 100 minvalue 1 maxvalue 9223372036854775806 
  increment by 10 cache 1000 nocycle ENGINE=InnoDB

시퀀스 객체 사용

시퀀스의 다음 값을 얻기 위해서는 아래처럼 사용하세요

NEXT VALUE FOR sequence_name

또는

NEXTVAL(sequence_name)

Oracle 모드(SQL_MODE=ORACLE)에서는

sequence_name.nextval

현재의 연결에서 사용된 시퀀스의 마지막 값을 얻기 위해서는 아래처럼 사용하세요:

PREVIOUS VALUE FOR sequence_name

또는

LASTVAL(sequence_name)

Oracle 모드(SQL_MODE=ORACLE)에서는

sequence_name.currval

구문 동작의 예를 들면 아래와 같습니다:

SELECT NEXTVAL(s);
+------------+
| NEXTVAL(s) |
+------------+
|        100 |
+------------+

SELECT NEXTVAL(s);
+------------+
| NEXTVAL(s) |
+------------+
|        110 |
+------------+

SELECT LASTVAL(s);
+------------+
| LASTVAL(s) |
+------------+
|        110 |
+------------+

DEFAULT에서 시퀀스 사용하기

create sequence s1;
create table t1 (a int primary key default (next value for s1), b int);
insert into t1 (b) values (1),(2);
select * from t1;
+---+------+
| a | b    |
+---+------+
| 1 |    1 |
| 2 |    2 |
+---+------+

시퀀스 변경

ALTER SEQUENCE문은 시퀀스를 변경하는데 사용됩니다.예를 들어, 시퀀스를 다른 값으로부터 다시 시작하게 하려면:

ALTER SEQUENCE s RESTART 50;

SELECT NEXTVAL(s);
+------------+
| NEXTVAL(s) |
+------------+
|         50 |
+------------+

다음처럼 SETVAL function은 시퀀스에서 반환될 다음 값을 정하는데 사용할 수 있습니다:

SELECT SETVAL(s, 100);
+----------------+
| SETVAL(s, 100) |
+----------------+
|            100 |
+----------------+

SETVAL은 시퀀스 값을 증가시키는데만 사용할 수 있습니다. 더 낮은 값을 설정하려는 시도는 실패하고, NULL을 반환합니다:

SELECT SETVAL(s, 50);
+---------------+
| SETVAL(s, 50) |
+---------------+
|          NULL |
+---------------+

시퀀스 제거

다음처럼 DROP SEQUENCE문은 시퀀스를 드롭(제거)하는데 사용됩니다:

DROP SEQUENCE s;

복제

마스터-마스터 설정 또는 Galera에서 시퀀스를 사용하려는 경우 INCREMENT = 0 을 사용해야합니다. 이는 시퀀스에게 auto_increment_increment 및 auto_increment_offset을 사용해 각 서버별로 고유한 값을 생성하도록 지시합니다.

표준 준수

MariaDB 10.3는 시퀀스에 대해 ANSI SQL과 Oracle 문법을 모두 지원합니다.

그러나 SEQUENCE가 일종의 특수한 테이블로서 구현되었기 때문에, 테이블과 동일한 네임스페이스를 사용합니다. 이러한 구현의 장점은 SHOW TABLES에 시퀀스가 표시되고, CREATE TABLE을 이용해 시퀀스를 생성하고 DROP TABLE을 이용해 제거할 수 있다는 것입니다. SELECT를 이용해 다른 테이블처럼 조회할 수도 있습니다. 이렇게 함으로서 테이블을 다루는 오래된 도구들이 시퀀스에 대해서도 동작할 수 있도록 보장합니다.

시퀀스 객체가 많은 컨텍스트에서 마치 일반적인 테이블처럼 동작하므로, LOCK TABLES의 영향을 받습니다. LOCK TABLE이 시퀀스에 영향을 주지 않는 Oracle과 같은 다른 DBMS에서는 그렇지 않습니다.

노트

시퀀스 구현의 목표는 mysqldump와 같은 오래된 도구들이 변경되지 않고 동작하게 하면서, 동시에 일반적으로 사용되는 시퀀스 표준과의 호환성을 유지하는 것입니다.

이를 가능케 하기 위해, sequence는 현재 몇가지 독특한 속성을 갖는 테이블로 구현되었습니다.

시퀀스 테이블의 특별한 속성은 아래와 같습니다:

• 하나의 시퀀스 테이블은 항상 하나의 행을 가집니다
• CREATE TABLE 또는 CREATE SEQUENCE를 사용해 시퀀스가 생성되면, 한 행이 삽입됩니다.
• 시퀀스 테이블에 삽입(insert)하려고 시도하면, 해당 단일 행이 갱신(update)됩니다. 이는 mysqldump이 동작할 수 있도록 하고, 또한 시퀀스의 모든 속성의 변경을 단일 삽입만으로 수행할 수 있도록 해 주는 추가적인 이점을 제공합니다.신규 어플리케이션들은 물론 ALTER SEQUENCE도 사용할 수 있습니다.
• UPDATE 또는 DELETE 는 시퀀스 객체에 대해서 수행할 수 없습니다.
• 시퀀스 테이블에 SELECT를 수행하면 캐시에 예약 된 값을 제외하고 시퀀스의 현재 상태가 표시됩니다. next_value 컬럼은 열은 캐시에 예약되지 않은 다음 값을 보여줍니다.
• FLUSH TABLES will close the sequence and the next sequence number generated will be according to what's stored in the Sequence object. In effect, this will discard the cached values.
• 많은 일반적인 테이블에 대한 연산이 시퀀스 테이블에서도 동작합니다.다음 섹션을 참조하세요.

Table Operations that Work with Sequences

• SHOW CREATE TABLE sequence_name. This shows the table structure that is behind the SEQUENCE including the field names that can be used with SELECT or even CREATE TABLE.
• CREATE TABLE sequence-structure ... SEQUENCE=1
• ALTER TABLE sequence RENAME TO sequence2
• RENAME TABLE sequence_name TO new_sequence_name
• DROP TABLE sequence_name. This is allowed mainly to get old tools like mysqldump to work with sequence tables.
• SHOW TABLES

Implementation

Internally, sequence tables are created as a normal table without rollback (the InnoDB, Aria and MySAM engines support this), wrapped by a sequence engine object. This allowed us to create sequences with almost no performance impact for normal tables. (The cost is one 'if' per insert if the binary log is enabled).

Underlying Table Structure

The following example shows the table structure of sequences and how it can be used as a table. (Output of results are slightly edited to make them easier to read)

create sequence t1;
show create sequence t1\G
***** 1. row *****
  CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806
  increment by 1 cache 1000 nocycle ENGINE=InnoDB
show create table t1\G
***** 1. row *****
Create Table: CREATE TABLE `t1` (
  `next_not_cached_value` bigint(21) NOT NULL,
  `minimum_value` bigint(21) NOT NULL,
  `maximum_value` bigint(21) NOT NULL,
  `start_value` bigint(21) NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used',
  `increment` bigint(21) NOT NULL COMMENT 'increment value',
  `cache_size` bigint(21) unsigned NOT NULL,
  `cycle_option` tinyint(1) unsigned NOT NULL COMMENT '0 if no cycles are allowed, 1 if the sequence should begin a new cycle when maximum_value is passed',
  `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done'
) ENGINE=InnoDB SEQUENCE=1
select * from t1\G
next_not_cached_value: 1
 minimum_value: 1
 maximum_value: 9223372036854775806
  start_value: 1
  increment: 1
  cache_size: 1000
  cycle_option: 0
  cycle_count: 0

The cycle_count column is incremented every time the sequence wraps around.

크레딧

• AliSQL의 SEQUENCE를 통해 이 작업의 아이디어와 영감을 주신 Aliyun의 Jianwe Zhao에게 감사드립니다.
• 구현에 유용한 의견을 제시해주고 테스트를 도와 준 Peter Gulutzan에게 감사드립니다.

관련 자료

• CREATE SEQUENCE
• ALTER SEQUENCE
• DROP SEQUENCE
• NEXT VALUE FOR
• PREVIOUS VALUE FOR
• SETVAL(). Set next value for the sequence.
• AUTO INCREMENT