CREATE SEQUENCE
Syntax
CREATE [OR REPLACE] [TEMPORARY] SEQUENCE [IF NOT EXISTS] sequence_name [AS { TINYINT | SMALLINT | |MEDIUMINT | INT | INTEGER | BIGINT } [SIGNED | UNSIGNED]] [ INCREMENT [ BY | = ] number ] [ MINVALUE [=] number | NO MINVALUE | NOMINVALUE ] [ MAXVALUE [=] number | NO MAXVALUE | NOMAXVALUE ] [ START [ WITH | = ] number ] [ CACHE [=] number | NOCACHE ] [ CYCLE | NOCYCLE] [table_options]
Contents
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 SEQUENCEs 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().
CREATE SEQUENCE requires the CREATE privilege.
DROP SEQUENCE can be used to drop a sequence, and ALTER SEQUENCE to change it.
CREATE arguments
The following options may be used:
AS
int type, i.e. one of TINYINT, SMALLINT, MEDIUMINT, INT, INTEGER, BIGINT. Can be signed or unsigned. Maximum value is based on the data type. The use of BIGINT UNSIGNED with this option extends the possible maximum value from 9223372036854775806 to 18446744073709551614. Default BIGINT. From MariaDB 11.5.
INCREMENT
Increment to use for values. May be negative. Setting an increment of 0 causes the sequence to use the value of the auto_increment_increment system variable at the time of creation, which is always a positive number. (see MDEV-16035). Default 1.
MINVALUE
Minimum value for the sequence. From MariaDB 11.5, parser permits much smaller numbers, such as -9999999999999999999999999999, but converts to the minimum permitted for the int type, with a note. Default 1 if INCREMENT > 0 and -9223372036854775807 (or based on int type) if INCREMENT < 0.
MAXVALUE
Max value for sequence. From MariaDB 11.5, parser permits much larger numbers, such as 9999999999999999999999999999 used in Oracle examples, but converts to the maximum permitted for the int type, with a note. Default 9223372036854775806 (or based on int type) if INCREMENT > 0 and -1 if INCREMENT < 0. |
START
First value that the sequence will generate. Default MINVALUE if INCREMENT > 0, and MAX_VALUE if INCREMENT< 0.
CACHE / NOCACHE
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. Default 1000. FLUSH TABLES, shutting down the server, etc. will discard the cached values, 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. Note that setting the cache to 1 from 1000, can make inserts to tables using sequences for default values 2x slower and increase the binary log sizes up to 7x.
CYCLE / NOCYCLE
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). From MariaDB 11.5, the parser accepts values beyond this, and converts based on the int type.
- MINVALUE >= -9223372036854775807 (LONGLONG_MIN+1). From MariaDB 11.5, the parser accepts values beyond this, and converts based on the int type.
Note that sequences can't generate the maximum/minimum 64 bit number because of the constraint of
MINVALUE
and MAXVALUE
.
Atomic DDL
MariaDB starting with 10.6.1
MariaDB 10.6.1 supports Atomic DDL and CREATE SEQUENCE
is atomic.
Examples
CREATE SEQUENCE s START WITH 100 INCREMENT BY 10; CREATE SEQUENCE s2 START WITH -100 INCREMENT BY -10;
The following statement fails, as the increment conflicts with the defaults
CREATE SEQUENCE s3 START WITH -100 INCREMENT BY 10; ERROR 4082 (HY000): Sequence 'test.s3' values are conflicting
The sequence can be created by specifying workable minimum and maximum values:
CREATE SEQUENCE s3 START WITH -100 INCREMENT BY 10 MINVALUE=-100 MAXVALUE=1000;
From MariaDB 11.5:
CREATE SEQUENCE s3 AS BIGINT UNSIGNED START WITH 10;
Parser accepting larger or smaller values:
CREATE OR REPLACE SEQUENCE s AS TINYINT SIGNED MINVALUE=-999999999999999999999999999999999 MAXVALUE=999999999999999999999999999999999 START WITH 100 INCREMENT BY 10; Query OK, 0 rows affected, 2 warnings (0.037 sec) SHOW WARNINGS; +-------+------+-----------------------------------------------+ | Level | Code | Message | +-------+------+-----------------------------------------------+ | Note | 1292 | Truncated incorrect INTEGER value: 'MINVALUE' | | Note | 1292 | Truncated incorrect INTEGER value: 'MAXVALUE' | +-------+------+-----------------------------------------------+ SELECT * FROM INFORMATION_SCHEMA.SEQUENCES\G *************************** 1. row *************************** SEQUENCE_CATALOG: def SEQUENCE_SCHEMA: test SEQUENCE_NAME: s DATA_TYPE: tinyint NUMERIC_PRECISION: 8 NUMERIC_PRECISION_RADIX: 2 NUMERIC_SCALE: 0 START_VALUE: 100 MINIMUM_VALUE: -127 MAXIMUM_VALUE: 126 INCREMENT: 10 CYCLE_OPTION: 0
Cache
Flushing the cache:
CREATE OR REPLACE SEQUENCE s START WITH 1 INCREMENT BY 1 MAXVALUE=10 CACHE=5; SELECT NEXTVAL(s); +------------+ | NEXTVAL(s) | +------------+ | 1 | +------------+ SELECT NEXTVAL(s); +------------+ | NEXTVAL(s) | +------------+ | 2 | +------------+ FLUSH TABLES s; SELECT NEXTVAL(s); +------------+ | NEXTVAL(s) | +------------+ | 6 | +------------+ FLUSH TABLES s; SELECT NEXTVAL(s); ERROR 4084 (HY000): Sequence 'test.s' has run out
Create table with a sequence as a default value
One can use sequences instead of AUTO_INCREMENT
to generate values for a table
create sequence s1; create table t1 (a int primary key default nextval(s1), b int); insert into t1 (b) values(1); select * from t1; +---+------+ | a | b | +---+------+ | 1 | 1 | +---+------+
See Also
- Sequence Overview
- ALTER SEQUENCE
- DROP SEQUENCE
- NEXT VALUE FOR
- PREVIOUS VALUE FOR
- SETVAL(). Set next value for the sequence.
- AUTO INCREMENT
- SHOW CREATE SEQUENCE
- Information Schema SEQUENCES Table