This topic describes how to query the type of the sequence that is used for a table and the details of the sequences that are used for tables in the database.

SHOW CREATE TABLE

You can use the SHOW CREATE TABLE statement to query the information about a specified table. If the table is a sharded table or a broadcast table, the SHOW CREATE TABLE statement can returns the type of the sequence that is used to generate values for an auto-increment column of table.

Use the following syntax to query information about a table:

SHOW CREATE TABLE <name>
Note
  • The SHOW CREATE TABLE statement returns only the type of the sequence that is used for the specified table and does not return the details of the sequence. If you want to query the details of a sequence, use the SHOW SEQUENCES statement.
  • For a table that uses a unit group sequence, the SHOW CREATE TABLE statement returns a DDL statement that does not contain the number of units in the unit group sequence and the index of the unit sequence that is used for the table. Therefore, you cannot use the returned DDL statement to create a table that uses a unit sequence of the same unit group sequence.
  • If you want to create a table that uses a unit sequence of the same unit group sequence to which the sequence of another table belongs, use the SHOW SEQUENCES statement to query the number of units in the unit group sequence and the unit index of the other unit sequence. Then, modify the DDL statement that is returned by the SHOW CREATE TABLE statement based on the CREATE TABLE syntax.

Examples

Example 1: When the tab1 table is created, an auto-increment column is specified and the type of the sequence that is used for the table is not specified. In this case, a group sequence is created for the table.

You can use the following statement to query the information about the tab1 table:

mysql> SHOW CREATE TABLE tab1;

In this example, the following information 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 the tab2 table is created, the number of units and the unit index are specified for an auto-increment column. In this case, the tab2 table uses a unit sequence of a unit group sequence. The SHOW CREATE TABLE statement does not return the number of units or the unit index. The returned DDL statement cannot be executed to create a table that uses a unit sequence of the same unit group sequence to which the unit sequence that is used by the tab2 table belongs.

You can use the following statement to query the information about the tab2 table:

mysql> SHOW CREATE TABLE tab2;

In this example, the following information 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 the tab3 table is created, a sequence of the BY TIME type is specified for an auto-increment column. In this case, the tab3 table uses a time-based sequence.

You can use the following statement to query the information about the tab3 table:

mysql> SHOW CREATE TABLE tab3;

In this example, the following information 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 the tab4 table is created, a sequence of the BY SIMPLE type is specified for an auto-increment column. In this case, the tab4 table uses a simple sequence.

You can use the following statement to query the information about the tab4 table:

mysql> SHOW CREATE TABLE tab4;

In this example, the following information 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 use the SHOW SEQUENCES statement to query the name and details of the sequences that are used for tables in a database.

You can use the following statement to query the details of each sequence that is used for the tables in the database: 

mysql> SHOW SEQUENCES;

In this example, the following information 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)