ALTER TABLEGROUP

描述

该语句用于对一个表组增加多张表和修改表组的属性。

语法

• 对一个表组增加多张表。

  ALTER TABLEGROUP tablegroup_name ADD [TABLE] table_name [, table_name...];
  

• 修改表组的分区规则。

  ALTER TABLEGROUP tablegroup_name tablegroup_option;
  
  tablegroup_option:
      SHARDING = 'NONE' | 'PARTITION' | 'ADAPTIVE'
  

参数解释

示例

• 创建名为 tblgroup1 的表组，其 SHARDING 属性为 NONE，并将分区表 tbl1 和分区表 tbl2 加入到表组中。

  obclient> CREATE TABLEGROUP tblgroup1 SHARDING = 'NONE';
  Query OK, 0 rows affected
  
  obclient> CREATE TABLE tbl1(col1 INT,col2 VARCHAR(50))
      PARTITION BY LIST(col1) SUBPARTITION BY LIST COLUMNS(col2)
        (PARTITION p0 VALUES in (01)
           (SUBPARTITION mp0 VALUES in ('01'),
            SUBPARTITION mp1 VALUES in ('02')),
         PARTITION p1 VALUES in (02)
            (SUBPARTITION mp3 VALUES in ('01'),
             SUBPARTITION mp4 VALUES in ('02'))
         );
  Query OK, 0 rows affected
  
  obclient> CREATE TABLE tbl2(col1 INT,col2 VARCHAR(50))
      PARTITION BY LIST(col1) SUBPARTITION BY LIST COLUMNS(col2)
        (PARTITION p0 VALUES in (01)
          (SUBPARTITION mp0 VALUES in ('01'),
            SUBPARTITION mp1 VALUES in ('02')),
         PARTITION p1 VALUES in (02)
           (SUBPARTITION mp3 VALUES in ('01'),
            SUBPARTITION mp4 VALUES in ('02'))
         );
  Query OK, 0 rows affected
  
  obclient> ALTER TABLEGROUP tblgroup1 ADD tbl1,tbl2;
  Query OK, 0 rows affected
  

  由于 tbl1 和 tbl2 的分区方式一致，所以 SHARDING 属性可以更改为 PARTITION 或 ADAPTIVE。

  obclient> ALTER TABLEGROUP tblgroup1 SHARDING = 'PARTITION';
  Query OK, 0 rows affected
  
  obclient> ALTER TABLEGROUP tblgroup1 SHARDING = 'ADAPTIVE';
  Query OK, 0 rows affected
  

• 创建名为 tblgroup2 的表组，其 SHARDING 属性为 NONE，向表组中添加非分区表 tbl1 和分区表 tbl2。

  obclient> CREATE TABLEGROUP tblgroup2 SHARDING = 'NONE';
  Query OK, 0 rows affected
  
  obclient> CREATE TABLE tbl1 (col INT);
  Query OK, 0 rows affected
  
  obclient> CREATE TABLE tbl2 (col1 BIGINT PRIMARY KEY,col2 VARCHAR(50))
      PARTITION BY LIST(col1)
        (PARTITION p0 VALUES IN (1, 2, 3),
         PARTITION p1 VALUES IN (5, 6),
         PARTITION p2 VALUES IN (DEFAULT)
        );
  Query OK, 0 rows affected
  
  obclient> ALTER TABLEGROUP tblgroup2 ADD tbl1,tbl2;
  Query OK, 0 rows affected
  

  由于表组 tblgroup2 中包含非分区表和分区表，所以 SHARDING 属性不能更改为 PARTITION 或 ADAPTIVE。

  obclient> ALTER TABLEGROUP tblgroup2 SHARDING = 'PARTITION';
  ERROR 4179 (HY000): not all tables are non-partitioned or partitioned, modify tablegroup sharding attribute not allowed
  
  obclient> ALTER TABLEGROUP tblgroup2 SHARDING = 'ADAPTIVE';
  ERROR 4179 (HY000): not all tables are non-partitioned or partitioned, modify tablegroup sharding attribute not allowed
  

