添加分区

创建分区表后，您可以为分区表添加分区。

分区表对添加分区操作的支持情况

OceanBase 数据库的 MySQL 模式中，一级分区表和二级分区表对添加分区操作的具体支持情况如下表所示。

添加一级分区

您可以向一级分区表、模板化二级分区表和非模板化二级分区表中添加一级分区。

向一级分区表和模板化二级分区表中添加一级分区

向一级分区表和模板化二级分区表中添加一级分区的 SQL 语法如下：

ALTER TABLE table_name ADD PARTITION (partition_option);

partition_option:
  {PARTITION partition_name VALUES LESS THAN range_partition_expr
     [,PARTITION partition_name VALUES LESS THAN range_partition_expr]... }
 |{PARTITION partition_name VALUES IN list_partition_expr
     [,PARTITION partition_name VALUES IN list_partition_expr]...}

部分示例如下：

• 创建 Range 分区表 tbl1_r，并添加分区 M202106。

  obclient> CREATE TABLE tbl1_r (id INT NOT NULL,value varchar(50),time TIMESTAMP NOT NULL)
         PARTITION BY RANGE(UNIX_TIMESTAMP(time)) 
          (PARTITION M202101 VALUES LESS THAN(UNIX_TIMESTAMP('2021/02/01')),
           PARTITION M202102 VALUES LESS THAN(UNIX_TIMESTAMP('2021/03/01')),
           PARTITION M202103 VALUES LESS THAN(UNIX_TIMESTAMP('2021/04/01')),
           PARTITION M202104 VALUES LESS THAN(UNIX_TIMESTAMP('2021/05/01')),
           PARTITION M202105 VALUES LESS THAN(UNIX_TIMESTAMP('2021/06/01'))
          );
  Query OK, 0 rows affected
  
  obclient> ALTER TABLE tbl1_r ADD PARTITION 
         (PARTITION M202106 VALUES LESS THAN(UNIX_TIMESTAMP('2021/07/01')));
  Query OK, 0 rows affected
  

• 创建 List 分区表 tbl1_l，并添加分区 p2 和 p3。

  obclient> CREATE TABLE tbl1_l (col1 INT PRIMARY KEY,col2 VARCHAR(50))
         PARTITION BY LIST(col1) 
          (PARTITION p0 VALUES IN (1,2,3),
           PARTITION p1  VALUES IN (5,6)
          );
  Query OK, 0 rows affected
  
  obclient> ALTER TABLE tbl1_l ADD PARTITION 
         (PARTITION p2 VALUES IN (7,8),
          PARTITION p3  VALUES IN (DEFAULT)
         );
  Query OK, 0 rows affected
  

• 向 Range Columns + Range Columns 分区的模板化分区表 t_m_rcrc 中添加一级分区 p3 和 p4。

  obclient> CREATE TABLE t_m_rcrc(col1 INT,col2 INT) 
         PARTITION BY RANGE COLUMNS(col1)
         SUBPARTITION BY RANGE COLUMNS(col2)
         SUBPARTITION TEMPLATE 
          (SUBPARTITION mp0 VALUES LESS THAN(1000),
           SUBPARTITION mp1 VALUES LESS THAN(2000),
           SUBPARTITION mp2 VALUES LESS THAN(3000)
          )
          (PARTITION p0 VALUES LESS THAN(100),
           PARTITION p1 VALUES LESS THAN(200),
           PARTITION p2 VALUES LESS THAN(300)
          ); 
  Query OK, 0 rows affected
  
  obclient> ALTER TABLE t_m_rcrc ADD PARTITION
         (PARTITION p3 VALUES LESS THAN(400),
          PARTITION p4 VALUES LESS THAN(500)
         ); 
  Query OK, 0 rows affected
  

向非模板化二级分区表中添加一级分区

向非模板化二级分区表中添加一级分区的 SQL 语法如下：

ALTER TABLE table_name ADD PARTITION (partition_option);

partition_option:
  {PARTITION partition_name VALUES LESS THAN range_partition_expr (subpartition_option)
     [,PARTITION partition_name VALUES LESS THAN range_partition_expr (subpartition_option)]... }
 |{PARTITION partition_name VALUES IN list_partition_expr (subpartition_option)
     [,PARTITION partition_name VALUES IN list_partition_expr (subpartition_option)]...}

