CREATE SEQUENCE
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]
The options for CREATE SEQUENCE
can be given in any order, optionally followed by table_options
.
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.
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:
Option | Default value | Description |
---|---|---|
INCREMENT | 1 | Increment to use for values. May be negative |
MINVALUE | 1 if INCREMENT > 0 and -9223372036854775807 if INCREMENT < 0 | Minimum value for the sequence |
MAXVALUE | 9223372036854775806 if INCREMENT > 0 and -1 if INCREMENT < 0 | Max value for sequence |
START | MINVALUE if INCREMENT > 0 and MAX_VALUE if INCREMENT< 0 | First value that the sequence will generate |
CACHE | 1000 | 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
.
See also
- Sequence Overview
- ALTER SEQUENCE
- DROP SEQUENCE
- NEXT VALUE FOR
- PREVIOUS VALUE FOR
- SETVAL(). Set next value for the sequence.
- AUTO INCREMENT