Implicit usage-PolarDB(PolarDB)-阿里云帮助中心

This topic describes the implicit usage of sequences.

Create a sequence

After you define AUTO_INCREMENT for the primary key of a table shard or a broadcast table, a sequence automatically populates the primary key. This sequence is automatically maintained by PolarDB-X 1.0.

The standard table creation syntax is extended to add the Sequence type for auto-increment columns. If a type keyword is not specified, the default type is GROUP. The names of sequences that are automatically created by PolarDB-X 1.0 and associated with a table are prefixed with AUTO_SEQ_, followed by the table name.

Create a group sequence, time-based sequence, or simple sequence

Syntax

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

Note If you specify BY TIME (a time-based sequence), the column type must be BIGINT.

Create a unit group sequence

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: Create a table that uses a group sequence by default for its auto-increment column.

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 that have the same name. Each table uses a unit group sequence with the same number of units but a different unit index for its auto-increment column. The tables are used in three different instances or databases.

Instance 1/Database 1

Run the following command:

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);

Instance 2/Database 2

Run the following command:

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);

Instance 3/Database 3

Run the following command:

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 for its auto-increment column.

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 for its auto-increment column.

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

Modify a sequence

You cannot use the ALTER TABLE statement to change the type of an associated sequence. However, you can use the ALTER TABLE statement to change the start value:

ALTER TABLE <name> ... AUTO_INCREMENT=<start value>

Note

To change the type of a sequence associated with a table, run the SHOW SEQUENCES command to find the exact sequence name and type. Then, run the ALTER SEQUENCE command to modify it.
After you start using a sequence, exercise caution when you modify the AUTO_INCREMENT start value. Evaluate the existing sequence values and the rate at which new values are generated to prevent conflicts.

View table and sequence information

SHOW CREATE TABLE

For a table shard or a broadcast table, this statement shows the sequence type of the auto-increment column.

To view the CREATE TABLE statement for a table, use the following syntax:

SHOW CREATE TABLE <name>

Note

The SHOW CREATE TABLE statement shows only the associated sequence type, not its details. To view the sequence details, run the SHOW SEQUENCES command.
For a table associated with a unit group sequence, the SHOW CREATE TABLE output does not show the number of units or the unit index. Therefore, you cannot use the returned DDL statement to create a table with the same unit group sequence configuration.
To create a table with the same unit group sequence configuration, you must first run the SHOW SEQUENCES command to find the number of units and the unit index. Then, modify the DDL statement returned by SHOW CREATE TABLE according to the CREATE TABLE syntax.

Examples

Example 1: When the table is created, AUTO_INCREMENT is specified without a sequence type keyword, so a group sequence is used by default.

mysql> SHOW CREATE TABLE tab1;