subpartition_option：
  {SUBPARTITION subpartition_name VALUES LESS THAN range_partition_expr, ...}
 |{SUBPARTITION subpartition_name VALUES IN list_partition_expr, ....}
 |{SUBPARTITION subpartition_name, ....}

以向 Range + Range 非模板化分区表 t_f_rclc 中添加一级分区 p2 为例，示例如下：

obclient> CREATE TABLE t_f_rclc (col1 INT,col2 INT) 
       PARTITION BY RANGE COLUMNS(col1)
       SUBPARTITION BY LIST COLUMNS(col2)
       (PARTITION p0 VALUES LESS THAN(100)
         (SUBPARTITION sp0 VALUES IN(1,3),
          SUBPARTITION sp1 VALUES IN(4,6),
          SUBPARTITION sp2 VALUES IN(7,9)),
        PARTITION p1 VALUES LESS THAN(200)
         (SUBPARTITION sp3 VALUES IN(1,3),
          SUBPARTITION sp4 VALUES IN(4,6),
          SUBPARTITION sp5 VALUES IN(7,9))
       ); 

obclient> ALTER TABLE t_f_rclc ADD PARTITION
       (PARTITION p2 VALUES LESS THAN(300)
         (SUBPARTITION sp6 VALUES IN(1,3),
          SUBPARTITION sp7 VALUES IN(4,6),
          SUBPARTITION sp8 VALUES IN(7,9))
       ); 
Query OK, 0 rows affected

添加二级分区

语法

ALTER TABLE table_name MODIFY PARTITION partition_name
    ADD SUBPARTITION (subpartition_option);

subpartition_option:
    {SUBPARTITION subpartition_name VALUES LESS THAN range_partition_expr
        [, SUBPARTITION subpartition_name VALUES LESS THAN range_partition_expr] ...}
    | {SUBPARTITION subpartition_name VALUES IN list_partition_expr
        [, SUBPARTITION subpartition_name VALUES IN list_partition_expr] ...}

参数解释

示例

• 向 Range + Range 模板化分区表中添加二级分区表。

  1. 创建 Range + Range 分区的模板化二级分区表 tbl1_m_rr。

     CREATE TABLE tbl1_m_rr (col1 INT, col2 INT, col3 INT)
         PARTITION BY RANGE(col1)
            SUBPARTITION BY RANGE(col2)
            SUBPARTITION TEMPLATE
            (SUBPARTITION p0 VALUES LESS THAN (1000),
             SUBPARTITION p1 VALUES LESS THAN (2000))
         (PARTITION p0 VALUES LESS THAN (100),
          PARTITION p1 VALUES LESS THAN (200));
     

  2. 向表 tbl1_m_rr 一级分区 p1 中添加二级分区 p1sp2。

     ALTER TABLE tbl1_m_rr MODIFY PARTITION p1
         ADD SUBPARTITION (SUBPARTITION p1sp2 VALUES LESS THAN (3000));
     

• 向 Range + Range 非模板化分区表中添加二级分区表。

  1. 创建 Range + Range 分区的非模板化二级分区表 tbl2_f_rr。

     CREATE TABLE tbl2_f_rr(col1 INT, col2 INT)
         PARTITION BY RANGE(col1)
             SUBPARTITION BY RANGE(col2)
         (PARTITION p0 VALUES LESS THAN(100)
            (SUBPARTITION sp0 VALUES LESS THAN(2020),
             SUBPARTITION sp1 VALUES LESS THAN(2021)
             ),
          PARTITION p1 VALUES LESS THAN(200)
            (SUBPARTITION sp2 VALUES LESS THAN(2020),
             SUBPARTITION sp3 VALUES LESS THAN(2021),
             SUBPARTITION sp4 VALUES LESS THAN(2022)
             )
          );
     

  2. 向表 tbl2_f_rr 一级分区 p1 中添加二级分区 sp5 和 sp6。

     ALTER TABLE tbl2_f_rr MODIFY PARTITION p1
         ADD SUBPARTITION
            (SUBPARTITION sp5 VALUES LESS THAN(2023),
             SUBPARTITION sp6 VALUES LESS THAN(2024));
     

相关文档

ALTER TABLE