Configure a sequence for an auto-increment column during table creation - PolarDB

When you create a table with an auto-increment column, PolarDB-X automatically creates a sequence and associates it with that column. The sequence type depends on the database's partitioning mode:

AUTO mode databases: a NEW sequence is created by default.
DRDS mode databases: a GROUP sequence is created by default.

The associated sequence is named using the format AUTO_SEQ_<table_name>.

To override the default, specify a sequence type explicitly in the CREATE TABLE statement.

Syntax

NEW or TIME sequence

CREATE TABLE <name> (
   <column> ... AUTO_INCREMENT [ BY NEW | TIME ],
   <column definition>,
   ...
) ... AUTO_INCREMENT=<start value>

Note If you specify the BY TIME keyword that specifies the time-based sequence type for the auto-increment column, the data type of the auto-increment column must be BIGINT.

GROUP sequence

CREATE TABLE <name> (
   <column> ... AUTO_INCREMENT [ BY GROUP ] [ UNIT COUNT <numeric value> INDEX <numeric value> ],
   <column definition>,
   ...
) ... AUTO_INCREMENT=<start value>

Use UNIT COUNT and INDEX to distribute a single GROUP sequence across multiple instances or databases. Set UNIT COUNT to the total number of instances and INDEX to the position of each instance.

Examples

Default GROUP sequence (DRDS mode)

In DRDS mode, omitting BY creates a GROUP sequence automatically:

CREATE TABLE tab1 (
    col1 BIGINT NOT NULL AUTO_INCREMENT,
    col2 VARCHAR(16),
    PRIMARY KEY(col1)
) DBPARTITION BY HASH(col1);

Default NEW sequence (AUTO mode)

In AUTO mode, omitting BY creates a NEW sequence automatically:

CREATE TABLE tab4 (
    col1 BIGINT NOT NULL AUTO_INCREMENT,
    col2 VARCHAR(16),
    PRIMARY KEY(col1)
) PARTITION BY KEY(col1);

Explicit GROUP sequence (AUTO mode)

To use a GROUP sequence in AUTO mode, specify BY GROUP:

CREATE TABLE tab5 (
    col1 BIGINT NOT NULL AUTO_INCREMENT BY GROUP,
    col2 VARCHAR(16),
    PRIMARY KEY(col1)
) PARTITION BY HASH(col1);

TIME sequence (DRDS mode)

CREATE TABLE tab3 (
    col1 BIGINT NOT NULL AUTO_INCREMENT BY TIME,
    col2 VARCHAR(16),
    PRIMARY KEY(col1)
) DBPARTITION BY HASH(col1);

Unit sequences for multi-instance deployment

To coordinate a single GROUP sequence across three instances or databases, create each table with the same UNIT COUNT and a unique INDEX:

Instance 1 (INDEX 0)

CREATE TABLE tab2 (
    col1 BIGINT NOT NULL AUTO_INCREMENT UNIT COUNT 3 INDEX 0,
    col2 VARCHAR(16),
    PRIMARY KEY(col1)
) DBPARTITION BY HASH(col1);

Instance 2 (INDEX 1)

CREATE TABLE tab2 (
    col1 BIGINT NOT NULL AUTO_INCREMENT UNIT COUNT 3 INDEX 1,
    col2 VARCHAR(16),
    PRIMARY KEY(col1)
) DBPARTITION BY HASH(col1);

Instance 3 (INDEX 2)

CREATE TABLE tab2 (
    col1 BIGINT NOT NULL AUTO_INCREMENT UNIT COUNT 3 INDEX 2,
    col2 VARCHAR(16),
    PRIMARY KEY(col1)
) DBPARTITION BY HASH(col1);

Each instance generates values from a non-overlapping subset of the overall GROUP sequence, preventing ID conflicts across instances.