Sequence 隐式用法
在为拆分表或广播表的主键定义 AUTO_INCREMENT 后,Sequence 可以用于自动填充主键,由 DRDS 自动维护。
CREATE TABLE
扩展标准建表语法,增加了自增列的 Sequence 类型,如果未指定类型关键字,则默认类型为 GROUP。DRDS 自动创建的、跟表相关联的 Sequence 名称,都是以 AUTO_SEQ_ 为前缀,后面加上表名。
Group Sequence、Time-based Sequence 与 Simple Sequence
CREATE TABLE <name> (
<column> ... AUTO_INCREMENT [ BY GROUP | SIMPLE | TIME ],
<column definition>,
...
) ... AUTO_INCREMENT=<start value>
注意:如果指定了 BY TIME,即 Time-based Sequence,则该列类型必须为 BIGINT。
单元化 Group Sequence
CREATE TABLE <name> (
<column> ... AUTO_INCREMENT [ BY GROUP ] [ UNIT COUNT <numeric value> INDEX <numeric value> ],
<column definition>,
...
) ... AUTO_INCREMENT=<start value>
示例
示例一:默认创建一张使用 Group Sequence 作为自增列的表。
CREATE TABLE tab1 (
col1 BIGINT NOT NULL AUTO_INCREMENT,
col2 VARCHAR(16),
PRIMARY KEY(col1)
) DBPARTITION BY HASH(col1);
示例二:创建3张同名的、使用相同单元数量和不同单元索引的单元化 Group Sequence 作为自增列的表,分别用于3个不同的实例或库。
实例1/库1:
CREATE TABLE tab2 (
col1 BIGINT NOT NULL AUTO_INCREMENT UNIT COUNT 3 INDEX 0,
col2 VARCHAR(16),
PRIMARY KEY(col1)
) DBPARTITION BY HASH(col1);
实例2/库2:
CREATE TABLE tab2 (
col1 BIGINT NOT NULL AUTO_INCREMENT UNIT COUNT 3 INDEX 1,
col2 VARCHAR(16),
PRIMARY KEY(col1)
) DBPARTITION BY HASH(col1);
实例3/库3:
CREATE TABLE tab2 (
col1 BIGINT NOT NULL AUTO_INCREMENT UNIT COUNT 3 INDEX 2,
col2 VARCHAR(16),
PRIMARY KEY(col1)
) DBPARTITION BY HASH(col1);
示例三:创建一张使用 Time-based Sequence 作为自增列的表。
CREATE TABLE tab3 (
col1 BIGINT NOT NULL AUTO_INCREMENT BY TIME,
col2 VARCHAR(16),
PRIMARY KEY(col1)
) DBPARTITION BY HASH(col1);
示例四:创建一张使用 Simple Sequence 作为自增列的表。
CREATE TABLE tab4 (
col1 BIGINT NOT NULL AUTO_INCREMENT BY SIMPLE,
col2 VARCHAR(16),
PRIMARY KEY(col1)
) DBPARTITION BY HASH(col1);
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 SIMPLE,
`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)
ALTER TABLE
暂不支持通过 ALTER TABLE 来修改对应 Sequence 的类型,但可修改起始值。如果想要修改表相关的 Sequence 的类型,需要通过 SHOW SEQUENCES
指令查找出 Sequence 的具体名称和类型,然后再用 ALTER SEQUENCE
指令去修改。
ALTER TABLE <name> ... AUTO_INCREMENT=<start value>
注意:使用 DRDS Sequence 后,请谨慎修改 AUTO_INCREMENT 的起始值(仔细评估已经产生的 Sequence 值,以及生成新 Sequence 值的速度,防止产生冲突)。
版权声明
本文仅代表作者观点,不代表本站立场。
本文系作者授权发表,未经许可,不得转载。
评论