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.Distributed Relational Database Service (DRDS) automatically maintains the sequence.

The standard CREATE TABLE syntax is extended, so that you can add the sequence type for an auto-increment column. If you do not specify a type, the default type is used. The default type is GROUP. If a sequence is automatically created by DRDS 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 an 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 an 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 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 to create a table that uses a unit group sequence and set the unit index of the sequence to 0:

    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 to create a table that uses a unit group sequence and set the unit index of the sequence to 1:

    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 to create a table that uses a unit group sequence and set the unit index of the sequence to 2:

    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 to create a table that uses a time-based sequence:

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 to create a table that uses a simple sequence:

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