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`.

Note

Changing the START value does not affect the generated values until you execute ALTER SEQUENCE ... RESTART.

`SEQUENCE` function

You can control a sequence through the following expression functions:

NEXTVAL or NEXT VALUE FOR
Essentially, both are the NEXTVAL() function that gets the next valid value of a sequence object. The parameter of the NEXTVAL() function is the identifier 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 the identifier 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 is num.

Note

In the implementation of a sequence in TiDB, the SETVAL function cannot change the initial progression or cycle progression of this sequence. This function only returns the next valid value based on this progression.

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

Was this page helpful?

Download PDF Request docs changes Ask questions on Discord