AliSQL provides the Sequence Engine feature. This feature allows you to use a Sequence engine on your ApsaraDB for RDS instance to simplify the generation of sequence values.
Introduction
Unique, monotonically increasing sequence values are commonly required for the primary keys in a single-node persistent database system, for the globally unique identifiers (GUIDs) in a distributed persistent database system, and for the idempotence among multiple persistent database systems. Each database system may have its unique way to ensure unique sequence values. For example, MySQL provides the AUTO_INCREMENT attribute, and Oracle and SQL Server provide the SEQUENCE attribute.
However, in MySQL databases, it is inconvenient to encapsulate unique sequence values such as dates or user names by using the AUTO_INCREMENT attribute. To make the generation of unique sequence values easier, the following alternative solutions are provided:
- Use an application or a proxy to generate sequence values. However, in this case, the states of the sequence values are sent to the application. This makes scaling more complicated.
- Use a simulated table to generate sequence values. However, middleware must be provided to encapsulate and simplify the logic that is used to obtain the generated sequence values.
A Sequence engine is compatible with various database engines and simplifies the generation of sequence values.
A Sequence engine is compatible with various storage engines used with MySQL. However, the underlying persistent data is still stored by using existing storage engines such as InnoDB and MyISAM. This ensures compatibility with third-party tools such as XtraBackup. Therefore, a Sequence engine is merely a logical engine.
A Sequence engine uses Sequence Handler to access sequence objects. This allows you to increase the value of a sequence by using the NEXTVAL operator and manage the cached data. The data is passed to the underlying base table engine to support data access.
Prerequisites
Your RDS instance is running one of the following database engine versions:
- MySQL 5.6
- MySQL 8.0
Limits
- You cannot perform subqueries or JOIN queries in a Sequence engine.
- You can use the
SHOW CREATE TABLE
orSHOW CREATE SEQUENCE
statement to access a sequence. However, you cannot use theSHOW CREATE SEQUENCE
statement to access a regular table. - You cannot specify a Sequence engine during table creation. If you want to specify a Sequence engine for a table, you must execute the statement described in the "Create a sequence" section.
Create a sequence
CREATE SEQUENCE [IF NOT EXISTS] schema.sequence_name
[START WITH <constant>]
[MINVALUE <constant>]
[MAXVALUE <constant>]
[INCREMENT BY <constant>]
[CACHE <constant> | NOCACHE]
[CYCLE | NOCYCLE]
;
The following table describes the parameters that you need to configure.
Parameter | Description |
---|---|
START | The start value of the sequence. |
MINVALUE | The minimum value of the sequence. |
MAXVALUE | The maximum value of the sequence.
Note If the sequence is specified as NOCYCLE, the following error is reported when the maximum value is reached:
|
INCREMENT BY | The increment at which the value of the sequence increases. |
CACHE/NOCACHE | The size of the cache. You can set a larger cache size for better performance. However, if your RDS instance is restarted, sequence values stored in the cache will be lost. |
CYCLE/NOCYCLE | Specifies whether the value of the sequence restarts from the specified MINVALUE after reaching the maximum value. Valid values:
|
Example:
create sequence s
start with 1
minvalue 1
maxvalue 9999999
increment by 1
cache 20
cycle;
If you want to use the mysqldump program to back up your RDS instance, you can create a sequence table and insert an initial row into the sequence table. Example:
CREATE SEQUENCE 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=InnoDB DEFAULT CHARSET=latin1
INSERT INTO schema.sequence_name VALUES(0,0,1,9223372036854775807,1,1,10000,1,0);
COMMIT;
Introduction to sequence tables
Sequences are stored in the tables that are created based on the default storage engine. If you query the sequences that you created, the system returns the tables that are created based on the default storage engine. Example:
SHOW CREATE [TABLE|SEQUENCE] schema.sequence_name;
CREATE SEQUENCE 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=InnoDB DEFAULT CHARSET=latin1
Statements supported
A Sequence engine supports the following statements:
SELECT [nextval | currval | *] FROM seq;
SELECT nextval(seq),currval(seq);
SELECT seq.currval, seq.nextval from dual;