
will be according to what's stored in the Sequence object. In effect, this
will discard the cached values.
* A number of normal table operations work on Sequence tables. See next
section.

Table Operations that Work with Sequences
-----------------------------------------

* SHOW CREATE TABLE sequence_name. This shows the table structure that is
behind the SEQUENCE including the field names that can be used with SELECT or
even CREATE TABLE.
* CREATE TABLE sequence-structure ... SEQUENCE=1
* ALTER TABLE sequence RENAME TO sequence2
* RENAME TABLE sequence_name TO new_sequence_name
* DROP TABLE sequence_name. This is allowed mainly to get old tools like
mysqldump to work with sequence tables.
* SHOW TABLES

Implementation
--------------

Internally, sequence tables are created as a normal table without rollback
(the InnoDB, Aria and MySAM engines support this), wrapped by a sequence
engine object. This allowed us to create sequences with almost no performance
impact for normal tables. (The cost is one 'if' per insert if the binary log
is enabled).

Underlying Table Structure
--------------------------

The following example shows the table structure of sequences and how it can be
used as a table. (Output of results are slightly edited to make them easier to
read)

create sequence t1;
show create sequence t1\G
*************************** 1. row ***************************
 CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806
 increment by 1 cache 1000 nocycle ENGINE=InnoDB

show create table t1\G
*************************** 1. row ***************************
Create Table: CREATE TABLE `t1` (
 `next_not_cached_value` bigint(21) NOT NULL,
 `minimum_value` bigint(21) NOT NULL,
 `maximum_value` bigint(21) NOT NULL,
 `start_value` bigint(21) NOT NULL COMMENT 'start value when sequences is
created or value if RESTART is used',
 `increment` bigint(21) NOT NULL COMMENT 'increment value',
 `cache_size` bigint(21) unsigned NOT NULL,
 `cycle_option` tinyint(1) unsigned NOT NULL COMMENT '0 if no cycles are
allowed, 1 if the sequence should begin a new cycle when maximum_value is
passed',
 `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done'
) ENGINE=InnoDB SEQUENCE=1

select * from t1\G
next_not_cached_value: 1
 minimum_value: 1
 maximum_value: 9223372036854775806
 start_value: 1
 increment: 1
 cache_size: 1000
 cycle_option: 0
 cycle_count: 0

The cycle_count column is incremented every time the sequence wraps around.

Credits
-------

* Thanks to Jianwe Zhao from Aliyun for his work on SEQUENCE in AliSQL, which
gave ideas and inspiration for this work.
* Thanks to Peter Gulutzan,who helped test and gave useful comments about the
implementation.

URL: https://mariadb.com/kb/en/sequence-overview/