CREATE SEQUENCE-PolarDB(PolarDB)-阿里云帮助中心

Creates a sequence in PolarDB for XScale. Three sequence types are supported: new sequences, group sequences, and time sequences.

New sequences

A new sequence generates consecutive integer values within a defined range. Use this sequence type in databases running in AUTO mode.

Note

In AUTO mode, CREATE SEQUENCE creates a new sequence by default. New sequences cannot be created in DRDS mode.

Syntax

CREATE [NEW] SEQUENCE <name>
[ START WITH <numeric value> ]
[ INCREMENT BY <numeric value> ]
[ MAXVALUE <numeric value> ]
[ CYCLE | NOCYCLE ]

Parameters

Parameter	Description	Default
`START WITH`	The starting value of the sequence.	`1`
`INCREMENT BY`	The increment between consecutive sequence values.	`1`
`MAXVALUE`	The maximum value of the sequence. Must be a positive integer. The maximum supported value is `9223372036854775807` (signed BIGINT).	`9223372036854775807`
`CYCLE`	When the sequence reaches MAXVALUE, it wraps around and restarts from the START WITH value. Mutually exclusive with `NOCYCLE`.	—
`NOCYCLE`	When the sequence reaches MAXVALUE, any further value request returns an error. Mutually exclusive with `CYCLE`.	Default behavior

Examples

Create a new sequence starting at 1000:

CREATE NEW SEQUENCE newseq START WITH 1000;

Create a new sequence starting at 1, incrementing by 2, with a maximum value of 100, cycling back to the start when the maximum is reached:

CREATE NEW SEQUENCE newseq2 START WITH 1 INCREMENT BY 2 MAXVALUE 100 CYCLE;

Group sequences

A group sequence distributes sequence generation across multiple database instances or databases, producing globally unique but nonconsecutive values. Use this type when you need distributed ID generation across instances.

Note

In DRDS mode, CREATE SEQUENCE creates a group sequence by default. In AUTO mode, specify GROUP explicitly.

Syntax

CREATE [GROUP] SEQUENCE <name>
[ START WITH <numeric value> ]
[ UNIT COUNT <numeric value> INDEX <numeric value> ]

Parameters

Parameter	Description	Default
`START WITH`	The reference start value. The actual start value may be greater, as it is determined by `UNIT COUNT` and `INDEX`.	`100001` (when `UNIT COUNT` and `INDEX` are not specified)
`UNIT COUNT`	The total number of units in the group sequence.	`1`
`INDEX`	The index of this unit within the group. Valid values: `0` to (`UNIT COUNT` − 1).	`0`

Usage notes

Group sequences produce nonconsecutive values. The START WITH value is for reference only; the actual start value may be greater.
After a group sequence is created, UNIT COUNT and INDEX cannot be changed.
A group sequence with more than one unit cannot be converted to another sequence type.

Examples

Create a single-unit group sequence:

CREATE GROUP SEQUENCE groupseq;

Create a three-unit global group sequence distributed across three instances or databases. Each unit sequence uses the same name and UNIT COUNT, but a unique INDEX.

Instance 1 or database 1:

CREATE GROUP SEQUENCE ugroupseq UNIT COUNT 3 INDEX 0;

Instance 2 or database 2:

CREATE GROUP SEQUENCE ugroupseq UNIT COUNT 3 INDEX 1;

Instance 3 or database 3:

CREATE GROUP SEQUENCE ugroupseq UNIT COUNT 3 INDEX 2;

Time sequences

Important

The column that stores time sequence values must be of the BIGINT data type.

Syntax

CREATE TIME SEQUENCE <name>

Example

CREATE TIME SEQUENCE seq3;