This topic describes how to create sequences of different types.

Group Sequence

Syntax
CREATE [ GROUP ] SEQUENCE <name>
[ START WITH <numeric value> ]
Parameters
Parameter Description
START WITH The start value of the group sequence. If you do not specify this parameter, the default value is used. Default value: 100001.
Examples

Create a group sequence.

  • Method 1

    Execute the following statement in your CLI:

    mysql> CREATE SEQUENCE seq1;
  • Method 2

    Execute the following statement in your CLI:

    mysql> CREATE GROUP SEQUENCE seq1;

Unit group sequence

Syntax
CREATE [ GROUP ] SEQUENCE <name>
[ START WITH <numeric value> ]
[ UNIT COUNT <numeric value> INDEX <numeric value> ]
Parameters
Parameter Description
START WITH The start value of the unit group sequence. The default start value depends on the unit index and the number of units. If you do not specify the INDEX parameter or the UNIT COUNT parameter, the default start value is used. Default value: 100001.
UNIT COUNT The number of units specified for the unit group sequence. Default value: 1.
INDEX The unit index of the unit group sequence. The value range is from 0 to the value obtained by subtracting 1 from the number of units. Default value: 0.
Note
  • If you do not specify a sequence type, the group sequence type is used by default.
  • Values of group sequences and unit group sequences may be nonconsecutive. The START WITH parameter only provides reference for group sequences and unit group sequences. A group sequence or a unit group sequence may not start from the value specified by START WITH. The actual start value is always greater than the specified start value.
  • A group sequence can be regarded as a special case of unit group sequences. A group sequence means that when you create a unit group sequence, you set the UNIT COUNT parameter to 1 and the INDEX parameter to 0.
Examples

Create a globally unique numeric sequence that has three units. In this example, create three unit group sequences that have the same sequence name, the same number of units, and different unit indexes for three different instances or databases. These three sequences form a globally unique sequence.

  1. Create a unit group sequence for Instance 1 or Database 1.
    mysql> CREATE GROUP SEQUENCE seq2 UNIT COUNT 3 INDEX 0;
  2. Create a unit group sequence for Instance 2 or Database 2.
    mysql> CREATE GROUP SEQUENCE seq2 UNIT COUNT 3 INDEX 1;
  3. Create a unit group sequence for Instance 3 or Database 3.
    mysql> CREATE GROUP SEQUENCE seq2 UNIT COUNT 3 INDEX 2;

Time-based Sequence

Syntax
CREATE TIME SEQUENCE <name>
Notice The column that is used to store the values of a time-based sequence must be of the BIGINT data type.
Examples

Create a time-based sequence.

Execute the following statement in your CLI:

7mysql> CREATE TIME SEQUENCE seq3;

Simple Sequence

Syntax
CREATE SIMPLE SEQUENCE <name>
[ START WITH <numeric value> ]
[ INCREMENT BY <numeric value> ]
[ MAXVALUE <numeric value> ][ CYCLE | NOCYCLE ]
Parameters
Parameter Description
START WITH The start value of the simple sequence. If you do not specify this parameter, the default value is used. Default value: 1.
INCREMENT BY The increment between two adjacent sequence values. If you do not specify this parameter, the default value is used. Default value: 1.
MAXVALUE The maximum value allowed by the simple sequence. If you do not specify this parameter, the default value is used. The default maximum value is of the signed BIGINT data type. For example, you can set the maximum value to 9223372036854775807.
CYCLE or NOCYCLE You can select only CYCLE or NOCYCLE. These options are used to specify whether to repeat the sequence that starts from the value specified by START WITH after the sequence reaches the specified maximum value. If you do not specify an option, the default option is used. Default option: NOCYCLE.
Examples

Create a simple sequence. The start value of the simple sequence is 1000, the step size is 2, and the maximum value is 99999999999. After the maximum value is reached, the sequence does not generate values from the start value.

Execute the following statement in your CLI:

mysql> CREATE SIMPLE SEQUENCE seq4 START WITH 1000 INCREMENT BY 2 MAXVALUE 99999999999 NOCYCLE;