Skip to content

Latest commit

 

History

History
318 lines (231 loc) · 9 KB

sql-statement-create-sequence.md

File metadata and controls

318 lines (231 loc) · 9 KB
Raw
title summary aliases
CREATE SEQUENCE
An overview of the usage of CREATE SEQUENCE for the TiDB database.
/docs/dev/sql-statements/sql-statement-create-sequence/
/docs/dev/reference/sql/statements/create-sequence/

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 ::=
    '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

{{< copyable "sql" >}}

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 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 object with the default parameter:

    {{< copyable "sql" >}}

    CREATE SEQUENCE seq;
    Query OK, 0 rows affected (0.06 sec)

  • Use the nextval() function to get the next value of the sequence object:

    {{< copyable "sql" >}}

    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:

    {{< copyable "sql" >}}

    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:

    {{< copyable "sql" >}}

    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:

    {{< copyable "sql" >}}

    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:

    {{< copyable "sql" >}}

    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 a NULL value.

    {{< copyable "sql" >}}

    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 of START parameter.

    {{< copyable "sql" >}}

    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.

    {{< copyable "sql" >}}

    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.

    {{< copyable "sql" >}}

    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.

    {{< copyable "sql" >}}

    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.

    {{< copyable "sql" >}}

    INSERT into t values();
    Query OK, 1 row affected (0.00 sec)

    {{< copyable "sql" >}}

    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 of seq2 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.

    {{< copyable "sql" >}}

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

See also