Sequence Engine

Introduction

Persistent database systems often require a monotonically increasing unique value for use cases such as business primary keys on a single node, a globally unique identifier in a distributed system, or idempotency control across multiple systems. Different database systems provide different implementation methods. For example, MySQL provides the AUTO_INCREMENT attribute, and Oracle and SQL Server provide SEQUENCE objects.

In MySQL databases, using AUTO_INCREMENT can be inconvenient if you want to encapsulate a unique value with additional information, such as a date or user ID. In practice, different workarounds are used:

• Generate sequence values from an application or a proxy. However, maintaining state on the application side complicates scaling out and in.

• Generate sequence values from a simulated table in the database. This method requires middleware to encapsulate and simplify the logic for retrieving unique values.

The Sequence Engine is designed to be as compatible as possible with the methods used by other databases.

The Sequence Engine implements the MySQL storage engine interface, but it uses an existing storage engine, such as InnoDB or MyISAM, to store persistent data. This makes it compatible with third-party tools like XtraBackup. Therefore, the Sequence Engine is a logical engine.

The Sequence Engine accesses Sequence objects through the Sequence Handler interface to manage NEXTVAL advancement and cache management. It then passes requests to the underlying base table's storage engine for final data access.

Prerequisites

Your instance must be one of the following versions:

• RDS for MySQL 8.4

• RDS for MySQL 8.0 with a minor engine version of 20190816 or later

• RDS for MySQL 5.7 with a minor engine version of 20210430 or later

• RDS for MySQL 5.6 with a minor engine version of 20170901 or later

Limitations

• Sequences do not support subqueries or joins.

• You can use SHOW CREATE TABLE to view the structure of a sequence.

• You cannot specify the Sequence engine when creating a table. Sequence tables can only be created using the Create a sequence syntax.

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

The following table describes the parameters.

ERROR HY000: Sequence 'db.seq' has been run out.

Example:

create sequence s
       start with 1
       minvalue 1
       maxvalue 9999999
       increment by 1
       cache 20
       cycle;

To ensure compatibility with backups created by using mysqldump, you can also create a sequence by creating a sequence table and inserting an initial record. Example:

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;

Sequence table definition

A sequence is stored in a table. You can view its definition by running the SHOW CREATE TABLE command. Example:

SHOW CREATE TABLE schema.sequence_name;

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 syntax

Sequences support the following query syntaxes:

• SELECT nextval(<sequence_name>),currval(<sequence_name>) FROM <sequence_name>;

  Note

  Applicable to RDS for MySQL 8.4, 8.0, and 5.7.

• SELECT <sequence_name>.currval, <sequence_name>.nextval FROM dual;

  Note

  Applicable to RDS for MySQL 8.4, 8.0, 5.7, and 5.6.

Example:

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