Documentation
Home Page PolarDB PolarDB for Xscale PolarDB-X 1.0 (DRDS) SQL Reference Sequence Implicit usage

Implicit usage

Updated at:
Copy as MD
Product

When you add AUTO_INCREMENT to a primary key column in a table shard or broadcast table, PolarDB-X 1.0 (Distributed Relational Database Service) automatically creates and maintains a sequence to generate unique primary key values. The auto-created sequence is named AUTO_SEQ_<table_name>.

PolarDB-X 1.0 extends the standard CREATE TABLE syntax so you can specify the sequence type inline. The default type is GROUP.

Sequence types

Type Keyword ID ordering Use when
Group sequence BY GROUP (default) Globally unique; may not be monotonically increasing across nodes (IDs are allocated in batches per node) General-purpose auto-increment. Highest throughput.
Time-based sequence BY TIME Time-ordered. Column must be BIGINT. You need IDs that reflect insertion order by time.
Simple sequence BY SIMPLE Strictly incremental. Lower throughput than GROUP. You need strictly ordered, gap-free IDs.
Unit group sequence BY GROUP + UNIT COUNT … INDEX … Non-overlapping ID ranges per instance You run multiple PolarDB-X 1.0 instances and need to merge data across them without ID conflicts.
Note

GROUP sequence IDs are globally unique but may appear non-consecutive within a single session. This is expected: PolarDB-X 1.0 allocates IDs in batches per node for performance. Each node draws from its own batch, so values are unique but not strictly ordered across nodes.

Create a table with an implicit sequence

Group, time-based, or simple sequence

CREATE TABLE <name> (
   <column> ... AUTO_INCREMENT [ BY GROUP | BY SIMPLE | BY TIME ],
   <column_definition>,
   ...
) ... AUTO_INCREMENT=<start_value>
Note

BY TIME requires the column data type to be BIGINT.

Examples

Group sequence (default — no keyword required):

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

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

Simple sequence:

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

Unit group sequence

Use a unit group sequence when multiple PolarDB-X 1.0 instances write to separate databases and you later merge the data. Each instance gets a non-overlapping slice of the ID space, so primary keys stay unique after merging.

CREATE TABLE <name> (
   <column> ... AUTO_INCREMENT [ BY GROUP ] UNIT COUNT <total_units> INDEX <unit_index>,
   <column_definition>,
   ...
) ... AUTO_INCREMENT=<start_value>
Parameter Description
UNIT COUNT Total number of instances sharing this sequence
INDEX Zero-based index of this instance (0 to UNIT COUNT - 1)

Example: three instances sharing the same ID space

All three tables share the name tab2 and use UNIT COUNT 3. Each table gets a different INDEX.

Instance 1 (INDEX 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);

Instance 2 (INDEX 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);

Instance 3 (INDEX 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);

Change the sequence start value

ALTER TABLE cannot change the sequence type. Use it only to update the start value:

ALTER TABLE <name> AUTO_INCREMENT=<start_value>
Warning

After a sequence has generated values, avoid changing the start value. If you must change it, analyze the current sequence value and generation rate before setting a new start value to prevent duplicate IDs.

To change the sequence type, run SHOW SEQUENCES to get the sequence name, then run ALTER SEQUENCE.

Query sequence information

SHOW CREATE TABLE

SHOW CREATE TABLE returns the sequence type for the auto-increment column.

SHOW CREATE TABLE <name>
Important

SHOW CREATE TABLE returns only the sequence type, not sequence details such as UNIT COUNT or UNIT INDEX. For unit group sequences, the returned DDL statement cannot recreate the sequence with the same unitization configuration. To get the full unit count and index values, run SHOW SEQUENCES and incorporate those values into your CREATE TABLE statement manually.

Examples

tab1 was created without a sequence type keyword, so it uses the default GROUP sequence:

mysql> SHOW CREATE TABLE tab1;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 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)

tab2 was created with UNIT COUNT 3 INDEX 1, but SHOW CREATE TABLE does not include those values:

mysql> SHOW CREATE TABLE tab2;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 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)

tab3 uses a time-based sequence:

mysql> SHOW CREATE TABLE tab3;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 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)

tab4 uses a simple sequence:

mysql> SHOW CREATE TABLE tab4;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 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 SEQUENCES

SHOW SEQUENCES returns the name and full details of every sequence in the current database, including UNIT_COUNT, UNIT_INDEX, INNER_STEP, and other properties not visible in SHOW CREATE TABLE.

mysql> SHOW SEQUENCES;
+---------------+--------+------------+------------+------------+--------------+------------+---------------------+-------+--------+
| 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)

Output columns:

Column Description
NAME Sequence name. Auto-created sequences use the AUTO_SEQ_<table_name> format.
VALUE Current sequence value.
UNIT_COUNT Total number of units (unit group sequences only).
UNIT_INDEX Index of this unit (unit group sequences only).
INNER_STEP Batch size for ID allocation per node (group sequences only).
INCREMENT_BY Step size between consecutive values (simple sequences only).
START_WITH Starting value (simple sequences only).
MAX_VALUE Maximum allowed value (simple sequences only).
CYCLE Whether the sequence restarts after reaching MAX_VALUE (Y/N).
TYPE Sequence type: GROUP, TIME, or SIMPLE.
Previous：Explicit sequencesNext：Outline