All Products
Search
Document Center

Implicit sequence

Last Updated: Aug 05, 2020

After AUTO_INCREMENT is set for a primary key of a partitioned table or a broadcast table, a sequence can be used to automatically fill in the primary key. The sequence is automatically maintained by Distributed Relational Database Service (DRDS).

CREATE TABLE

The standard CREATE TABLE syntax is extended to add the sequence type for auto-increment columns. If the type keyword is not specified, the default type is GROUP. The table-related sequence names that DRDS creates automatically are prefixed by AUTO_SEQ_ which is followed by the table name.

Group sequence, time-based sequence, and simple sequence

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

Note: The name of the column must be of the BIGINT type if BY TIME (that is, a time-based sequence) is specified.

Unit group sequence

  1. CREATE TABLE <name> (
  2. <column> ... AUTO_INCREMENT [ BY GROUP ] [ UNIT COUNT <numeric value> INDEX <numeric value> ],
  3. <column definition>,
  4. ...
  5. ) ... AUTO_INCREMENT=<start value>

Examples

Example 1: Create a table that uses a group sequence as an AUTO_INCREMENT column by default.

  1. CREATE TABLE tab1 (
  2. col1 BIGINT NOT NULL AUTO_INCREMENT,
  3. col2 VARCHAR(16),
  4. PRIMARY KEY(col1)
  5. ) DBPARTITION BY HASH(col1);

Example 2: Create three same-name tables that use unit group sequences with the same number of units but different unit indexes as AUTO_INCREMENT columns, and assign the three tables to three instances or databases respectively.

Instance 1 or database 1:

  1. CREATE TABLE tab2 (
  2. col1 BIGINT NOT NULL AUTO_INCREMENT UNIT COUNT 3 INDEX 0,
  3. col2 VARCHAR(16),
  4. PRIMARY KEY(col1)
  5. ) DBPARTITION BY HASH(col1);

Instance 2 or database 2:

  1. CREATE TABLE tab2 (
  2. col1 BIGINT NOT NULL AUTO_INCREMENT UNIT COUNT 3 INDEX 1,
  3. col2 VARCHAR(16),
  4. PRIMARY KEY(col1)
  5. ) DBPARTITION BY HASH(col1);

Instance 3 or database 3:

  1. CREATE TABLE tab2 (
  2. col1 BIGINT NOT NULL AUTO_INCREMENT UNIT COUNT 3 INDEX 2,
  3. col2 VARCHAR(16),
  4. PRIMARY KEY(col1)
  5. ) DBPARTITION BY HASH(col1);

Example 3: Create a table that uses a time-based sequence as an AUTO_INCREMENT column.

  1. CREATE TABLE tab3 (
  2. col1 BIGINT NOT NULL AUTO_INCREMENT BY TIME,
  3. col2 VARCHAR(16),
  4. PRIMARY KEY(col1)
  5. ) DBPARTITION BY HASH(col1);

Example 4: Create a table that uses a simple sequence as an AUTO_INCREMENT column.

  1. CREATE TABLE tab4 (
  2. col1 BIGINT NOT NULL AUTO_INCREMENT BY SIMPLE,
  3. col2 VARCHAR(16),
  4. PRIMARY KEY(col1)
  5. ) DBPARTITION BY HASH(col1);

SHOW CREATE TABLE

The sequence type is displayed for the auto-increment column of a partitioned table or broadcast table.

  1. SHOW CREATE TABLE <name>

Notes:

  • The SHOW CREATE TABLE statement queries only the sequence type and does not display the sequence details. To view the details, execute the SHOW SEQUENCES statement.
  • For a table associated with a unit group sequence, the number of units and the unit index are not displayed. You cannot use the data definition languange (DDL) syntax retrieved by executing the SHOW CREATE TABLE statement to create a table that supports unit group sequences. To create a table that supports unit group sequences, you must execute the SHOW SEQUENCES statement to view the number of units and the unit index, and modify the table creation DDL syntax obtained by executing SHOW CREATE TABLE. See CREATE TABLE to modify the table creation DDL syntax.

Examples

