This topic describes how to modify different types of sequences.

PolarDB-X 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 a different type, but the source type and the destination type cannot be a unit group sequence.

Usage notes

When you convert a sequence from one type to a different type, 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 a different type or from a different type to a unit group sequence. You also 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 immediately takes effect. 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 at which sequence values are generated 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, the parameter is ignored. This parameter is required when you convert the sequence from one type to a different type.
INCREMENT BY The increment between two simple sequence values that are adjacent. 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 option is 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 sequence

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, the parameter is ignored.
Note You cannot convert a unit group sequence to a different 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, the parameter is ignored. This parameter is required when you convert the sequence from one type to a different type.
INCREMENT BY The increment between two simple sequence values that are adjacent. 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 option is 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, the parameter is ignored. This parameter is required when you convert the sequence from one type to a different type.
INCREMENT BY The increment between two simple sequence values that are adjacent. 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 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 option is 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 a different type

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

  • Use the CHANGE TO <sequence_type> clause in the ALTER SEQUENCE statement to convert a sequence to a different 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. 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 a different type or convert a sequence of a different 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.
    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;