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 that you want to create. If you do not specify this parameter, the default value is used. Default value: 100001.
Examples

Create a group sequence.

  • Method 1

    You can use the CREATE SEQUENCE statement to create a group sequence.

    mysql> CREATE SEQUENCE seq1;
  • Method 2

    You can use the CREATE GROUP SEQUENCE statement to create a group sequence.

    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 that you want to create. The default start value is calculated based on the specified values of the INDEX and UNIT COUNT parameters. 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 in the unit group sequence. Default value: 1.
INDEX The unit index of the unit sequence. The valid value range is from 0 to the value that is obtained by subtracting 1 from the number of units. Default value: 0.
Note
  • If you do not specify a sequence type, a group sequence is created.
  • Values of group sequences and unit group sequences may be nonconsecutive. The value of the START WITH parameter is used as a reference for a group sequence or a unit group sequence. The actual start value of a group sequence or a unit group sequence can be equal to or greater than the value that is specified by START WITH.
  • Group sequences can be regarded as special cases of unit group sequences. If you set the UNIT COUNT parameter to 1 and the INDEX parameter to 0, the sequence that is created is a group sequence.
Examples

The following examples show how to create a globally unique sequence that contains three units. Three unit sequences that have the same sequence name, the same number of units, and different unit indexes are created for three different instances or databases. These three sequences are grouped to form a globally unique sequence.

  1. Create a unit sequence for Instance 1 or Database 1.
    mysql> CREATE GROUP SEQUENCE seq2 UNIT COUNT 3 INDEX 0;
  2. Create a unit sequence for Instance 2 or Database 2.
    mysql> CREATE GROUP SEQUENCE seq2 UNIT COUNT 3 INDEX 1;
  3. Create a unit 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.
Example

Create a time-based sequence.

You can use the CREATE TIME SEQUENCE statement to create a time-based sequence.

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 that you want to create. If you do not specify this parameter, the default start value is used. Default value: 1.
INCREMENT BY The increment between two adjacent sequence values. If you do not specify this parameter, the default increment is used. Default value: 1.
MAXVALUE The maximum value of the simple sequence. If you do not specify this parameter, the default maximum value is used. The default maximum value is 9223372036854775807 and of the signed BIGINT data type.
CYCLE or NOCYCLE You can select CYCLE or NOCYCLE. These options are used to specify whether to repeat the sequence from the value that is specified by the START WITH parameter after the sequence reaches the specified maximum value. If you do not specify an option, the default option is used. Default option: NOCYCLE.
Example

The following example shows how to create a simple sequence. The start value of the simple sequence is 1000, the increment is 2, and the maximum value is 99999999999. After the maximum value is reached, the sequence stops generating new values.

You can use the CREATE SIMPLE SEQUENCE statement to create a simple sequence.

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