MariaDB starting with 10.3
--------------------------
Sequences were introduced in MariaDB 10.3.

Introduction
------------

A sequence is an object that generates a sequence of numeric values, as
specified by the CREATE SEQUENCE statement.

CREATE SEQUENCE will create a sequence that generates new values when called
with NEXT VALUE FOR sequence_name. It's an alternative to AUTO INCREMENT when
one wants to have more control of how the numbers are generated. As the
SEQUENCE caches values (up to the CACHE value in the CREATE SEQUENCE
statement, by default 1000) it can in some cases be much faster than AUTO
INCREMENT. Another benefit is that one can access the last value generated by
all used sequences, which solves one of the limitations with LAST_INSERT_ID().

Creating a Sequence
-------------------

The CREATE SEQUENCE statement is used to create a sequence. Here is an example
of a sequence starting at 100, incrementing by 10 each time:

CREATE SEQUENCE s START WITH 100 INCREMENT BY 10;

The CREATE SEQUENCE statement, along with defaults, can be viewd with the SHOW
CREATE SEQUENCE STATEMENT, for example:

SHOW CREATE SEQUENCE s\G
*************************** 1. row ***************************
   Table: s
Create Table: CREATE SEQUENCE `s` start with 100 minvalue 1 maxvalue
9223372036854775806 
 increment by 10 cache 1000 nocycle ENGINE=InnoDB

Using Sequence Objects
----------------------

To get the next value from a sequence, use

NEXT VALUE FOR sequence_name

or

NEXTVAL(sequence_name)

or in Oracle mode (SQL_MODE=ORACLE)

sequence_name.nextval

For retrieving the last value used by the current connection from a sequence
use:

PREVIOUS VALUE FOR sequence_name

or

LASTVAL(sequence_name)

or in Oracle mode (SQL_MODE=ORACLE)

sequence_name.currval

For example:

SELECT NEXTVAL(s);
+------------+
| NEXTVAL(s) |
+------------+
|        100 |
+------------+

SELECT NEXTVAL(s);
+------------+
| NEXTVAL(s) |
+------------+
|        110 |
+------------+

SELECT LASTVAL(s);
+------------+
| LASTVAL(s) |
+------------+
|        110 |
+------------+

Using Sequences in DEFAULT
--------------------------

MariaDB starting with 10.3.3
----------------------------
Starting from 10.3.3 you can use Sequences in DEFAULT:

create sequence s1;
create table t1 (a int primary key default (next value for s1), b int);
insert into t1 (b) values (1),(2);
select * from t1;
+---+------+
| a | b    |
+---+------+
| 1 |    1 |
| 2 |    2 |
+---+------+

Changing a Sequence
-------------------

The ALTER SEQUENCE statement is used for changing sequences. For example, to
restart the sequence at another value:

ALTER SEQUENCE s RESTART 50;

SELECT NEXTVAL(s);
+------------+
| NEXTVAL(s) |
+------------+
|         50 |
+------------+

The SETVAL function can also be used to set the next value to be returned for
a SEQUENCE, for example:

SELECT SETVAL(s, 100);
+----------------+
| SETVAL(s, 100) |
+----------------+
|            100 |
+----------------+

SETVAL can only be used to increase the sequence value. Attempting to set a
lower value will fail, returning NULL:

SELECT SETVAL(s, 50);
+---------------+
| SETVAL(s, 50) |
+---------------+
|          NULL |
+---------------+

Dropping a Sequence
-------------------

The DROP SEQUENCE statement is used to drop a sequence, for example:

DROP SEQUENCE s;

Replication
-----------

If one wants to use Sequences in a master-master setup or with Galera one
should use INCREMENT=0. This will tell the Sequence to use
auto_increment_increment and auto_increment_offset to generate unique values
for each server.

Standards Compliance
--------------------

MariaDB 10.3 supports both ANSI SQL and Oracle syntax for sequences.

However as SEQUENCE is implemented as a special kind of table, it uses the
same namespace as tables. The benefits are that sequences show up in SHOW
TABLES, and one can also create a sequence with CREATE TABLE and drop it with
DROP TABLE. One can SELECT from it as from any other table. This ensures that
all old tools that work with tables should work with sequences.

Since sequence objects act as regular tables in many contexts, they will be
affected by LOCK TABLES. This is not the case in other DBMS, such as Oracle,
where LOCK TABLE does not affect sequences.

Notes
-----

One of the goals with the Sequence implementation is that all old tools, such
as mysqldump, should work unchanged, while still keeping the normal usage of
sequence standard compatibly.

To make this possible, sequence is currently implemented as a table with a few
exclusive properties.

The special properties for sequence tables are:

* A sequence table has always one row.
* When one creates a sequence, either with CREATE TABLE or CREATE SEQUENCE,
one row will be inserted.
* If one tries to insert into a sequence table, the single row will be
updated.  This allows mysqldump to work but also gives the additional benefit
that one can change all properties of a sequence with a single insert. New
applications should of course also use ALTER SEQUENCE.
* UPDATE or DELETE can't be performed on Sequence objects.
* Doing a select on the sequence shows the current state of the sequence,
except the values that are reserved in the cache.  The next_value column shows
the next value not reserved by the cache.
* FLUSH TABLES will close the sequence and the next sequence number generated
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/