Implicit sequences - PolarDB - Alibaba Cloud Documentation Center

Create a sequence

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 (PolarDB-X 1.0) 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.
```
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);
```

ALTER TABLE

ALTER TABLE cannot be used to change the type of a sequence. The following ALTER TABLE syntax can be used to change the start value of a sequence:

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

Note

To change the sequence type of a table, execute the SHOW SEQUENCES statement to check the sequence name and the sequence type, and then execute the ALTER SEQUENCE statement to change the sequence type.
After a sequence is used, we recommend that you do not modify the start value specified for the AUTO_INCREMENT parameter. If you need to modify the start value, analyze the existing sequence values and the rate of generating sequence values to prevent duplicate sequence values from being generated.

Query the information and sequence types of a table

SHOW CREATE TABLE

The SHOW CREATE TABLE statement returns the type of the sequence that is used to generate values for an auto-increment column in a table shard or a broadcast table.

Use the following syntax to query the information about a table:

SHOW CREATE TABLE <name>

Note

SHOW CREATE TABLE returns only the type of the sequence and does not return the sequence details. To query the sequence details, execute the SHOW SEQUENCES statement.
For a table that uses an unit group sequence, SHOW CREATE TABLE returns a DDL statement that does not contain the number of units and the unit index of the unit group sequence. Therefore, you cannot execute this DDL statement to create a table and define the table to use an unit group sequence that has the same unitization capability of the returned sequence.
If you need to create a table and define the table to use an unit group sequence that has the same unitization capability of the sequence of another table, you must execute the SHOW SEQUENCES statement to query the number of units and the unit index. Then, modify the DDL statement that is returned by SHOW CREATE TABLE based on the CREATE TABLE syntax.

Examples

Example 1: When you were creating the tab1 table, you did not specify a sequence type for the auto-increment column. A group sequence is used by default.

Execute the following statement in your CLI to query the information about the tab1 table:

mysql> SHOW CREATE TABLE tab1;