查看表信息及相关Sequence类型
本文主要介绍如何对使用Sequence类型作为自增列的表信息以及相关Sequence类型进行查看。
SHOW CREATE TABLE
当表为拆分表或者广播表时，显示自增列Sequence的类型。
查看已创建的表语法如下：
SHOW CREATE TABLE <name>
说明
SHOW CREATE TABLE仅显示相关Sequence的类型，并不显示Sequence详细信息，如需查看，请使用SHOW SEQUENCES命令。关联了单元化Group Sequence的表并不显示单元数量和单元索引，因此不能将
SHOW CREATE TABLE显示的DDL直接用于创建具备同样 单元化Group Sequence 能力的表。如果需要创建具备同样单元化能力的表，必须使用
SHOW SEQUENCES查看单元数量和单元索引，然后参照CREATE TABLE的语法修改通过SHOW CREATE TABLE获取的建表DDL。
示例
示例一：建表时指定AUTO_INCREMENT，但没有指定Sequence类型关键字，则默认使用 Group Sequence 。
请在命令行输入如下代码：
mysql> SHOW CREATE TABLE tab1;
返回结果如下：
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 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)
示例二：建表时为AUTO_INCREMENT指定了单元数量和单元索引，使用 单元化 Group Sequence ，但SHOW CREATE TABLE时并不显示单元数量和单元索引，不能将此DDL用于创建具备同样 单元化 Group Sequence 能力的表。
请在命令行输入如下代码：
mysql> SHOW CREATE TABLE tab2;
返回结果如下：
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 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)
示例三：建表时为AUTO_INCREMENT指定了BY TIME，即 Time-based Sequence 类型。
请在命令行输入如下代码：
mysql> SHOW CREATE TABLE tab3;
返回结果如下：
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 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)
示例四：建表时为AUTO_INCREMENT指定了BY SIMPLE，即 Simple Sequence 类型。
请在命令行输入如下代码：
mysql> SHOW CREATE TABLE tab4;
返回结果如下：
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 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
建表后相关的Sequence名称和详细信息，可通过SHOW SEQUENCES查看。
请在命令行输入如下代码：
mysql> SHOW SEQUENCES;
返回结果如下：
+---------------+--------+------------+------------+------------+--------------+------------+---------------------+-------+--------+
| 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)