All Products
Search
Document Center

Explict sequence

Last Updated: Aug 14, 2020

This topic describes how to use data definition language (DDL) statements to create, modify, delete, and query sequences and query the values of explicit sequences in Distributed Relational Database Service (DRDS).

Create a sequence

Group Sequence

  1. CREATE [ GROUP ] SEQUENCE <name>
  2. [ START WITH <numeric value> ]
Parameter Description
START WITH The start value of a group sequence. If this parameter is not specified, the start value is 100001 by default.

Unit group sequence

  1. CREATE [ GROUP ] SEQUENCE <name>
  2. [ START WITH <numeric value> ]
  3. [ UNIT COUNT <numeric value> INDEX <numeric value> ]
Parameter Description
START WITH The start value of a unit group sequence. The default start value depends on the number of units and the unit index. If the number of units and the unit index are not specified or both are set to default values, the default start value is 100001.
UNIT COUNT The number of units in a unit group sequence. The default value is 1.
INDEX The unit index of a unit group sequence. The value ranges from [0 to the number of units - 1]. The default value is 0.

Note:

  • If the UNIT COUNT and INDEX parameters of the unit group sequence are not specified or both are set to default values, the unit group sequence is equivalent to a group sequence.
  • Unit group sequences that belong to the same DRDS sequence must have the same number of units but different unit indexes.

Time-based Sequence

  1. CREATE TIME SEQUENCE <name>

Note: The name of a column that stores time-based sequence values must be of the BIGINT type.

Simple Sequence

  1. CREATE SIMPLE SEQUENCE <name>
  2. [ START WITH <numeric value> ]
  3. [ INCREMENT BY <numeric value> ]
  4. [ MAXVALUE <numeric value> ]
  5. [ CYCLE | NOCYCLE ]
Parameter Description
START WITH The start value of the simple sequence. If this parameter is not specified, the start value is 1 by default.
INCREMENT BY The increment (or interval value or step) of each simple sequence increase. If this parameter is not specified, the default value is 1.
MAXVALUE The maximum value of the 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, which 9223372036854775807.
CYCLE or NOCYCLE Indicates whether to repeat from the value specified by the START WITH parameter after the simple sequence value reaches the maximum value. If this parameter is not specified, the default value is NOCYCLE.

Notes

  • If the sequence type is not specified, the group sequence type is used by default.
  • The group sequence and the unit group sequence are nonconsecutive. The START WITH parameter only provides reference for group sequences and unit group sequences. The start value of a group sequence or a unit group sequence is not necessarily the same as the value specified by the START WITH parameter but is invariably greater than this value.
  • The group sequence is a special case of the unit group sequence of which UNIT COUNT is 1 and INDEX is 0.

Examples

Example 1: Create a group sequence.

  • Method 1:
  1. mysql> CREATE SEQUENCE seq1;
  2. Query OK, 1 row affected (0.01 sec)
  • Method 2:
  1. mysql> CREATE GROUP SEQUENCE seq1;
  2. Query OK, 1 row affected (0.01 sec)

Example 2: Create a DRDS sequence that contains three units. Assign three unit group sequences that have the same name and the same number of units but different unit indexes to three instances or databases respectively to form a DRDS sequence.

Instance 1 or database 1:

  1. mysql> CREATE GROUP SEQUENCE seq2 UNIT COUNT 3 INDEX 0;
  2. Query OK, 1 row affected (0.01 sec)

Instance 2 or database 2:

  1. mysql> CREATE GROUP SEQUENCE seq2 UNIT COUNT 3 INDEX 1;
  2. Query OK, 1 row affected (0.01 sec)

Instance 3 or database 3:

  1. mysql> CREATE GROUP SEQUENCE seq2 UNIT COUNT 3 INDEX 2;
  2. Query OK, 1 row affected (0.01 sec)

Example 3: Create a time-based sequence.

  1. mysql> CREATE TIME SEQUENCE seq3;
  2. Query OK, 1 row affected (0.03 sec)

