全部产品
Search
文档中心

Sequence隐式用法

更新时间: 2020-08-05

在为拆分表或广播表的主键定义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的类型,但您可以参见如下命令通过ALTER TABLE修改起始值:

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

如果想要修改表相关的Sequence类型,需要通过SHOW SEQUENCES指令查找出Sequence的具体名称和类型,然后再用ALTER SEQUENCE指令去修改,具体操作请参见Sequence显式用法

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