全部产品
云市场

Sequence 隐式用法

更新时间:2019-01-31 11:16:03

在为拆分表或广播表的主键定义 AUTO_INCREMENT 后,Sequence 可以用于自动填充主键,由 DRDS 自动维护。

CREATE TABLE

扩展标准建表语法,增加了自增列的 Sequence 类型,如果未指定类型关键字,则默认类型为 GROUP。DRDS 自动创建的、跟表相关联的 Sequence 名称,都是以 AUTO_SEQ_ 为前缀,后面加上表名。

Group Sequence、Time-based Sequence 与 Simple Sequence

  1. CREATE TABLE <name> (
  2. <column> ... AUTO_INCREMENT [ BY GROUP | SIMPLE | TIME ],
  3. <column definition>,
  4. ...
  5. ) ... AUTO_INCREMENT=<start value>

注意:如果指定了 BY TIME,即 Time-based Sequence,则该列类型必须为 BIGINT。

单元化 Group Sequence

  1. CREATE TABLE <name> (
  2. <column> ... AUTO_INCREMENT [ BY GROUP ] [ UNIT COUNT <numeric value> INDEX <numeric value> ],
  3. <column definition>,
  4. ...
  5. ) ... AUTO_INCREMENT=<start value>

示例

示例一:默认创建一张使用 Group Sequence 作为自增列的表。

  1. CREATE TABLE tab1 (
  2. col1 BIGINT NOT NULL AUTO_INCREMENT,
  3. col2 VARCHAR(16),
  4. PRIMARY KEY(col1)
  5. ) DBPARTITION BY HASH(col1);

示例二:创建3张同名的、使用相同单元数量和不同单元索引的单元化 Group Sequence 作为自增列的表,分别用于3个不同的实例或库。

实例1/库1:

  1. CREATE TABLE tab2 (
  2. col1 BIGINT NOT NULL AUTO_INCREMENT UNIT COUNT 3 INDEX 0,
  3. col2 VARCHAR(16),
  4. PRIMARY KEY(col1)
  5. ) DBPARTITION BY HASH(col1);

实例2/库2:

  1. CREATE TABLE tab2 (
  2. col1 BIGINT NOT NULL AUTO_INCREMENT UNIT COUNT 3 INDEX 1,
  3. col2 VARCHAR(16),
  4. PRIMARY KEY(col1)
  5. ) DBPARTITION BY HASH(col1);

实例3/库3:

  1. CREATE TABLE tab2 (
  2. col1 BIGINT NOT NULL AUTO_INCREMENT UNIT COUNT 3 INDEX 2,
  3. col2 VARCHAR(16),
  4. PRIMARY KEY(col1)
  5. ) DBPARTITION BY HASH(col1);

示例三:创建一张使用 Time-based Sequence 作为自增列的表。

  1. CREATE TABLE tab3 (
  2. col1 BIGINT NOT NULL AUTO_INCREMENT BY TIME,
  3. col2 VARCHAR(16),
  4. PRIMARY KEY(col1)
  5. ) DBPARTITION BY HASH(col1);

示例四:创建一张使用 Simple Sequence 作为自增列的表。

  1. CREATE TABLE tab4 (
  2. col1 BIGINT NOT NULL AUTO_INCREMENT BY SIMPLE,
  3. col2 VARCHAR(16),
  4. PRIMARY KEY(col1)
  5. ) DBPARTITION BY HASH(col1);

SHOW CREATE TABLE

