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>
- 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 theSHOW 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 theSHOW CREATE TABLE
statement based on theCREATE 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)