Sequence Engine

How it works

The Sequence Engine is a logical engine layered on top of existing MySQL storage engines (InnoDB, MyISAM). It stores sequence state in an underlying base table handled by your default storage engine, which keeps it compatible with third-party tools such as XtraBackup and mysqldump.

Sequence Handler mediates all access to sequence objects. When you call NEXTVAL, the handler advances the sequence, updates the cached values, and returns the next integer. The cached block is managed in memory; on instance restart, any cached but unused values are discarded.

Prerequisites

Before you begin, ensure that you have:

• A PolarDB-X Standard Edition instance running MySQL 8.0

Limitations

• The Sequence Engine does not support subqueries or JOIN queries.

• Use SHOW CREATE TABLE to inspect a sequence definition. You cannot specify ENGINE=Sequence in a CREATE TABLE statement; create sequences with the CREATE SEQUENCE statement instead.

Create a sequence

CREATE SEQUENCE [IF NOT EXISTS] <database_name>.<sequence_name>
  [START WITH <constant>]
  [MINVALUE <constant>]
  [MAXVALUE <constant>]
  [INCREMENT BY <constant>]
  [CACHE <constant> | NOCACHE]
  [CYCLE | NOCYCLE];

CREATE SEQUENCE s
  START WITH 1
  MINVALUE 1
  MAXVALUE 9999999
  INCREMENT BY 1
  CACHE 20
  CYCLE;

CREATE TABLE schema.sequence_name (
  `currval`   bigint(21) NOT NULL COMMENT 'current value',
  `nextval`   bigint(21) NOT NULL COMMENT 'next value',
  `minvalue`  bigint(21) NOT NULL COMMENT 'min value',
  `maxvalue`  bigint(21) NOT NULL COMMENT 'max value',
  `start`     bigint(21) NOT NULL COMMENT 'start value',
  `increment` bigint(21) NOT NULL COMMENT 'increment value',
  `cache`     bigint(21) NOT NULL COMMENT 'cache size',
  `cycle`     bigint(21) NOT NULL COMMENT 'cycle state',
  `round`     bigint(21) NOT NULL COMMENT 'already how many round'
) ENGINE=Sequence DEFAULT CHARSET=latin1;

INSERT INTO schema.sequence_name VALUES(0,0,1,9223372036854775807,1,1,10000,1,0);
COMMIT;

Inspect a sequence

Sequences are backed by a table in the default storage engine. Use SHOW CREATE TABLE to see the full column structure:

SHOW CREATE TABLE schema.sequence_name;

Output:

CREATE TABLE schema.sequence_name (
  `currval`   bigint(21) NOT NULL COMMENT 'current value',
  `nextval`   bigint(21) NOT NULL COMMENT 'next value',
  `minvalue`  bigint(21) NOT NULL COMMENT 'min value',
  `maxvalue`  bigint(21) NOT NULL COMMENT 'max value',
  `start`     bigint(21) NOT NULL COMMENT 'start value',
  `increment` bigint(21) NOT NULL COMMENT 'increment value',
  `cache`     bigint(21) NOT NULL COMMENT 'cache size',
  `cycle`     bigint(21) NOT NULL COMMENT 'cycle state',
  `round`     bigint(21) NOT NULL COMMENT 'already how many round'
) ENGINE=Sequence DEFAULT CHARSET=latin1

Query sequence values

Two equivalent syntaxes are supported:

-- Syntax 1: functional form
SELECT nextval(<sequence_name>), currval(<sequence_name>) FROM <sequence_name>;

-- Syntax 2: dot notation
SELECT <sequence_name>.currval, <sequence_name>.nextval FROM dual;

NEXTVAL advances the sequence and returns the new value.

CURRVAL returns the last value returned by NEXTVAL in the current session. Call NEXTVAL at least once before calling CURRVAL; otherwise the following error is returned:

Sequence 'xxx' is not yet defined in current session

For a new sequence, call NEXTVAL first:

SELECT <sequence_name>.nextval FROM dual;

Then query both values:

mysql> SELECT test.currval, test.nextval FROM dual;
+--------------+--------------+
| test.currval | test.nextval |
+--------------+--------------+
|           24 |           25 |
+--------------+--------------+
1 row in set (0.03 sec)

Cache behavior and gaps

When CACHE is set to a value greater than 1, each session pre-allocates a block of sequence values. This reduces contention on the base table. If your instance is restarted, the sequence values that are stored in the cache are lost.