DocsICP FilingConsole
Documentation
Home Page PolarDB PolarDB for Xscale PolarDB-X 1.0 (DRDS) SQL Reference Sequence Explicit sequences

Explicit sequences

Updated at:
Copy as MD
Product
My Favorites

This topic describes how to use explicit sequences.

Create a sequence

Group sequence
  • Syntax
    CREATE [ GROUP ] SEQUENCE <name>
[ START WITH <numeric value> ]
  • Parameters
    Parameter Description
    START WITH Specifies the starting value for the group sequence. If omitted, the default starting value is 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 Specifies the starting value for the unit group sequence. The default starting value depends on the unit count and unit index. If these parameters are omitted, the default starting value is 100001.
    UNIT COUNT Specifies the number of units for the unit group sequence. The default value is 1.
    INDEX Specifies the index of the unit for the unit group sequence. The value must be in the range of [0, UNIT COUNT - 1]. The default value is 0.
    Note
    • The default sequence type is group sequence.
    • Group sequences and unit group sequences are non-consecutive. The START WITH parameter provides only a guideline. A group sequence or unit group sequence does not start at the exact value specified, but is guaranteed to start with a greater value.
    • A group sequence is a special case of a unit group sequenceUnit group sequence where is 1 and is 0.
  • Example

    To create a globally unique numeric sequence that consists of three units, create three unit group sequences that have the same name and unit count but different unit indexes. Then, assign each sequence to a different instance or database.

    1. Instance 1 or database 1:
      mysql> CREATE GROUP SEQUENCE seq2 UNIT COUNT 3 INDEX 0;
    2. Instance 2 or database 2:
      mysql> CREATE GROUP SEQUENCE seq2 UNIT COUNT 3 INDEX 1;
    3. Instance 3 or database 3:
      mysql> CREATE GROUP SEQUENCE seq2 UNIT COUNT 3 INDEX 2;
Time-based sequence
  • Syntax
    CREATE TIME SEQUENCE <name>
    Important The column that stores the values of a time-based sequence must be of the BIGINT type.
  • Example
    mysql> 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 Specifies the starting value of the simple sequence. If this parameter is not specified, the default value is 1.
    INCREMENT BY Specifies the increment for the simple sequence. This value is also known as the interval or step. If omitted, the default value is 1.
    MAXVALUE Specifies the maximum value for the simple sequence. If omitted, the default value is the maximum value of a signed BIGINT, which is 9223372036854775807.
    CYCLE or NOCYCLE Specifies whether the simple sequence restarts from the START WITH value after it reaches the maximum value. If omitted, NOCYCLE is used by default.
  • Example

    The following example creates a simple sequence that starts at 1000, increments by 2, has a maximum value of 99999999999, and does not cycle.

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

Modify a sequence

Using PolarDB-X, you can modify sequences in the following ways:

  • Modify the parameters of a simple sequence, including the starting value, increment, maximum value, and cycle option.
  • Modify the starting value of a group sequence or a unit group sequence.
  • Convert between sequence types. Unit group sequences are excluded.
Notes
  • Group sequences and unit group sequences are non-consecutive. The START WITH parameter provides only a guideline. The sequence does not start at the exact value specified, but is guaranteed to start with a greater value.
  • You cannot convert a unit group sequence to another type or modify its unit-related parameters.
  • When you modify a simple sequence by specifying START WITH, the change takes effect immediately. The next value retrieved will be the new START WITH value.
  • To prevent conflicts, carefully evaluate existing sequence values and their generation rate before changing the START WITH value. Modify the START WITH value only when necessary.
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 starting value of the sequence. This parameter has no default value and is ignored if not specified. This parameter is required when you convert the sequence type.
    INCREMENT BY This parameter is valid only when you convert a group sequence to a simple sequence. It specifies the increment for the new simple sequence. If omitted, the default value is 1.
    MAXVALUE This parameter is valid only when you convert a group sequence to a simple sequence. It specifies the maximum value for the new simple sequence. If omitted, the default value is the maximum value of a signed BIGINT, which is 9223372036854775807.
    CYCLE or NOCYCLE This parameter is valid only when you convert a group sequence to a simple sequence. It specifies whether the simple sequence restarts from the START WITH value after it reaches the maximum value. If omitted, NOCYCLE is used by default.
    Note When the target type is TIME, the preceding parameters are not supported.
