Query information about the sequence that is associated with a table

This topic describes how to query information about the sequence that is associated with the auto-increment column in a table.

SHOW CREATE TABLE

If the table is a sharded table or broadcast table in a database that is in DRDS mode, you can execute the SHOW CREATE TABLE statement to query the type of the sequence that is associated with the auto-increment column in the table. If the table is created in a database that is in AUTO mode, the SHOW CREATE TABLE statement does not return the type of the sequence that is associated with the auto-increment column in the table. By default, a NEW sequence is associated with the auto-increment column in a table that is created in a database in AUTO mode.

Syntax:

SHOW CREATE TABLE <name>

Note

• The SHOW CREATE TABLE statement returns only information about the type of the sequence that is associated with the auto-increment column in the table and does not return the details of the sequence. If you want to query the details of a sequence, execute the SHOW SEQUENCES statement.

• If the sequence that is associated with the auto-increment column in the table is a unit sequence of a GROUP sequence, the SHOW CREATE TABLE statement returns a DDL statement that does not contain the number of units in the GROUP sequence or the index of the unit sequence. Therefore, you cannot use the returned DDL statement to create a table that uses a unit sequence of the same GROUP sequence.

• If you want to create a table that uses a unit sequence of the same GROUP sequence to generate values for the auto-increment column in the table, use the SHOW SEQUENCES statement to query the number of units in the GROUP sequence and the unit indexes of other unit sequences of the GROUP sequence. Then, modify the DDL statement that is returned by the SHOW CREATE TABLE statement based on the CREATE TABLE syntax.

Examples

• Example 1When the tab1 table is created in a database that is in DRDS mode, an auto-increment column is specified and the type of the sequence that is used to generate values for the auto-increment column is not specified. In this case, a GROUP sequence is created for the auto-increment column. You can execute the following statement to query information about the table:

  SHOW CREATE TABLE tab1\G
  

The following result is returned:

  *************************** 1. row ***************************
         Table: tab1
  Create Table: CREATE TABLE `tab1` (
      `col1` bigint(20) NOT NULL AUTO_INCREMENT BY GROUP,
      `col2` varchar(16) DEFAULT NULL,
      PRIMARY KEY (`col1`)
  ) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 DEFAULT COLLATE = utf8mb4_0900_ai_ci  dbpartition by hash(`col1`)

• Example 2The tab2 table is created in a database that is in DRDS mode, and the BY TIME keyword that specifies the time-based sequence type is specified for the auto-increment column in the table. You can execute the following statement to query information about the table:

  SHOW CREATE TABLE tab2;
  

The following result is returned:

  *************************** 1. row ***************************
         Table: tab2
  Create Table: CREATE TABLE `tab2` (
      `col1` bigint(20) NOT NULL AUTO_INCREMENT BY TIME,
      `col2` varchar(16) DEFAULT NULL,
      PRIMARY KEY (`col1`)
  ) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 DEFAULT COLLATE = utf8mb4_0900_ai_ci  dbpartition by hash(`col1`)

• Example 3The tab3 table is created in a database that is in AUTO mode, and no sequence type is specified for the auto-increment column in the table. In this case, a NEW sequence is created and associated with the auto-increment column by default. You can execute the following statement to query information about the table:

  SHOW FULL CREATE TABLE tab3\G
  

The following result is returned:

  *************************** 1. row ***************************
         TABLE: tab3
  CREATE TABLE: CREATE PARTITION TABLE `tab3` (
      `col1` int(11) NOT NULL AUTO_INCREMENT,
      `col2` varchar(16) DEFAULT NULL,
      PRIMARY KEY (`col1`)
  ) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 DEFAULT COLLATE = utf8mb4_0900_ai_ci
  PARTITION BY KEY(`col1`)
  PARTITIONS 3
  /* tablegroup = `tg222` */

SHOW SEQUENCES

You can execute the SHOW SEQUENCES statement to query the names and other details of sequences that are associated with auto-increment columns in tables in the current database. For information about the syntax of the SHOW SEQUENCES statement, see SHOW SEQUENCES.

You can execute the following statement to query details of sequences:

SHOW SEQUENCES;

The following result is returned:

+-------------+---------------+-------+------------+------------+------------+--------------+------------+-----------+-------+-------+--------------+
| SCHEMA_NAME | NAME          | VALUE | UNIT_COUNT | UNIT_INDEX | INNER_STEP | INCREMENT_BY | START_WITH | MAX_VALUE | CYCLE | TYPE  | PHY_SEQ_NAME |
+-------------+---------------+-------+------------+------------+------------+--------------+------------+-----------+-------+-------+--------------+
| ddltest     | AUTO_SEQ_tab2 | N/A   | N/A        | N/A        | N/A        | N/A          | N/A        | N/A       | N/A   | TIME  | N/A          |
| ddltest     | seq2          | N/A   | N/A        | N/A        | N/A        | N/A          | N/A        | N/A       | N/A   | TIME  | N/A          |
| ddltest     | AUTO_SEQ_tab1 | 0     | 1          | 0          | 100000     | N/A          | N/A        | N/A       | N/A   | GROUP | N/A          |
| ddltest     | seq1          | 0     | 1          | 0          | 100000     | N/A          | N/A        | N/A       | N/A   | GROUP | N/A          |
+-------------+---------------+-------+------------+------------+------------+--------------+------------+-----------+-------+-------+--------------+