This topic describes how to query the information and the sequence type of a table that uses a sequence to generate values for an auto-increment column.

SHOW CREATE TABLE

The 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>
Note
  • SHOW CREATE TABLE returns only the type of the sequence and does not return the sequence details. To query the sequence details, execute the SHOW 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 by SHOW CREATE TABLE based on the CREATE TABLE syntax.

Examples

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 SEQUENCES

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