Unit group sequence
  • Syntax
    ALTER SEQUENCE <name> 
START WITH <numeric value>
  • Parameters
    Parameter Description
    START WITH The starting value of the unit group sequence. This parameter has no default value and is ignored if omitted.
    Note You cannot convert a unit group sequence to another type or modify its unit-related parameters.
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 starting value of the sequence. This parameter has no default value and is ignored if not specified. This parameter is required when you convert the sequence type.
    INCREMENT BY The increment for the simple sequence. If omitted, the default value is 1. This parameter is not valid when you convert a time-based sequence to a group sequence.
    MAXVALUE The maximum value for the simple sequence. If not specified, the default value is the maximum value of a signed BIGINT, which is 9223372036854775807. This parameter is not valid when you convert a time-based sequence to a group sequence.
    CYCLE or NOCYCLE You can specify only one of these options. It specifies whether the simple sequence restarts from the START WITH value after it reaches the maximum value. If omitted, the default value is NOCYCLE. This parameter is not valid when you convert a time-based sequence to a group sequence.
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 starting value of the sequence. This parameter has no default value and is ignored if not specified. This parameter is required when you convert the sequence type.
    INCREMENT BY The increment for the simple sequence. If omitted, the default value is 1. This parameter is not valid when you convert a simple sequence to a group sequence.
    MAXVALUE The maximum value for the simple sequence. If not specified, the default value is the maximum value of a signed BIGINT, which is 9223372036854775807. This parameter is not valid when you convert a simple sequence to a group sequence.
    CYCLE or NOCYCLE You can specify only one of these options. It specifies whether the simple sequence restarts from the START WITH value after it reaches the maximum value. If omitted, the default value is NOCYCLE. This parameter is not valid when you convert a simple sequence to a group sequence.
    Note When the target type is TIME, the preceding parameters are not supported.
Convert between sequence types

When converting a sequence from one type to another, note the following:

  • Use the CHANGE TO <sequence_type> clause in an ALTER SEQUENCE statement.
  • If an ALTER SEQUENCE statement includes the CHANGE TO clause, you must specify the START WITH parameter to prevent the generation of duplicate values.
  • Conversions to or from a unit group sequence are not supported.
Example
  • To modify the simple sequence named seq4, change its starting value to 3000, its increment to 5, its maximum value to 1000000, and set it to cycle:
    mysql> ALTER SEQUENCE seq4 START WITH 3000 INCREMENT BY 5 MAXVALUE 1000000 CYCLE;
  • To convert a group sequence to a simple sequence:
    mysql> ALTER SEQUENCE seq1 CHANGE TO SIMPLE START WITH 1000000;

Query and retrieve sequences

Query a sequence
  • Syntax
    SHOW SEQUENCES
  • Example
    mysql> SHOW SEQUENCES;

    The following 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 result set, the TYPE column displays an abbreviation of the sequence type.
Retrieve an explicit sequence value
  • Syntax
    [<schema_name>.]<sequence name>.NEXTVAL
  • Examples
    • Method 1
      mysql> SELECT sample_seq.nextval FROM dual;

      The following result is returned:

      +--------------------+
| SAMPLE_SEQ.NEXTVAL |
+--------------------+
|             101001 |
+--------------------+
1 row in set (0.04 sec)
    • Method 2
      mysql> INSERT INTO some_users (name,address,gmt_create,gmt_modified,intro) VALUES ('sun',sample_seq.nextval,now(),now(),'aa');
      Note
      • This method uses sample_seq.nextval as a value in the SQL statement.
      • If you specify the AUTO_INCREMENT parameter when you create a table, you do not need to specify the auto-increment column in the INSERT statement. PolarDB-X automatically manages the values.
Retrieve sequence values in batches
  • Syntax
    SELECT [<schema_name>.]<sequence name>.NEXTVAL FROM DUAL WHERE COUNT = <numeric value>
  • Example
    mysql> SELECT sample_seq.nextval FROM dual WHERE count = 10;

    The following 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
    DROP SEQUENCE <name>
  • Example
    mysql> DROP SEQUENCE seq3;
Previous：LimitsNext：Implicit usage