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

ALTER SEQUENCE - 图1

TableName

ALTER SEQUENCE - 图2

CreateSequenceOptionListOpt

ALTER SEQUENCE - 图3

SequenceOptionList

ALTER SEQUENCE - 图4

SequenceOption

ALTER SEQUENCE - 图5

  1. CreateSequenceStmt ::=
  2.     'ALTER' 'SEQUENCE' TableName CreateSequenceOptionListOpt
  4. TableName ::=
  5.     Identifier ('.' Identifier)?
  7. CreateSequenceOptionListOpt ::=
  8.     SequenceOption*
  10. SequenceOptionList ::=
  11.     SequenceOption
  13. SequenceOption ::=
  14.     ( 'INCREMENT' ( '='? | 'BY' ) | 'START' ( '='? | 'WITH' ) | ( 'MINVALUE' | 'MAXVALUE' | 'CACHE' ) '='? ) SignedNum
  15. |   'COMMENT' '='? stringLit
  16. |   'NOMINVALUE'
  17. |   'NO' ( 'MINVALUE' | 'MAXVALUE' | 'CACHE' | 'CYCLE' )
  18. |   'NOMAXVALUE'
  19. |   'NOCACHE'
  20. |   'CYCLE'
  21. |   'NOCYCLE'
  22. |   'RESTART' ( ( '='? | 'WITH' ) SignedNum )?

Syntax

  1. ALTER SEQUENCE sequence_name
  2.     [ INCREMENT [ BY | = ] increment ]
  3.     [ MINVALUE [=] minvalue | NO MINVALUE | NOMINVALUE ]
  4.     [ MAXVALUE [=] maxvalue | NO MAXVALUE | NOMAXVALUE ]
  5.     [ START [ WITH | = ] start ]
  6.     [ CACHE [=] cache | NOCACHE | NO CACHE]
  7.     [ CYCLE | NOCYCLE | NO CYCLE]
  8.     [table_options]

Parameters

ParametersDefault valueDescription
INCREMENT1Specifies the increment of a sequence. Its positive or negative values can control the growth direction of the sequence.
MINVALUE1 or -9223372036854775807Specifies the minimum value of a sequence. When INCREMENT > 0, the default value is 1. When INCREMENT < 0, the default value is -9223372036854775807.
MAXVALUE9223372036854775806 or -1Specifies the maximum value of a sequence. When INCREMENT > 0, the default value is 9223372036854775806. When INCREMENT < 0, the default value is -1.
STARTMINVALUE or MAXVALUESpecifies the initial value of a sequence. When INCREMENT > 0, the default value is MINVALUE. When INCREMENT < 0, the default value is MAXVALUE.
CACHE1000Specifies the local cache size of a sequence in TiDB.
CYCLENO CYCLESpecifies 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.

ALTER SEQUENCE - 图6

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.

ALTER SEQUENCE - 图7

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:

  1. CREATE SEQUENCE s1;
  1. Query OK, 0 rows affected (0.15 sec)

Get the next two values from the sequence by executing the following SQL statement twice:

  1. SELECT NEXTVAL(s1);
  1. +-------------+
  2. | NEXTVAL(s1) |
  3. +-------------+
  4. |           1 |
  5. +-------------+
  6. 1 row in set (0.01 sec)
  1. SELECT NEXTVAL(s1);
  1. +-------------+
  2. | NEXTVAL(s1) |
  3. +-------------+
  4. |           2 |
  5. +-------------+
  6. 1 row in set (0.00 sec)

Change the increment of the sequence to 2:

  1. ALTER SEQUENCE s1 INCREMENT=2;
  1. Query OK, 0 rows affected (0.18 sec)

Now, get the next two values from the sequence again:

  1. SELECT NEXTVAL(s1);
  1. +-------------+
  2. | NEXTVAL(s1) |
  3. +-------------+
  4. |        1001 |
  5. +-------------+
  6. 1 row in set (0.02 sec)
  1. SELECT NEXTVAL(s1);
  1. +-------------+
  2. | NEXTVAL(s1) |
  3. +-------------+
  4. |        1003 |
  5. +-------------+
  6. 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:

  1. CREATE SEQUENCE s2 MAXVALUE=10;
  1. Query OK, 0 rows affected (0.17 sec)
  1. ALTER SEQUENCE s2 MAXVALUE=100;
  1. Query OK, 0 rows affected (0.15 sec)
  1. SHOW CREATE SEQUENCE s2\G
  1. *************************** 1. row ***************************
  2.        Sequence: s2
  3. Create Sequence: CREATE SEQUENCE `s2` start with 1 minvalue 1 maxvalue 100 increment by 1 cache 1000 nocycle ENGINE=InnoDB
  4. 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. 1, 3, 5, ...            // The sequence starts from 1 and increments by 2.
  2. SELECT SETVAL(seq, 6)   // Sets the current value of a sequence to 6.
  3. 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).

See also