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

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

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.

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 a NULL 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 of START 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.

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

  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

• DROP SEQUENCE
• SHOW CREATE SEQUENCE