Example 4: Create a simple sequence with a start value of 1000, a step of 2, and a maximum value of 99999999999, which does not repeat after increasing to the maximum value.

  1. mysql> CREATE SIMPLE SEQUENCE seq4 START WITH 1000 INCREMENT BY 2 MAXVALUE 99999999999 NOCYCLE;
  2. Query OK, 1 row affected (0.03 sec)

Modify a sequence

DRDS allows you to modify sequences in the following ways:

  • For simple sequences, change the values of START WITH, INCREMENT BY, MAXVALUE, and CYCLE or NOCYCLE.
  • For group sequences and unit group sequences, change the value of the START WITH parameter.
  • Convert between different types of sequences (except unit group sequences).

Group Sequence

  1. ALTER SEQUENCE <name> [ CHANGE TO SIMPLE | TIME ]
  2. START WITH <numeric value>
  3. [ INCREMENT BY <numeric value> ]
  4. [ MAXVALUE <numeric value> ]
  5. [ CYCLE | NOCYCLE ]
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 types.
INCREMENT BY The increment (or interval value or step) of each simple sequence increase. This parameter only takes effect when you convert a group sequence to a simple sequence. If this parameter is not specified, the default value is 1.
MAXVALUE The maximum value of a simple sequence. This parameter only takes effect 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, which 9223372036854775807.
CYCLE or NOCYCLE Indicates whether to repeat from the value specified by the START WITH parameter after the simple sequence value reaches the maximum value. This parameter is valid only when you convert a group sequence to a simple sequence. If this parameter is not specified, the default value is NOCYCLE.

Note: The time-based sequence does not support this parameter.

Unit group sequence

  1. ALTER SEQUENCE <name>
  2. START WITH <numeric value>
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 any other type of sequence or modify unit-related parameters.

Time-based Sequence

  1. ALTER SEQUENCE <name> [ CHANGE TO GROUP | SIMPLE ]
  2. START WITH <numeric value>
  3. [ INCREMENT BY <numeric value> ]
  4. [ MAXVALUE <numeric value> ]
  5. [ CYCLE | NOCYCLE ]
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 types.
INCREMENT BY The increment (or interval value or step) of each simple sequence increase. If this parameter is not specified, the default value is 1. This parameter is invalid when you convert a simple sequence to a group sequence.
MAXVALUE The maximum value of the 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, which 9223372036854775807. This parameter is invalid when you convert a simple sequence to a group sequence.
CYCLE or NOCYCLE Indicates whether to repeat from the value specified by the START WITH parameter after the simple sequence value reaches the maximum value. If this parameter is not specified, the default value is NOCYCLE. This parameter is invalid when you convert a simple sequence to a group sequence.

Simple Sequence

  1. ALTER SEQUENCE <name> [ CHANGE TO GROUP | TIME ]
  2. START WITH <numeric value>
  3. [ INCREMENT BY <numeric value> ]
  4. [ MAXVALUE <numeric value> ]
  5. [ CYCLE | NOCYCLE ]
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 types.
INCREMENT BY The increment (or interval value or step) of each simple sequence increase. If this parameter is not specified, the default value is 1. This parameter is invalid when you convert a simple sequence to a group sequence.
MAXVALUE The maximum value of the 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, which 9223372036854775807. This parameter is invalid when you convert a simple sequence to a group sequence.
CYCLE or NOCYCLE Indicates whether to repeat from the value specified by the START WITH parameter after the simple sequence value reaches the maximum value. If this parameter is not specified, the default value is NOCYCLE. This parameter is invalid when you convert a simple sequence to a group sequence.

Note: The time-based sequence does not support this parameter.

Notes

  • The group sequence and the unit group sequence are nonconsecutive. The START WITH parameter only provides reference for group sequences and unit group sequences. The start value of a group sequence or a unit group sequence is not necessarily the same as the value specified by the START WITH parameter but is invariably greater than this value.
  • You cannot convert unit group sequences to any other type of sequence or modify unit-related parameters.
  • If you set the START WITH parameter when you modify a simple sequence, the value of the START WITH parameter takes effect immediately. The next automatically generated sequence value starts from the new value of the START WITH parameter. For example, if you change the value of the START WITH parameter to 200 when the sequence value increases to 100, the next automatically generated sequence value starts from 200.
  • Before you change the value of the START WITH parameter, you must analyze the existing sequence values and the speed of creating sequence values to avoid conflicts. Exercise caution when you modify the value of the START WITH parameter.

