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().
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
To get the next value from a sequence, use
NEXT VALUE FOR sequence_name
or in Oracle mode (SQL_MODE=ORACLE)
For retrieving the last value used by the current connection from a sequence use:
PREVIOUS VALUE FOR sequence_name
or in Oracle mode (SQL_MODE=ORACLE)
SELECT NEXTVAL(s); +------------+ | NEXTVAL(s) | +------------+ | 100 | +------------+ SELECT NEXTVAL(s); +------------+ | NEXTVAL(s) | +------------+ | 110 | +------------+ SELECT LASTVAL(s); +------------+ | LASTVAL(s) | +------------+ | 110 | +------------+
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 | +---+------+
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 | +---------------+
The DROP SEQUENCE statement is used to drop a sequence, for example:
DROP SEQUENCE s;
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.
MariaDB 10.3 supports both ANSI SQL and Oracle syntax for sequences.
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.
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:
next_valuecolumn shows the next value not reserved by the cache.
SEQUENCEincluding the field names that can be used with SELECT or even CREATE TABLE.
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).
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
cycle_count column is incremented every time the sequence wraps around.
© 2019 MariaDB
Licensed under the Creative Commons Attribution 3.0 Unported License and the GNU Free Documentation License.