CREATE SEQUENCE
The CREATE SEQUENCE
statement creates sequence objects in TiDB. The sequence is a database object that is on a par with the table and the View
object. The sequence is used to generate serialized IDs in a customized way.
Synopsis
- CreateSequenceStmt
- IfNotExists
- TableName
- CreateSequenceOptionListOpt
- SequenceOptionList
- SequenceOption
CreateSequenceStmt ::=
'CREATE' 'SEQUENCE' IfNotExists TableName CreateSequenceOptionListOpt CreateTableOptionListOpt
IfNotExists ::=
('IF' 'NOT' 'EXISTS')?
TableName ::=
Identifier ('.' Identifier)?
CreateSequenceOptionListOpt ::=
SequenceOption*
SequenceOptionList ::=
SequenceOption
SequenceOption ::=
( 'INCREMENT' ( '='? | 'BY' ) | 'START' ( '='? | 'WITH' ) | ( 'MINVALUE' | 'MAXVALUE' | 'CACHE' ) '='? ) SignedNum
| 'NOMINVALUE'
| 'NO' ( 'MINVALUE' | 'MAXVALUE' | 'CACHE' | 'CYCLE' )
| 'NOMAXVALUE'
| 'NOCACHE'
| 'CYCLE'
| 'NOCYCLE'
Syntax
CREATE [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 | NO CACHE]
[ CYCLE | NOCYCLE | NO CYCLE]
[table_options]
Parameters
Parameters | Default value | Description |
---|---|---|
TEMPORARY | false | TiDB currently does not support the TEMPORARY option and provides only syntax compatibility for it. |
INCREMENT | 1 | Specifies the increment of a sequence. Its positive or negative values can control the growth direction of the sequence. |
MINVALUE | 1 or -9223372036854775807 | Specifies the minimum value of a sequence. When INCREMENT > 0 , the default value is 1 . When INCREMENT < 0 , the default value is -9223372036854775807 . |
MAXVALUE | 9223372036854775806 or -1 | Specifies the maximum value of a sequence. When INCREMENT > 0 , the default value is 9223372036854775806 . When INCREMENT < 0 , the default value is -1 . |
START | MINVALUE or MAXVALUE | Specifies the initial value of a sequence. When INCREMENT > 0 , the default value is MINVALUE . When INCREMENT < 0 , the default value is MAXVALUE . |
CACHE | 1000 | Specifies the local cache size of a sequence in TiDB. |
CYCLE | NO CYCLE | Specifies whether a sequence restarts from the minimum value (or maximum for the descending sequence). When INCREMENT > 0 , the default value is MINVALUE . When INCREMENT < 0 , the default value is MAXVALUE . |
SEQUENCE
function
You can control a sequence through the following expression functions:
NEXTVAL
orNEXT VALUE FOR
Essentially, both are the
nextval()
function that gets the next valid value of a sequence object. The parameter of thenextval()
function is theidentifier
of the sequence.LASTVAL
This function gets the last used value of this session. If the value does not exist,
NULL
is used. The parameter of this function is theidentifier
of the sequence.SETVAL
This function sets the progression of the current value for a sequence. The first parameter of this function is the
identifier
of the sequence; the second parameter isnum
.
Examples
Create a sequence object with the default parameter:
CREATE SEQUENCE seq;Query OK, 0 rows affected (0.06 sec)Use the
nextval()
function to get the next value of the sequence object:SELECT nextval(seq);+--------------+ | nextval(seq) | +--------------+ | 1 | +--------------+ 1 row in set (0.02 sec)Use the
lastval()
function to get the value generated by the last call to a sequence object in this session:SELECT lastval(seq);+--------------+ | lastval(seq) | +--------------+ | 1 | +--------------+ 1 row in set (0.02 sec)Use the
setval()
function to set the current value (or the current position) of the sequence object:SELECT setval(seq, 10);+-----------------+ | setval(seq, 10) | +-----------------+ | 10 | +-----------------+ 1 row in set (0.01 sec)You can also use the
next value for
syntax to get the next value of the sequence:SELECT next value for seq;+--------------------+ | next value for seq | +--------------------+ | 11 | +--------------------+ 1 row in set (0.00 sec)Create a sequence object with default custom parameters:
CREATE SEQUENCE seq2 start 3 increment 2 minvalue 1 maxvalue 10 cache 3;Query OK, 0 rows affected (0.01 sec)When the sequence object has not been used in this session, the
lastval()
function returns aNULL
value.SELECT lastval(seq2);+---------------+ | lastval(seq2) | +---------------+ | NULL | +---------------+ 1 row in set (0.01 sec)The first valid value of the
nextval()
function for the sequence object is the value ofSTART
parameter.SELECT nextval(seq2);+---------------+ | nextval(seq2) | +---------------+ | 3 | +---------------+ 1 row in set (0.00 sec)Although the
setval()
function can change the current value of the sequence object, it cannot change the arithmetic progression rule for the next value.SELECT setval(seq2, 6);+-----------------+ | setval(seq2, 6) | +-----------------+ | 6 | +-----------------+ 1 row in set (0.00 sec)When you use
nextval()
to get the next value, the next value will follow the arithmetic progression rule defined by the sequence.SELECT next value for seq2;+---------------------+ | next value for seq2 | +---------------------+ | 7 | +---------------------+ 1 row in set (0.00 sec)You can use the next value of the sequence as the default value for the column, as in the following example.
CREATE table t(a int default next value for seq2);Query OK, 0 rows affected (0.02 sec)In the following example, the value is not specified, so the default value of
seq2
is used.INSERT into t values();Query OK, 1 row affected (0.00 sec)SELECT * from t;+------+ | a | +------+ | 9 | +------+ 1 row in set (0.00 sec)In the following example, the value is not specified, so the default value of
seq2
is used. But the next value ofseq2
is not within the range defined in the above example (CREATE SEQUENCE seq2 start 3 increment 2 minvalue 1 maxvalue 10 cache 3;
), so an error is returned.INSERT into t values();ERROR 4135 (HY000): Sequence 'test.seq2' has run out
MySQL compatibility
This statement is a TiDB extension. The implementation is modeled on sequences available in MariaDB.
Except for the SETVAL
function, all other functions have the same progressions as MariaDB. Here "progression" means that the numbers in a sequence follow a certain arithmetic progression rule defined by the sequence. Although you can use SETVAL
to set the current value of a sequence, the subsequent values of the sequence still follow the original progression rule.
For example:
1, 3, 5, ... // The sequence starts from 1 and increments by 2.
select setval(seq, 6) // Sets the current value of a sequence to 6.
7, 9, 11, ... // Subsequent values still follow the progression rule.
In the CYCLE
mode, the initial value of a sequence in the first round is the value of the START
parameter, and the initial value in the subsequent rounds is the value of MinValue
(INCREMENT
> 0) or MaxValue
(INCREMENT
< 0).