Convert a sequence type to another

  • You can convert a sequence type to another by using the CHANGE TO<sequence_type> clause of ALTER SEQUENCE.
  • If you specify the CHANGE TO clause in ALTER SEQUENCE, you must add the START WITH parameter to avoid forgetting to specify the start value and getting duplicate values. If the CHANGE TO clause is not specified, the START WITH parameter is optional.
  • You cannot convert other types of sequences to unit group sequences or convert unit group sequences to other types of sequences.

Examples

Example 1: Change the start value of the simple sequence seq4 to 3000, the step to 5, and the maximum value to 1000000. The sequence value repeats after increasing to the maximum value.

  1. mysql> ALTER SEQUENCE seq4 START WITH 3000 INCREMENT BY 5 MAXVALUE 1000000 CYCLE;
  2. Query OK, 1 row affected (0.01 sec)

Example 2: Convert a group sequence to a simple sequence.

  1. mysql> ALTER SEQUENCE seq1 CHANGE TO SIMPLE START WITH 1000000;
  2. Query OK, 1 row affected (0.02 sec)

Delete a sequence

Syntax

  1. DROP SEQUENCE <name>

Example

  1. mysql> DROP SEQUENCE seq3;
  2. Query OK, 1 row affected (0.02 sec)

Query sequences

Syntax

  1. SHOW SEQUENCES

The TYPE column in the results set lists the abbreviations of sequence types.

Example

  1. mysql> SHOW SEQUENCES;
  2. +------+--------+------------+------------+------------+--------------+------------+-------------+-------+--------+
  3. | NAME | VALUE | UNIT_COUNT | UNIT_INDEX | INNER_STEP | INCREMENT_BY | START_WITH | MAX_VALUE | CYCLE | TYPE |
  4. +------+--------+------------+------------+------------+--------------+------------+-------------+-------+--------+
  5. | seq1 | 100000 | 1 | 0 | 100000 | N/A | N/A | N/A | N/A | GROUP |
  6. | seq2 | 400000 | 3 | 1 | 100000 | N/A | N/A | N/A | N/A | GROUP |
  7. | seq3 | N/A | N/A | N/A | N/A | N/A | N/A | N/A | N/A | TIME |
  8. | seq4 | 1006 | N/A | N/A | N/A | 2 | 1000 | 99999999999 | N | SIMPLE |
  9. +------+--------+------------+------------+------------+--------------+------------+-------------+-------+--------+
  10. 4 rows in set (0.00 sec)

Query the value of an explicit sequence

Syntax

  1. [<schema_name>.]<sequence name>.NEXTVAL

Example

  1. SELECT sample_seq.nextval FROM dual;
  2. +--------------------+
  3. | SAMPLE_SEQ.NEXTVAL |
  4. +--------------------+
  5. | 101001 |
  6. +--------------------+
  7. 1 row in set (0.04 sec)

You can write sample_seq.nextval as a value to the SQL statement:

  1. mysql> INSERT INTO some_users (name,address,gmt_create,gmt_modified,intro) VALUES ('sun',sample_seq.nextval,now(),now(),'aa');
  2. Query OK, 1 row affected (0.01 sec)

Note: If you set the AUTO_INCREMENT parameter when creating a table, you do not need to specify an auto-increment column when running the INSERT statement. The auto-increment column is automatically maintained by DRDS.

Query sequence values in batches

Syntax

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

Example

  1. mysql> SELECT sample_seq.nextval FROM dual WHERE count = 10;
  2. +--------------------+
  3. | SAMPLE_SEQ.NEXTVAL |
  4. +--------------------+
  5. | 101002 |
  6. | 101003 |
  7. | 101004 |
  8. | 101005 |
  9. | 101006 |
  10. | 101007 |
  11. | 101008 |
  12. | 101009 |
  13. | 101010 |
  14. | 101011 |
  15. +--------------------+
  16. 10 row in set (0.04 sec)