The Ultimate Guide to High Availability with MariaDB

CREATE SEQUENCE

You are viewing an old version of this article. View the current version here.
MariaDB starting with 10.3

CREATE SEQUENCE is being introduced in MariaDB 10.3. Details may still change before 10.3 is released as beta.

Syntax

CREATE [OR REPLACE] [TEMPORARY] SEQUENCE [IF NOT EXISTS] sequence_name
[ INCREMENT [ BY | = ] increment ]
[ MINVALUE [=] minvalue | NO MINVALUE | NOMINVALUE ]
[ MAXVALUE [=] maxvalue | NO MAXVALUE | NOMAXVALUE ]
[ START [ WITH | = ] start ] [ CACHE [=] cache | NOCACHE ] [ CYCLE | NOCYCLE] [table_options]

Contents

  1. Syntax
  2. Description
    1. Arguments to create
    2. Constraints on create arguments
  3. Replication
  4. Standards compliance
  5. Notes
  6. Table operations that work with SEQUENCE tables
  7. Implementation
  8. Underlying table structure
  9. Credits
  10. See also

table_options can be any of the normal table options in CREATE TABLE but the most usable ones are ENGINE=... and COMMENT=.

NOMAXVALUE and NOMINVALUE are there to allow one to create SEQUENCE's using the Oracle syntax.

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

Description

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 CACHE) 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().

DROP SEQUENCE can be used to drop a sequence,

Arguments to create

The following options may be used:

OptionDefault value Description
INCREMENT 1Increment to use for values. May be negative
MINVALUE1 if INCREMENT > 0 and -9223372036854775807 if INCREMENT < 0Minimum value for the sequence
MAXVALUE9223372036854775806 if INCREMENT > 0 and -1 if INCREMENT < 0Max value for sequence
STARTMINVALUE if INCREMENT > 0 and MAX_VALUE if INCREMENT< 0First value that the sequence will generate
CACHE1000 Number of values that should be cached. 0 if no CACHE. The underlying table will be updated first time a new sequence number is generated and each time the cache runs out.

If CYCLE is used then the sequence should start again from MINVALUE after it has run out of values. Default value is NOCYCLE.

Constraints on create arguments

To be able to create a legal sequence, the following must hold:

  • MAXVALUE >= start
  • MAXVALUE > MINVALUE
  • START >= MINVALUE
  • MAXVALUE <= 9223372036854775806 (LONGLONG_MAX-1)
  • MINVALUE >= -9223372036854775807 (LONGLONG_MIN+1)

Note that sequences can't generate the maximum/minimum 64 bit number because of the constraint of MINVALUE and MAXVALUE.

Replication

If one wants to use SEQUENCE 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, like 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 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

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 `t1` (
  `next_value` bigint(21) NOT NULL COMMENT 'next not cached value',
  `min_value` bigint(21) NOT NULL COMMENT 'min value',
  `max_value` bigint(21) NOT NULL COMMENT 'max value',
  `start` bigint(21) NOT NULL COMMENT 'start value',
  `increment` bigint(21) NOT NULL COMMENT 'increment value',
  `cache` bigint(21) NOT NULL COMMENT 'cache size',
  `cycle` tinyint(1) unsigned NOT NULL COMMENT 'cycle state',
  `round` bigint(21) NOT NULL COMMENT 'How many cycles has been done'
) ENGINE=InnoDB SEQUENCE=1


MariaDB [test]> select * from t1\G


next_value: 1
 min_value: 1
 max_value: 9223372036854775806
     start: 1
 increment: 1
     cache: 1000
     cycle: 0
     round: 0

The round 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.

See also

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.