PolarDB-X 云原生分布式数据库 Sequence 隐式用法

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

CREATE TABLE

扩展标准建表语法，增加了自增列的Sequence类型，如果未指定类型关键字，则默认类型为GROUP。PolarDB-X自动创建的、跟表相关联的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的类型，但您可以参见如下命令通过ALTER TABLE修改起始值：

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

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

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