Sequence engine

Introduction

In persistent databases, the natural key of a single node, the globally unique identifier (GUID) of a distributed system, and the idempotency of a distributed system often require monotonically increasing values that are unique. Different databases provide different solutions. 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 GUIDs such as dates or user information to a database by using the AUTO_INCREMENT attribute. Information such as these uses other solutions:

• Applications or proxies are used to generate the sequence. However, this will bring the states of the sequence to the application side and make scaling more complicated.
• A simulated table is used to generate the sequence. However, the logic to obtain the GUID must be encapsulated and simplified by middleware.

AliSQL provides the sequence engine, which is designed to be compatible with other engines and simplify the procedure of generating a sequence.

The designed interface of the MySQL storage engine is brought out in the sequence engine. However, the underlying persistent data is stored by existing engines such as InnoDB and MyISAM, so that third-party tools such as XtraBackup can be compatible. Therefore, the sequence engine is only a logic engine.

The sequence engine uses Sequence Handler to access the sequence object to increase the value of the sequence by using the NEXTVAL operator and manage the cache. The data is passed to the engine of the base table for data access.

Limits

• You cannot perform subqueries and JOIN queries in the sequence engine.
• You can use the SHOW CREATE TABLE or SHOW CREATE SEQUENCE statement to access the sequence, but you cannot use the SHOW CREATE SEQUENCE statement to access an ordinary table.
• You cannot create a sequence table by setting the engine to the sequence engine. You can only use the statements in the following Create a sequence section to create a sequence table.

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 of creating a sequence.

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;

If you need to use the mysqldump program to back up data, you can create a sequence table and insert an entry. 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;

Sequence table

The sequence engine stores data in the base table. Therefore, the table queried by using the SHOW CREATE statement is the base table. 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

Query statements

 SELECT [nextval | currval | *] FROM seq;
 SELECT nextval(seq),currval(seq);
 SELECT seq.currval, seq.nextval from dual;