Create a sequence

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
    • Method 1
      mysql> CREATE SEQUENCE seq1;
    • Method 2
      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

    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;

Modify a sequence

Distributed Relational Database Service (PolarDB-X 1.0) allows you to modify a sequence in the following ways:

  • For a simple sequence, you can change the values of START WITH, INCREMENT BY, MAXVALUE, and CYCLE or NOCYCLE.
  • For a group sequence or a unit group sequence, you can change the value of START WITH.
  • You can convert a sequence from one type to another, but neither of the source type nor the destination type can be a unit group sequence.
Note

When you convert a sequence from one type to another, take note of the following points:

  • The values of group sequences are nonconsecutive. The values of unit group sequences are also nonconsecutive. The value of the START WITH parameter serves only as a reference for these two types of sequences. A group sequence or a unit group sequence may not start from the value specified by START WITH but must start from a value that is greater than the specified value.
  • You cannot convert a sequence from a unit group type to another or from another type to a unit group sequence. In addition, you cannot change the parameter values of a unit group sequence.
  • If you have specified a value for START WITH when you modify a simple sequence, the value takes effect immediately. The next sequence value starts from the specified value. For example, if you change the value of START WITH to 200 when the sequence value increases to 100, the next sequence value starts from 200.
  • Before you change the value of START WITH, analyze the existing sequence values and the rate of generating sequence values to prevent duplicate sequence values from being generated. Exercise caution when you change the value of START WITH.
Group Sequence
  • Syntax
    ALTER SEQUENCE <name> [ CHANGE TO SIMPLE | TIME ]
    START WITH <numeric value>
    [ INCREMENT BY <numeric value> ]
    [ MAXVALUE <numeric value> ]
    [ CYCLE | NOCYCLE ]
  • Parameters
    Parameter Description
    START WITH The start value of the sequence. This parameter has no default value. If this parameter is not specified, it is ignored. This parameter is required when you convert the sequence from one type to another.
    INCREMENT BY The increment between two adjacent simple sequence values. This parameter takes effect only when you convert a group sequence to a simple sequence. If this parameter is not specified, the default value is used. The default value is 1.
    MAXVALUE The maximum value of the simple sequence. This parameter takes effect only when you convert a group sequence to a simple sequence. If this parameter is not specified, the default value is used. The default value is the maximum value of the signed BIGINT type: 9223372036854775807.
    CYCLE or NOCYCLE Specifies whether to continue generating sequence values after the sequence value reaches the maximum value of the sequence that starts from the value specified by START WITH. You can specify only one of the two options: CYCLE and NOCYCLE. Specify the CYCLE option to continue generating sequence values. Specify the NOCYCLE option to stop generating sequence values. The CYCLE or NOCYCLE option takes effect only when you convert a group sequence to a simple sequence. If no options are specified, the default option takes effect. The default option is NOCYCLE.
    Note When you convert a sequence to a time-based sequence, the preceding parameters are not supported.
Unit group sequences
  • Syntax
    ALTER SEQUENCE <name> 
    START WITH <numeric value>
  • Parameters
    Parameter Description
    START WITH The start value of the unit group sequence. This parameter has no default value. If this parameter is not specified, it is ignored.
    Note You cannot convert a unit group sequence to another type. In addition, you cannot modify the parameters of a unit group sequence.
Time-based Sequence
  • Syntax
    ALTER SEQUENCE <name>[ CHANGE TO GROUP | SIMPLE ]
    START WITH <numeric value>
    [ INCREMENT BY <numeric value> ]
    [ MAXVALUE <numeric value> ]
    [ CYCLE | NOCYCLE ]
  • Parameters
    Parameter Description
    START WITH The start value of the sequence. This parameter has no default value. If this parameter is not specified, it is ignored. This parameter is required when you convert the sequence from one type to another.
    INCREMENT BY The increment between two adjacent simple sequence values. This parameter does not take effect when you convert a simple sequence to a group sequence. If this parameter is not specified, the default value is used. The default value is 1.
    MAXVALUE The maximum value of the simple sequence. This parameter does not take effect when you convert a simple sequence to a group sequence. If this parameter is not specified, the default value is used. The default value is the maximum value of the signed BIGINT type: 9223372036854775807.
    CYCLE or NOCYCLE Specifies whether to continue generating sequence values after the sequence value reaches the maximum value of the sequence that starts from the value specified by START WITH. You can specify only one of the two options: CYCLE and NOCYCLE. Specify the CYCLE option to continue generating sequence values. Specify the NOCYCLE option to stop generating sequence values. The CYCLE or NOCYCLE is ineffective when you convert a simple sequence to a group sequence. If no options are specified, the default option takes effect. The default option is NOCYCLE.
