ApsaraDB RDS:Sequence Engine

How it works

The Sequence Engine is a logical engine layered on top of an existing storage engine (InnoDB or MyISAM). It does not store data itself; instead, it delegates persistence to the underlying base table while using Sequence Handler to manage sequence state and caching.

• Call NEXTVAL to advance the sequence counter and retrieve the next value.

• Call CURRVAL to read the last value returned to the current session without advancing the counter.

Because sequences are backed by standard storage engines, they are compatible with third-party tools such as XtraBackup and mysqldump.

Supported versions

The Sequence Engine requires one of the following minor engine versions:

RDS Enterprise Edition is not supported.

Limitations

• Subqueries and JOIN queries are not supported on sequences.

• To create a sequence, use the CREATE SEQUENCE statement. You cannot specify ENGINE=Sequence in a CREATE TABLE statement.

• Inspect a sequence's definition with SHOW CREATE TABLE.

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];

When you execute the preceding statement, you must configure the parameters that are enclosed in brackets ([]).

The following table describes each parameter.

Example: Create a cycling sequence that starts at 1 and wraps at 9,999,999.

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;

Query a sequence

Two syntax forms are supported. Use the one that matches your MySQL version.

The FROM dual form mirrors Oracle's sequence syntax (sequence_name.nextval) and is the only option on MySQL 5.6.

Example: Retrieve the current and next values for a sequence named test.

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

Example: Call NEXTVAL twice to observe the increment.

mysql> SELECT test.nextval FROM dual;
+--------------+
| test.nextval |
+--------------+
|           25 |
+--------------+

mysql> SELECT test.nextval FROM dual;
+--------------+
| test.nextval |
+--------------+
|           26 |
+--------------+

Before querying a new sequence for the first time in a session, call NEXTVAL to initialize it: Skipping this step returns the error: Sequence 'xxx' is not yet defined in current session.

SELECT <sequence_name>.nextval FROM dual;

Sequence table structure

Sequences are stored in the underlying base table engine. Run SHOW CREATE TABLE to inspect a sequence's current definition and column layout:

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

Gaps and data loss

Instance restarts discard any values held in cache. Do not use sequences for business logic that requires a contiguous, unbroken series of numbers (such as regulatory invoice numbers).

Use sequences for surrogate primary keys or globally unique identifiers (GUIDs) in distributed systems, where gaps are acceptable.