当表为拆分表或者广播表时,显示自增列 Sequence 的类型。

  1. 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。

  1. mysql> SHOW CREATE TABLE tab1;
  2. +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  3. | Table | Create Table |
  4. +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  5. | tab1 | CREATE TABLE `tab1` (
  6. `col1` bigint(20) NOT NULL AUTO_INCREMENT BY GROUP,
  7. `col2` varchar(16) DEFAULT NULL,
  8. PRIMARY KEY (`col1`)
  9. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 dbpartition by hash(`col1`) |
  10. +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  11. 1 row in set (0.02 sec)

示例二:建表时为 AUTO_INCREMENT 指定了单元数量和单元索引,使用单元化 Group Sequence,但 SHOW CREATE TABLE 时并不显示单元数量和单元索引,不能将此 DDL 用于创建具备同样单元化 Group Sequence 能力的表。

  1. mysql> SHOW CREATE TABLE tab2;
  2. +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  3. | Table | Create Table |
  4. +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  5. | tab2 | CREATE TABLE `tab2` (
  6. `col1` bigint(20) NOT NULL AUTO_INCREMENT BY GROUP,
  7. `col2` varchar(16) DEFAULT NULL,
  8. PRIMARY KEY (`col1`)
  9. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 dbpartition by hash(`col1`) |
  10. +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  11. 1 row in set (0.01 sec)

示例三:建表时为 AUTO_INCREMENT 指定了 BY TIME,即 Time-based Sequence 类型

  1. mysql> SHOW CREATE TABLE tab3;
  2. +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  3. | Table | Create Table |
  4. +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  5. | tab3 | CREATE TABLE `tab3` (
  6. `col1` bigint(20) NOT NULL AUTO_INCREMENT BY TIME,
  7. `col2` varchar(16) DEFAULT NULL,
  8. PRIMARY KEY (`col1`)
  9. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 dbpartition by hash(`col1`) |
  10. +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  11. 1 row in set (0.01 sec)

示例四:建表时为 AUTO_INCREMENT 指定了 BY SIMPLE,即 Simple Sequence 类型

  1. mysql> SHOW CREATE TABLE tab4;
  2. +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  3. | Table | Create Table |
  4. +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  5. | tab3 | CREATE TABLE `tab4` (
  6. `col1` bigint(20) NOT NULL AUTO_INCREMENT BY SIMPLE,
  7. `col2` varchar(16) DEFAULT NULL,
  8. PRIMARY KEY (`col1`)
  9. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 dbpartition by hash(`col1`) |
  10. +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  11. 1 row in set (0.01 sec)

SHOW SEQUENCES

建表后相关的 Sequence 名称和详细信息,可通过 SHOW SEQUENCES 查看:

  1. mysql> SHOW SEQUENCES;
  2. +---------------+--------+------------+------------+------------+--------------+------------+---------------------+-------+--------+
  3. | NAME | VALUE | UNIT_COUNT | UNIT_INDEX | INNER_STEP | INCREMENT_BY | START_WITH | MAX_VALUE | CYCLE | TYPE |
  4. +---------------+--------+------------+------------+------------+--------------+------------+---------------------+-------+--------+
  5. | seq1 | 100000 | 1 | 0 | 100000 | N/A | N/A | N/A | N/A | GROUP |
  6. | seq2 | 400000 | 3 | 1 | 100000 | N/A | N/A | N/A | N/A | GROUP |
  7. | seq3 | N/A | N/A | N/A | N/A | N/A | N/A | N/A | N/A | TIME |
  8. | seq4 | 1006 | N/A | N/A | N/A | 2 | 1000 | 99999999999 | N | SIMPLE |
  9. | AUTO_SEQ_tab1 | 100000 | 1 | 0 | 100000 | N/A | N/A | N/A | N/A | GROUP |
  10. | AUTO_SEQ_tab2 | 400000 | 3 | 1 | 100000 | N/A | N/A | N/A | N/A | GROUP |
  11. | AUTO_SEQ_tab3 | N/A | N/A | N/A | N/A | N/A | N/A | N/A | N/A | TIME |
  12. | AUTO_SEQ_tab4 | 2 | N/A | N/A | N/A | 1 | 1 | 9223372036854775807 | N | SIMPLE |
  13. +---------------+--------+------------+------------+------------+--------------+------------+---------------------+-------+--------+
  14. 8 rows in set (0.01 sec)

ALTER TABLE

暂不支持通过 ALTER TABLE 来修改对应 Sequence 的类型,但可修改起始值。如果想要修改表相关的 Sequence 的类型,需要通过 SHOW SEQUENCES 指令查找出 Sequence 的具体名称和类型,然后再用 ALTER SEQUENCE 指令去修改。

  1. ALTER TABLE <name> ... AUTO_INCREMENT=<start value>

注意:使用 DRDS Sequence 后,请谨慎修改 AUTO_INCREMENT 的起始值(仔细评估已经产生的 Sequence 值,以及生成新 Sequence 值的速度,防止产生冲突)。