Simple Sequence
  • Syntax
    ALTER SEQUENCE <name> [ CHANGE TO GROUP | TIME ]
    START WITH <numeric value>
    [ INCREMENT BY <numeric value> ]
    [ MAXVALUE <numeric value> ]
    [ CYCLE | NOCYCLE ]
  • Parameters
    Parameter Description
    START WITH The start value of the sequence. This parameter has no default value. If this parameter is not specified, it is ignored. This parameter is required when you convert the sequence from one type to another.
    INCREMENT BY The increment between two adjacent simple sequence values. This parameter does not take effect when you convert a simple sequence to a group sequence. If this parameter is not specified, the default value is used. The default value is 1.
    MAXVALUE The maximum value of the simple sequence. This parameter does not take effect when you convert a simple sequence to a group sequence. If this parameter is not specified, the default value is used. The default value is the maximum value of the signed BIGINT type: 9223372036854775807.
    CYCLE or NOCYCLE Specifies whether to continue generating sequence values after the sequence value reaches the maximum value of the sequence that starts from the value specified by START WITH. You can specify only one of the two options: CYCLE and NOCYCLE. Specify the CYCLE option to continue generating sequence values. Specify the NOCYCLE option to stop generating sequence values. The CYCLE or NOCYCLE is ineffective when you convert a simple sequence to a group sequence. If no options are specified, the default option takes effect. The default option is NOCYCLE.
    Note When you convert a sequence to a time-based sequence, the preceding parameters are not supported.
Convert a sequence from one type to another

When you convert a sequence from one type to another, take note of the following points:

  • Use the CHANGE TO <sequence_type> clause in the ALTER SEQUENCE statement to convert a sequence to another type.
  • If you include the CHANGE TO clause in ALTER SEQUENCE, you must specify the START WITH parameter to prevent duplicate values from being generated. This way, if you forget to specify a start value, duplicate values are not generated. The CHANGE TO clause is optional. If you omit this clause, you do not need to specify the START WITH parameter.
  • You cannot convert a unit group sequence to another type or convert a sequence of another type to a unit group sequence.

Examples

  • Modify a simple sequence named seq4: Set START WITH to 3000, INCREMENT BY to 5, and MAXVALUE to 1000000, and change NOCYCLE to CYCLE. To modify the simple sequence, execute the following statement:
    mysql> ALTER SEQUENCE seq4 START WITH 3000 INCREMENT BY 5 MAXVALUE 1000000 CYCLE;
  • Convert a group sequence to a simple sequence.
    mysql> ALTER SEQUENCE seq1 CHANGE TO SIMPLE START WITH 1000000;

Query the type and value of a sequence

Query a sequence
  • Syntax
    SHOW SEQUENCES
  • Examples

    Execute the following statement in your CLI:

    mysql> SHOW SEQUENCES;

    The following query result is returned:

    +------+--------+------------+------------+------------+--------------+------------+-------------+-------+--------+
    | NAME | VALUE  | UNIT_COUNT | UNIT_INDEX | INNER_STEP | INCREMENT_BY | START_WITH | MAX_VALUE   | CYCLE | TYPE   |
    +------+--------+------------+------------+------------+--------------+------------+-------------+-------+--------+
    | seq1 | 100000 | 1          | 0          | 100000     | N/A          | N/A        | N/A         | N/A   | GROUP  |
    | seq2 | 400000 | 3          | 1          | 100000     | N/A          | N/A        | N/A         | N/A   | GROUP  |
    | seq3 | N/A    | N/A        | N/A        | N/A        | N/A          | N/A        | N/A         | N/A   | TIME   |
    | seq4 | 1006   | N/A        | N/A        | N/A        | 2            | 1000       | 99999999999 | N     | SIMPLE |
    +------+--------+------------+------------+------------+--------------+------------+-------------+-------+--------+
    4 rows in set (0.00 sec)
    Note In the query result, the values in the TYPE column are the abbreviations of the sequence types.
Query a sequence
  • Syntax
    [<schema_name>.]<sequence name>.NEXTVAL
  • Examples
    • Method 1
      mysql> SELECT sample_seq.nextval FROM dual;

      The following query result is returned:

      +--------------------+
      | SAMPLE_SEQ.NEXTVAL |
      +--------------------+
      |             101001 |
      +--------------------+
      1 row in set (0.04 sec)
    • Method 2

      Execute the following statement in your CLI:

      mysql> INSERT INTO some_users (name,address,gmt_create,gmt_modified,intro) VALUES ('sun',sample_seq.nextval,now(),now(),'aa');
      Note
      • When you use this statement, you include sample_seq.nextval in the SQL statement as a value.
      • If the AUTO_INCREMENT parameter is specified when you create a table, you do not need to specify an auto-increment column when you execute the INSERT statement. PolarDB-X 1.0 automatically manage the value of the AUTO_INCREMENT parameter.
Use the following syntax to query multiple sequence values at a time:
  • Syntax

    The following code provides the syntax:

    SELECT [<schema_name>.]<sequence name>.NEXTVAL FROM DUAL WHERE COUNT = <numeric value>
  • Examples

    Execute the following statement in your CLI:

    mysql> SELECT sample_seq.nextval FROM dual WHERE count = 10;

    The following query result is returned:

    +--------------------+
    | SAMPLE_SEQ.NEXTVAL |
    +--------------------+
    |             101002 |
    |             101003 |
    |             101004 |
    |             101005 |
    |             101006 |
    |             101007 |
    |             101008 |
    |             101009 |
    |             101010 |
    |             101011 |
    +--------------------+
    10 row in set (0.04 sec)

Delete a sequence

  • Syntax

    Use the following the syntax to delete a sequence:

    DROP SEQUENCE <name>
  • Examples

    Execute the following statement in your CLI:

    mysql> DROP SEQUENCE seq3;