Sequence Overview
MariaDB starting with 10.3
Sequences are being introduced in MariaDB 10.3. Details may still change before 10.3 is released as beta.
Contents
Using sequence objects
To get the next value from a sequence, use
NEXT VALUE FOR sequence
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
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.
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 #DELETEcan't be done 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 column
next_value
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 lot of normal table operations work on Sequence tables. See next section.
Table operations that work with Sequence tables
- 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
MariaDB [test]> 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.