After you specify the AUTO_INCREMENT attribute for a primary key column in a table shard or a broadcast table, a sequence can be used to automatically generate primary key values. Sequences are automatically maintained by PolarDB-X.

The standard CREATE TABLE syntax is extended. This way, you can specify the sequence type for an auto-increment column. If you do not specify a type, the default type GROUP is used. If a sequence is automatically created by PolarDB-X and associated with a table, the sequence name consists of the AUTO_SEQ_ prefix and the table name.

Group sequence, time-based sequence, or simple sequence

You can use the following syntax to create a table that uses a group sequence, time-based sequence, or simple sequence for an auto-increment column:

CREATE TABLE <name> (
   <column> ... AUTO_INCREMENT [ BY GROUP | SIMPLE | TIME ],
   <column definition>,
   ...
) ... AUTO_INCREMENT=<start value>
Note If you set the type to BY TIME that represents the time-based sequence type, the data type of the specified column must be BIGINT.

Unit group sequence

You can use the following syntax to create a table that use a unit group sequence:

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

Examples

Example 1: Create a table that uses a group sequence by default to generate values for an auto-increment column.

Execute the following statement in your CLI:

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

Example 2: Create three tables. Each table uses a unit group sequence to generate values for an auto-increment column. These three tables are created in three instances or databases. The unit group sequences for the three tables have the same table name, the same number of units, and different unit indexes.

  1. Create a table in Instance 1 or Database 1.

    Execute the following statement in your CLI:

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

    Execute the following statement in your CLI:

    mysql> CREATE TABLE tab2 (
    col1 BIGINT NOT NULL AUTO_INCREMENT UNIT COUNT 3 INDEX 1,
    col2 VARCHAR(16),
    PRIMARY KEY(col1)
    ) DBPARTITION BY HASH(col1);
  3. Create a table in Instance 3 or Database 3.

    Execute the following statement in your CLI:

    mysql> 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: Create a table that uses a time-based sequence to generate values for an auto-increment column.

Execute the following statement in your CLI:

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

Example 4: Create a table that uses a simple sequence to generate values for an auto-increment column.

Execute the following statement in your CLI:

mysql> CREATE TABLE tab4 ( 
col1 BIGINT NOT NULL AUTO_INCREMENT BY SIMPLE, 
col2 VARCHAR(16), 
PRIMARY KEY(col1)
) DBPARTITION BY HASH(col1);