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.
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>
BY TIME
that represents the time-based sequence type, the data type of the specified column must be BIGINT.
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.
- 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);
- 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);
- 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);
- Create a table in Instance 1 or Database 1.
- 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>
- 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 theALTER 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 TABLEThe 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>
SHOW CREATE TABLE
returns only the type of the sequence and does not return the sequence details. To query the sequence details, execute theSHOW 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 bySHOW CREATE TABLE
based on theCREATE TABLE
syntax.
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;
The following result is returned:
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tab1 | CREATE TABLE `tab1` (
`col1` bigint(20) NOT NULL AUTO_INCREMENT BY GROUP,
`col2` varchar(16) DEFAULT NULL,
PRIMARY KEY (`col1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 dbpartition by hash(`col1`) |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.02 sec)
Example 2: When you were creating the tab2 table, you specified the number of units
and the unit index for an unit group sequence that is used for an auto-increment
column. However, SHOW CREATE TABLE
does not return the number of units or the unit index. The returned DDL statement
cannot be executed to create a table and define the table to use an unit group sequence that have the same unitization capability as the sequence used by the tab2 table.
Execute the following statement in your CLI to query the information about the tab2 table:
mysql> SHOW CREATE TABLE tab2;
The following result is returned:
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tab2 | CREATE TABLE `tab2` (
`col1` bigint(20) NOT NULL AUTO_INCREMENT BY GROUP,
`col2` varchar(16) DEFAULT NULL,
PRIMARY KEY (`col1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 dbpartition by hash(`col1`) |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
Example 3: When you were creating the tab3 table, you specified a sequence of the
BY TIME
type for an auto-increment
column. This means that you specified a time-based sequence.
Execute the following statement in your CLI to query the information about the tab3 table:
mysql> SHOW CREATE TABLE tab3;
The following result is returned:
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tab3 | CREATE TABLE `tab3` (
`col1` bigint(20) NOT NULL AUTO_INCREMENT BY TIME,
`col2` varchar(16) DEFAULT NULL,
PRIMARY KEY (`col1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 dbpartition by hash(`col1`) |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
Example 4: When you were creating the tab4 table, you specified a sequence of the
BY SIMPLE
type for an auto-increment
column. This means that you specified a simple sequence.
Execute the following statement in your CLI to query the information about the tab4 table:
mysql> SHOW CREATE TABLE tab4;
The following result is returned:
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tab3 | CREATE TABLE `tab4` (
`col1` bigint(20) NOT NULL AUTO_INCREMENT BY TIME,
`col2` varchar(16) DEFAULT NULL,
PRIMARY KEY (`col1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 dbpartition by hash(`col1`) |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
SHOW SEQUENCESYou can execute the SHOW SEQUENCES
statement to query the name and details of the sequences in the tables of a database.
Execute the following statement in your CLI to query the names and details of sequences in the tables of a database:
mysql> SHOW SEQUENCES;
The following result is returned:
+---------------+--------+------------+------------+------------+--------------+------------+---------------------+-------+--------+
| NAME | VALUE | UNIT_COUNT | UNIT_INDEX | INNER_STEP | INCREMENT_BY | START_WITH | MAX_VALUE | CYCLE | TYPE |
+---------------+--------+------------+------------+------------+--------------+------------+---------------------+-------+--------+
| seq1 | 100000 | 1 | 0 | 100000 | N/A | N/A | N/A | N/A | GROUP |
| seq2 | 400000 | 3 | 1 | 100000 | N/A | N/A | N/A | N/A | GROUP |
| seq3 | N/A | N/A | N/A | N/A | N/A | N/A | N/A | N/A | TIME |
| seq4 | 1006 | N/A | N/A | N/A | 2 | 1000 | 99999999999 | N | SIMPLE |
| AUTO_SEQ_tab1 | 100000 | 1 | 0 | 100000 | N/A | N/A | N/A | N/A | GROUP |
| AUTO_SEQ_tab2 | 400000 | 3 | 1 | 100000 | N/A | N/A | N/A | N/A | GROUP |
| AUTO_SEQ_tab3 | N/A | N/A | N/A | N/A | N/A | N/A | N/A | N/A | TIME |
| AUTO_SEQ_tab4 | 2 | N/A | N/A | N/A | 1 | 1 | 9223372036854775807 | N | SIMPLE |
+---------------+--------+------------+------------+------------+--------------+------------+---------------------+-------+--------+
8 rows in set (0.01 sec)