CREATE TABLE

After defining AUTO_INCREMENT for the primary key of a partitioned table or broadcast table, a Sequence can be used to automatically fill in the primary key, which is maintained by PolarDB-X.

The standard syntax for creating tables has been expanded to include the Sequence type for the auto-increment column; if no type keyword is specified, the default type is GROUP. The names of Sequences automatically created by PolarDB-X and associated with tables are prefixed with AUTO_SEQ_, followed by the table name.

Group Sequence, Time-based Sequence, or Simple Sequence

The syntax for creating a table with a Group Sequence, Time-based Sequence, or Simple Sequence as the auto-increment column is as follows:

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

Note: If BY TIME is specified, i.e., a Time-based Sequence, then the column type must be BIGINT.

Sharded Group Sequence

The syntax for creating a table with a sharded Group Sequence is as follows:

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

Examples

Example 1: Default creation of a table using Group Sequence as the auto-increment column. Enter the following code on the command line:

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

Example 2: Creation of 3 identically named tables using the same unit count and different unit indexes as sharded Group Sequences for the auto-increment column, each for a different instance or database.

1. For instance 1/database 1, enter the following code on the command line:

   ```sql 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. For instance 2/database 2, enter the following code on the command line:

   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. For instance 3/database 3, enter the following code on the command line:

   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: Creation of a table using Time-based Sequence as the auto-increment column. Enter the following code on the command line:

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

Example 4: Creation of a table using Simple Sequence as the auto-increment column. Enter the following code on the command line:

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