Example 1: If AUTO_INCREMENT is set but the sequence type is not specified when a table is created, a group sequence is used by default.

  1. mysql> SHOW CREATE TABLE tab1;
  2. +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  3. | Table | Create Table |
  4. +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  5. | tab1 | CREATE TABLE `tab1` (
  6. `col1` bigint(20) NOT NULL AUTO_INCREMENT BY GROUP,
  7. `col2` varchar(16) DEFAULT NULL,
  8. PRIMARY KEY (`col1`)
  9. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 dbpartition by hash(`col1`) |
  10. +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  11. 1 row in set (0.02 sec)

Example 2: The number of units and the unit index are specified for the AUTO_INCREMENT parameter and a unit group sequence is used when a table is created, but the number of units and the unit index are not displayed after the SHOW CREATE TABLE statement is executed. In this case, you cannot use the DDL syntax to create a table that supports the unit group sequence.

  1. mysql> SHOW CREATE TABLE tab2;
  2. +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  3. | Table | Create Table |
  4. +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  5. | tab2 | CREATE TABLE `tab2` (
  6. `col1` bigint(20) NOT NULL AUTO_INCREMENT BY GROUP,
  7. `col2` varchar(16) DEFAULT NULL,
  8. PRIMARY KEY (`col1`)
  9. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 dbpartition by hash(`col1`) |
  10. +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  11. 1 row in set (0.01 sec)

Example 3: BY TIME is specified for AUTO_INCREMENT when a table is created, that is, a time-based sequence is used.

  1. mysql> SHOW CREATE TABLE tab3;
  2. +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  3. | Table | Create Table |
  4. +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  5. | tab3 | CREATE TABLE `tab3` (
  6. `col1` bigint(20) NOT NULL AUTO_INCREMENT BY TIME,
  7. `col2` varchar(16) DEFAULT NULL,
  8. PRIMARY KEY (`col1`)
  9. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 dbpartition by hash(`col1`) |
  10. +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  11. 1 row in set (0.01 sec)

Example 4: BY SIMPLE is specified for AUTO_INCREMENT when a table is created, that is, a simple sequence is used.

  1. mysql> SHOW CREATE TABLE tab4;
  2. +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  3. | Table | Create Table |
  4. +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  5. | tab3 | CREATE TABLE `tab4` (
  6. `col1` bigint(20) NOT NULL AUTO_INCREMENT BY SIMPLE,
  7. `col2` varchar(16) DEFAULT NULL,
  8. PRIMARY KEY (`col1`)
  9. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 dbpartition by hash(`col1`) |
  10. +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  11. 1 row in set (0.01 sec)

SHOW SEQUENCES

After the table is created, you can execute the SHOW SEQUENCES statement to view the sequence name and details.

  1. mysql> SHOW SEQUENCES;
  2. +---------------+--------+------------+------------+------------+--------------+------------+---------------------+-------+--------+
  3. | NAME | VALUE | UNIT_COUNT | UNIT_INDEX | INNER_STEP | INCREMENT_BY | START_WITH | MAX_VALUE | CYCLE | TYPE |
  4. +---------------+--------+------------+------------+------------+--------------+------------+---------------------+-------+--------+
  5. | seq1 | 100000 | 1 | 0 | 100000 | N/A | N/A | N/A | N/A | GROUP |
  6. | seq2 | 400000 | 3 | 1 | 100000 | N/A | N/A | N/A | N/A | GROUP |
  7. | seq3 | N/A | N/A | N/A | N/A | N/A | N/A | N/A | N/A | TIME |
  8. | seq4 | 1006 | N/A | N/A | N/A | 2 | 1000 | 99999999999 | N | SIMPLE |
  9. | AUTO_SEQ_tab1 | 100000 | 1 | 0 | 100000 | N/A | N/A | N/A | N/A | GROUP |
  10. | AUTO_SEQ_tab2 | 400000 | 3 | 1 | 100000 | N/A | N/A | N/A | N/A | GROUP |
  11. | AUTO_SEQ_tab3 | N/A | N/A | N/A | N/A | N/A | N/A | N/A | N/A | TIME |
  12. | AUTO_SEQ_tab4 | 2 | N/A | N/A | N/A | 1 | 1 | 9223372036854775807 | N | SIMPLE |
  13. +---------------+--------+------------+------------+------------+--------------+------------+---------------------+-------+--------+
  14. 8 rows in set (0.01 sec)

ALTER TABLE

The sequence type cannot be changed by executing the ALTER TABLE statement, but the start value can be modified. To modify the sequence type in a table, execute the SHOW SEQUENCES statement to view the sequence name and type, and then execute the ALTER SEQUENCE statement to modify the sequence type.

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

NOTE: Exercise caution when modifying the start value of AUTO_INCREMENT after a DRDS sequence is used. Evaluate the generated sequence values and the speed of generating new sequence values to avoid conflicts.