PolarDB-X can automatically create a sequence and associate the sequence with the auto-increment column in a table. If you do not specify a sequence type when you create a table that contains an auto-increment column, PolarDB-X creates a sequence of a specific type based on the partitioning mode of the database that contains the table. If the table belongs to a database that is in AUTO mode, PolarDB-X automatically creates a NEW sequence. If the table belongs to a database that is in DRDS mode, PolarDB-X automatically creates a GROUP sequence.

The name of the sequence that is associated with the auto-increment column in a table is specified in the AUTO_SEQ_Table name format.

PolarDB-X also allows you to specify the type of sequence that you want to associate with the auto-increment column in a table when you create the table.

Associate a NEW sequence or time-based (TIME) sequence with the auto-increment column in a table

Syntax:

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.

Associate a GROUP sequence with the auto-increment column in a table

Syntax:

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

Examples

  • Example 1

    You can execute the following statement to create a table that uses a default GROUP sequence to generate values for the auto-increment column in a database in DRDS mode: 

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

    You can execute the following statements to create tables that use the same table name in three different instances or three different databases. The auto-increment column in each table uses a unit sequence that belongs to the same GROUP sequence to generate values. The values of the UNIT COUNT parameter in the definitions of the unit sequences are the same, and the values of the INDEX parameter are different.

    • Create a unit sequence in Instance 1 or Database 1. 
      CREATE TABLE tab2 (
col1 BIGINT NOT NULL AUTO_INCREMENT UNIT COUNT 3 INDEX 0,
col2 VARCHAR(16),
PRIMARY KEY(col1)
) DBPARTITION BY HASH(col1);
    • Create a unit sequence in Instance 2 or Database 2. 
      CREATE TABLE tab2 (
col1 BIGINT NOT NULL AUTO_INCREMENT UNIT COUNT 3 INDEX 1,
col2 VARCHAR(16),
PRIMARY KEY(col1)
) DBPARTITION BY HASH(col1);
    • Create a unit sequence in Instance 3 or Database 3. 
      CREATE TABLE tab2 (
col1 BIGINT NOT NULL AUTO_INCREMENT UNIT COUNT 3 INDEX 2,
col2 VARCHAR(16),
PRIMARY KEY(col1)
) DBPARTITION BY HASH(col1);
  • Example 3

    You can execute the following statement to create a table that uses a TIME sequence to generate values for the auto-increment column in a database in DRDS mode: 

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

    You can execute the following statement to create a table that uses a default NEW sequence to generate values for the auto-increment column in a database in AUTO mode: 

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

    You can execute the following statement to create a table that uses a GROUP sequence to generate values for the auto-increment column in a database in AUTO mode: 

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