Docs

RDS for MySQL

Feature

Sequence Engine

This topic describes the Sequence Engine provided by RDS for MySQL.

Background

RDS for MySQL provides Sequence Engine to obtain unique and auto-incrementing sequence values. Sequence Engine is a logical engine that still uses the InnoDB storage engine for data persistence and is compatible with existing data backup and recovery tools. Through encapsulation, the upper layer realizes the functions of sequence value generation, caching, and so on.

Supported version

MySQL 5.7 version, and the kernel minor version is MySQL 5.7.32_20241215 and above.

MySQL 8.0 version, and the kernel minor version is MySQL 8.0.32_20250215 and above.

MySQL 8.4 version, and the kernel minor version is MySQL 8.4.7_20260415 and above.

tip

You can view the kernel minor version of an instance by Viewing instance information. If the kernel minor version of your instance is not within the supported kernel minor version range, you can Manually upgrading the instance kernel minor version.

Prerequisites

An instance is created and in the Running state. For details, see Creating an instance.

Creating a Sequence

Use the CREATE SEQUENCE statement to create a Sequence.

The parameters in the statement are described as follows:

Parameter	Description	Default value
START WITH	The starting value of the sequence. tip `START WITH` must be greater than or equal to `MINVALUE` and less than `MAXVALUE`.	1
MINVALUE	The minimum value of the Sequence. tip MINVALUE must be less than or equal to MAXVALUE.	1
MAXVALUE	The maximum value of the Sequence. tip MAXVALUE must be greater than or equal to MINVALUE.	9223372036854775807
INCREMENT BY	The increment value (compensation) for the sequence. tip `INCREMENT BY` must be greater than or equal to 1.	1
CACHE \| NOCACHE	The size of the Sequence cache. It improves performance by avoiding disk data updates every time nextval is obtained. Data in the cache is lost after a restart, and `nextval` jumps to the value of the next cache.	10000
CYCLE \| NOCYCLE	Specifies whether the sequence can restart from `MINVALUE` after reaching its limit.	0

tip

The preceding parameters cannot be negative.

For compatibility with the MySQL Dump backup method, you can also create a Sequence by using Engine=Sequence. Specify the Sequence parameters by inserting an initial data row. The parameters are based on the first record. Users can download, back up, and restore a Sequence created with Engine=Sequence using tools such as MySQL Dump. Example as follows:

Deleting a Sequence

You can delete a Sequence using the DROP SEQUENCE and DROP TABLE statements.

Using the DROP SEQUENCE statement

DROP SEQUENCE <数据库名>.<Sequence名称>;

Using the DROP TABLE statement

DROP TABLE <数据库名>.<Sequence名称>;

Obtaining nextval

You can obtain nextval by using the nextval() function or accessing the nextval column in the Sequence table.

Using the nextval() function

SELECT nextval(<数据库名>.<Sequence名称>);

Accessing the nextval column in the Sequence table

SELECT <数据库名>.<Sequence名称>.nextval from dual;

warning

SELECT nextval from <Database name>.<Sequence name>; queries records in the Sequence table instead of obtaining the next value of the Sequence.

Obtaining currval

You can obtain currval (the value of the last nextval obtained in the current session) by using the `currval()` function or by accessing the `currval` column in the Sequence table.

Using the `currval()` function

SELECT currval(<数据库名>.<Sequence名称>);

Accessing the `currval` column in the Sequence table

SELECT <数据库名>.<Sequence名称>.currval from dual;

warning

SELECT currval from <Database name>.<Sequence name>; queries records in the Sequence table instead of obtaining the current value of the Sequence.

For a newly created Sequence, you must call its NEXTVAL once before querying it. Otherwise, a `Sequence 'xxx' is not yet defined in current session` error occurs.

If the connection pool mode for the proxy endpoint is a transaction-level connection pool, the obtained currval value may be incorrect. To obtain an accurate currval value, use a proxy endpoint in direct connection mode or disable the database proxy for the instance.

View Sequence

You can view the specific properties of a Sequence by using the SHOW statement.

Last updated: 2026.05.28 15:17:48