Explicit sequences

Group sequence

A group sequence generates globally unique values across a distributed cluster. If you create a sequence without specifying a type, PolarDB-X 1.0 creates a group sequence by default.

Syntax:

CREATE [GROUP] SEQUENCE <name>
  [START WITH <numeric value>]

Parameters:

Examples:

-- Both statements create a group sequence
mysql> CREATE SEQUENCE seq1;
mysql> CREATE GROUP SEQUENCE seq1;

Unit group sequence

A unit group sequence partitions a global sequence across multiple instances or databases. Each unit produces values in a non-overlapping range, ensuring global uniqueness when all units use the same sequence name and unit count.

A group sequence is a special case of a unit group sequence with UNIT COUNT 1 and INDEX 0.

Syntax:

CREATE [GROUP] SEQUENCE <name>
  [START WITH <numeric value>]
  [UNIT COUNT <numeric value> INDEX <numeric value>]

Parameters:

Example: Create a globally unique sequence across three instances.

Create one unit group sequence per instance, using the same name and unit count but a different index:

-- Instance 1 or Database 1
mysql> CREATE GROUP SEQUENCE seq2 UNIT COUNT 3 INDEX 0;

-- Instance 2 or Database 2
mysql> CREATE GROUP SEQUENCE seq2 UNIT COUNT 3 INDEX 1;

-- Instance 3 or Database 3
mysql> CREATE GROUP SEQUENCE seq2 UNIT COUNT 3 INDEX 2;

Time-based sequence

A time-based sequence generates values based on timestamps, producing time-ordered IDs.

Syntax:

CREATE TIME SEQUENCE <name>

Example:

mysql> CREATE TIME SEQUENCE seq3;

Simple sequence

A simple sequence generates consecutive values with configurable increments, bounds, and cycling behavior. Use simple sequences when your application requires strictly consecutive IDs.

Syntax:

CREATE SIMPLE SEQUENCE <name>
  [START WITH <numeric value>]
  [INCREMENT BY <numeric value>]
  [MAXVALUE <numeric value>]
  [CYCLE | NOCYCLE]

Parameters:

Example: Create a simple sequence starting at 1000, incrementing by 2, with a maximum of 99999999999, and no cycling:

mysql> CREATE SIMPLE SEQUENCE seq4 START WITH 1000 INCREMENT BY 2 MAXVALUE 99999999999 NOCYCLE;

Group sequence

Syntax:

ALTER SEQUENCE <name> [CHANGE TO SIMPLE | TIME]
  START WITH <numeric value>
  [INCREMENT BY <numeric value>]
  [MAXVALUE <numeric value>]
  [CYCLE | NOCYCLE]

Parameters:

Unit group sequence

Syntax:

ALTER SEQUENCE <name>
  START WITH <numeric value>

Parameters:

You cannot convert a unit group sequence to another type or modify parameters other than START WITH.

Time-based sequence

Syntax:

ALTER SEQUENCE <name> [CHANGE TO GROUP | SIMPLE]
  START WITH <numeric value>
  [INCREMENT BY <numeric value>]
  [MAXVALUE <numeric value>]
  [CYCLE | NOCYCLE]

Parameters:

Simple sequence

Syntax:

ALTER SEQUENCE <name> [CHANGE TO GROUP | TIME]
  START WITH <numeric value>
  [INCREMENT BY <numeric value>]
  [MAXVALUE <numeric value>]
  [CYCLE | NOCYCLE]

Parameters:

Examples

Modify a simple sequence — set START WITH to 3000, INCREMENT BY to 5, MAXVALUE to 1000000, and switch to CYCLE:

mysql> ALTER SEQUENCE seq4 START WITH 3000 INCREMENT BY 5 MAXVALUE 1000000 CYCLE;

Convert a group sequence to a simple sequence:

mysql> ALTER SEQUENCE seq1 CHANGE TO SIMPLE START WITH 1000000;

List all sequences

Run SHOW SEQUENCES to view all sequences and their current state:

mysql> SHOW SEQUENCES;

Example output:

+------+--------+------------+------------+------------+--------------+------------+-------------+-------+--------+
| 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 |
+------+--------+------------+------------+------------+--------------+------------+-------------+-------+--------+
4 rows in set (0.00 sec)

The TYPE column shows abbreviated sequence type names.

Get the next sequence value

Use <sequence_name>.NEXTVAL to retrieve the next value from a sequence.

Syntax:

[<schema_name>.]<sequence_name>.NEXTVAL

Get a single value:

mysql> SELECT sample_seq.nextval FROM dual;

Output:

+--------------------+
| SAMPLE_SEQ.NEXTVAL |
+--------------------+
|             101001 |
+--------------------+
1 row in set (0.04 sec)

Use in an INSERT statement:

mysql> INSERT INTO some_users (name, address, gmt_create, gmt_modified, intro)
       VALUES ('sun', sample_seq.nextval, now(), now(), 'aa');

sample_seq.nextval is evaluated as a value within the SQL statement. If the table was created with AUTO_INCREMENT, you do not need to specify the auto-increment column — PolarDB-X 1.0 manages it automatically.

Get multiple values at once:

SELECT [<schema_name>.]<sequence_name>.NEXTVAL FROM DUAL WHERE COUNT = <numeric value>

Example — retrieve 10 values:

mysql> SELECT sample_seq.nextval FROM dual WHERE count = 10;

Output:

+--------------------+
| SAMPLE_SEQ.NEXTVAL |
+--------------------+
|             101002 |
|             101003 |
|             101004 |
|             101005 |
|             101006 |
|             101007 |
|             101008 |
|             101009 |
|             101010 |
|             101011 |
+--------------------+
10 rows in set (0.04 sec)