• 创建名为 tblgroup3 的表组，其 SHARDING 属性为 NONE，向表组中添加分区表 tbl1 和分区表 tbl2。

  obclient> CREATE TABLEGROUP tblgroup3 SHARDING = 'NONE';
  Query OK, 0 rows affected
  
  obclient> CREATE TABLE tbl1(col1 INT,col2 VARCHAR(50))
     PARTITION BY LIST(col1) SUBPARTITION BY LIST COLUMNS(col2)
      (PARTITION p0 VALUES in (01)
        (SUBPARTITION mp0 VALUES in ('01'),
         SUBPARTITION mp1 VALUES in ('02'),
         SUBPARTITION mp2 VALUES in ('03')
         ),
       PARTITION p1 VALUES in (02)
         (SUBPARTITION mp3 VALUES in ('01'),
          SUBPARTITION mp4 VALUES in ('02'),
          SUBPARTITION mp5 VALUES in ('03'))
       );
  Query OK, 0 rows affected
  
  obclient> CREATE TABLE tbl2(col1 INT,col2 VARCHAR(50))
     PARTITION BY LIST(col1) SUBPARTITION BY LIST COLUMNS(col2)
      (PARTITION p0 VALUES in (01)
        (SUBPARTITION mp0 VALUES in ('01'),
         SUBPARTITION mp1 VALUES in ('02')),
       PARTITION p1 VALUES in (02)
        (SUBPARTITION mp3 VALUES in ('01'),
         SUBPARTITION mp4 VALUES in ('02'))
       );
  Query OK, 0 rows affected
  
  obclient> ALTER TABLEGROUP tblgroup3 ADD tbl1,tbl2;
  Query OK, 0 rows affected
  

  由于表组 tblgroup3 中 tbl1 和 tbl2 的一级分区的分区类型相同，二级分区分区类型不同（二级分区个数不同），所以 SHARDING 属性可以更改为 PARTITION，但是不能更改为 ADAPTIVE。

  obclient> ALTER TABLEGROUP tblgroup3 SHARDING = 'PARTITION';
  Query OK, 0 rows affected
  
  obclient> ALTER TABLEGROUP tblgroup3 SHARDING = 'ADAPTIVE';
  ERROR 4179 (HY000): subpartition num not matched, modify tablegroup sharding attribute not allowed
  

• 创建名为 tblgroup4 的表组，其 SHARDING 属性为 ADAPTIVE，修改表组中表 tbl1 和表 tbl2 的分区个数，则表组中所有表此时分区方式不一致，表组中不能加入新的表 tbl3。

  obclient> CREATE TABLEGROUP tblgroup4 SHARDING = 'ADAPTIVE';
  Query OK, 0 rows affected
  
  obclient> CREATE TABLE tbl1(col1 INT,col2 INT)
      PARTITION BY RANGE(col1) SUBPARTITION BY RANGE(col2)
        SUBPARTITION TEMPLATE
         (SUBPARTITION mp0 VALUES LESS THAN(2020),
          SUBPARTITION mp1 VALUES LESS THAN(2021),
          SUBPARTITION mp2 VALUES LESS THAN(2022))
         (PARTITION p0 VALUES LESS THAN(100),
          PARTITION p1 VALUES LESS THAN(200));
  Query OK, 0 rows affected
  
  obclient> CREATE TABLE tbl2(col1 INT,col2 INT)
      PARTITION BY RANGE(col1) SUBPARTITION BY RANGE(col2)
        SUBPARTITION TEMPLATE
         (SUBPARTITION mp0 VALUES LESS THAN(2020),
          SUBPARTITION mp1 VALUES LESS THAN(2021),
          SUBPARTITION mp2 VALUES LESS THAN(2022))
        (PARTITION p0 VALUES LESS THAN(100),
         PARTITION p1 VALUES LESS THAN(200));
  Query OK, 0 rows affected
  
  obclient> ALTER TABLEGROUP tblgroup4 ADD tbl1,tbl2;
  Query OK, 0 rows affected
  
  obclient> ALTER TABLE tbl1
      ADD PARTITION (PARTITION p3 VALUES LESS THAN(400),
                     PARTITION p4 VALUES LESS THAN(500));
  Query OK, 0 rows affected
  
  obclient> ALTER TABLE tbl2 DROP SUBPARTITION p0smp0,p0smp1;
  Query OK, 0 rows affected
  
  obclient> CREATE TABLE tbl3(col1 INT,col2 INT)
      PARTITION BY RANGE(col1) SUBPARTITION BY RANGE(col2)
        SUBPARTITION TEMPLATE
         (SUBPARTITION mp0 VALUES LESS THAN(2020),
          SUBPARTITION mp1 VALUES LESS THAN(2021),
          SUBPARTITION mp2 VALUES LESS THAN(2022))
         (PARTITION p0 VALUES LESS THAN(100),
          PARTITION p1 VALUES LESS THAN(200));
  Query OK, 0 rows affected
  
  obclient> ALTER TABLEGROUP tblgroup4 ADD tbl3;
  ERROR 4179 (HY000): partition num not equal, add table to tablegroup not allowed
  

相关文档

• CREATE TABLEGROUP

• DROP TABLEGROUP

• 管理表组内的表

• 查看表组信息