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
 
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/