ALTER SEQUENCE
The ALTER SEQUENCE statement alters 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
- TableName
- CreateSequenceOptionListOpt
- SequenceOptionList
- SequenceOption
CreateSequenceStmt ::=
'ALTER' 'SEQUENCE' TableName CreateSequenceOptionListOpt
TableName ::=
Identifier ('.' Identifier)?
CreateSequenceOptionListOpt ::=
SequenceOption*
SequenceOptionList ::=
SequenceOption
SequenceOption ::=
( 'INCREMENT' ( '='? | 'BY' ) | 'START' ( '='? | 'WITH' ) | ( 'MINVALUE' | 'MAXVALUE' | 'CACHE' ) '='? ) SignedNum
| 'COMMENT' '='? stringLit
| 'NOMINVALUE'
| 'NO' ( 'MINVALUE' | 'MAXVALUE' | 'CACHE' | 'CYCLE' )
| 'NOMAXVALUE'
| 'NOCACHE'
| 'CYCLE'
| 'NOCYCLE'
| 'RESTART' ( ( '='? | 'WITH' ) SignedNum )?
Syntax
ALTER SEQUENCE 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 |
|---|---|---|
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:
NEXTVALorNEXT VALUE FOREssentially, both are the
NEXTVAL()function that gets the next valid value of a sequence object. The parameter of theNEXTVAL()function is theidentifierof the sequence.LASTVALThis function gets the last used value of this session. If the value does not exist,
NULLis used. The parameter of this function is theidentifierof the sequence.SETVALThis function sets the progression of the current value for a sequence. The first parameter of this function is the
identifierof the sequence; the second parameter isnum.
Examples
Create a sequence named s1:
CREATE SEQUENCE s1;
Query OK, 0 rows affected (0.15 sec)
Get the next two values from the sequence by executing the following SQL statement twice:
SELECT NEXTVAL(s1);
+-------------+
| NEXTVAL(s1) |
+-------------+
| 1 |
+-------------+
1 row in set (0.01 sec)
SELECT NEXTVAL(s1);
+-------------+
| NEXTVAL(s1) |
+-------------+
| 2 |
+-------------+
1 row in set (0.00 sec)
Change the increment of the sequence to 2:
ALTER SEQUENCE s1 INCREMENT=2;
Query OK, 0 rows affected (0.18 sec)
Now, get the next two values from the sequence again:
SELECT NEXTVAL(s1);
+-------------+
| NEXTVAL(s1) |
+-------------+
| 1001 |
+-------------+
1 row in set (0.02 sec)
SELECT NEXTVAL(s1);
+-------------+
| NEXTVAL(s1) |
+-------------+
| 1003 |
+-------------+
1 row in set (0.00 sec)
As you can see from the output, the values now increase by two, following the ALTER SEQUENCE statement.
You can also change other parameters of the sequence. For example, you can change the MAXVALUE of the sequence as follows:
CREATE SEQUENCE s2 MAXVALUE=10;
Query OK, 0 rows affected (0.17 sec)
ALTER SEQUENCE s2 MAXVALUE=100;
Query OK, 0 rows affected (0.15 sec)
SHOW CREATE SEQUENCE s2\G
*************************** 1. row ***************************
Sequence: s2
Create Sequence: CREATE SEQUENCE `s2` start with 1 minvalue 1 maxvalue 100 increment by 1 cache 1000 nocycle ENGINE=InnoDB
1 row in set (0.00 sec)
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).