AliSQL provides the Sequence Engine feature. This feature allows you to use a Sequence engine on your ApsaraDB RDS instance to simplify the generation of sequence values.
Unique sequence values that monotonically increase 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 engine 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 statuses 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 that are 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, from which the data can be accessed.
Your RDS instance runs one of the following database engine versions:
- MySQL 8.0
- MySQL 5.7
- MySQL 5.6
- You cannot perform subqueries or JOIN queries in a Sequence engine.
- You can use the
SHOW CREATE TABLEor
SHOW CREATE SEQUENCEstatement to access a sequence. However, you cannot use the
SHOW CREATE SEQUENCEstatement 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 that is described in the "Create a sequence" section of this topic.
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.
|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, the sequence values stored in the cache are lost.|
|CYCLE/NOCYCLE||Specifies whether the value of the sequence restarts from the specified MINVALUE after the maximum value is reached. Valid values:
create sequence s start with 1 minvalue 1 maxvalue 9999999 increment by 1 cache 20 cycle;
If you want to use the mysqldump plug-in to back up your RDS instance, you can create a sequence table and insert an initial row into the sequence table. 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;
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 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
A Sequence engine supports the following statements:
SELECT [nextval | currval | *] FROM seq; SELECT nextval(seq),currval(seq); SELECT seq.currval, seq.nextval from dual;