---
title: "创建二级分区表组 | OceanBase 文档中心"
description: 创建二级分区表组 本节主要介绍如何在 OceanBase 数据库的 MySQL 模式中创建二级分区表表组。 支持的二级分区类型 OceanBase 数据库的 MySQL 模式支持创建模板化和非模板化的二级分区表表组。 分区类型 创建模板化二级分区表组 创建非模板化二级分区表组 Range + Range / Rang…
image: https://mdn.alipayobjects.com/huamei_22khvb/afts/img/A*OSPzQ6GUQF4AAAAAQHAAAAgAeiGDAQ/original
---
切换语言

- 中文站 - 简体中文
- International - English
- 日本站 - 日本語

文档反馈![](https://mdn.alipayobjects.com/huamei_22khvb/afts/img/A*P8CuR4UJ_FkAAAAAAAAAAAAADiGDAQ/original) OceanBase 数据库分布式版 - V 3.2.3 企业版 LTS

# 创建二级分区表组

更新时间：2023-08-09 14:11:21

本节主要介绍如何在 OceanBase 数据库的 MySQL 模式中创建二级分区表表组。

## 支持的二级分区类型

OceanBase 数据库的 MySQL 模式支持创建模板化和非模板化的二级分区表表组。

| 分区类型 | 创建模板化二级分区表组 | 创建非模板化二级分区表组 |
| --- | --- | --- |
| Range + Range / Range + Range Columns / Range + List / Range + List Columns / Range + Hash / Range + Key | 支持 | 支持 |
| Range Columns + Range / Range Columns + Range Columns / Range Columns + List / Range Columns + List Columns / Range Columns + Hash / Range Columns + Key | 支持 | 支持 |
| List + Range / List + Range Columns / List + List / List + List Columns / List + Hash / List + Key | 支持 | 支持 |
| List Columns + Range / List Columns + Range Columns / List Columns + List / List Columns + List Columns / List Columns + Hash / List Columns + Key | 支持 | 支持 |
| Hash + Range / Hash + Range Columns / Hash + List / Hash + List Columns | 支持 | 不支持 |
| Hash + Key / Hash + Hash | 不支持 | 不支持 |
| Key + Range / Key + Range Columns / Key + List / Key + List Columns | 支持 | 不支持 |
| Key + Hash / Key + Key | 不支持 | 不支持 |

## 注意事项

- 创建表组需要当前用户具有全局 `CREATE` 权限。
 - 创建表组后，表组内的表不能单独进行分区管理操作，只能通过表组对分区表进行分区的统一管理。

## 创建二级分区表组语法

### 创建模板化二级分区表表组的 SQL 语法

```sql
CREATE TABLEGROUP [IF NOT EXISTS] tablegroupname [opt_tablegroup_option_list] opt_tg_partition_option

opt_tablegroup_option_list:
tablegroup_option [tablegroup_option]

tablegroup_option:
      LOCALITY [=] locality_name
    | PRIMARY_ZONE [=] primary_zone_name

opt_tg_partition_option:
    PARTITION BY
      KEY column_num tg_subpartition_option PARTITIONS int_num
    | HASH tg_subpartition_option PARTITIONS INTNUM
    | RANGE [COLUMNS column_num] tg_subpartition_option
          {PARTITION partition_name VALUES LESS THAN range_partition_expr, ...}
    | LIST [COLUMNS column_num] tg_subpartition_option
          {PARTITION partition_name VALUES IN list_partition_expr, ...}

tg_subpartition_option:
SUBPARTITION BY
     RANGE [COLUMNS column_num] SUBPARTITION TEMPLATE
          {SUBPARTITION partition_name VALUES LESS THAN range_partition_expr, ...}
    | HASH SUBPARTITIONS INTNUM
    | KEY column_num SUBPARTITIONS INTNUM
    | LIST [COLUMNS column_num] SUBPARTITION TEMPLATE
          {SUBPARTITION partition_name VALUES IN list_partition_expr, ...}

```

### 创建非模板化二级分区表表组的 SQL 语法

```sql
CREATE TABLEGROUP  <tablegroupname> [opt_tablegroup_option_list] opt_tg_partition_option

opt_tablegroup_option_list
tablegroup_option [tablegroup_option]

opt_tg_partition_option:
PARTITION BY
   RANGE COLUMNS column_num tg_subpartition_option
    { PARTITION partition_name VALUES LESS THAN range_partition_expr (subpartition_option_list)
      [,PARTITION partition_name VALUES LESS THAN range_partition_expr (subpartition_option_list) ...]}
 | LIST COLUMNS column_num tg_subpartition_option
    { PARTITION partition_name VALUES IN list_partition_expr (subpartition_option_list)
      [,PARTITION partition_name VALUES IN list_partition_expr (subpartition_option_list) ...] }
 | HASH [tg_subpartition_option] PARTITIONS int_num

tg_subpartition_option:
 SUBPARTITION BY
    RANGE [COLUMNS column_num]
  | LIST [COLUMNS column_num]
  | HASH [column_num][SUBPARTITIONS int_num]
  | KEY column_num SUBPARTITIONS int_num

subpartition_option_list:
    range_subpartition_option
  | list_subpartition_option
  | hash_subpartition_option
  | key_subpartition_option

range_subpartition_option:
  SUBPARTITION subpartition_name VALUES LESS THAN range_partition_expr
    [,SUBPARTITION subpartition_name VALUES LESS THAN range_partition_expr]...

list_subpartition_option:
  SUBPARTITION subpartition_name VALUES IN list_partition_expr
    [, SUBPARTITION subpartition_name VALUES IN list_partition_expr]...

hash_subpartition_option | key_subpartition_option:
  SUBPARTITION subpartition_name
    [, SUBPARTITION subpartition_name]...

```

### 语句说明

- `opt_tablegroup_option_list`：定义表组的分区方式。要求表组的 Locality 和 Primary Zone 信息必须与表组内的表完全一致。表组的 Locality 包括副本类型、个数和位置，Primary Zone 包括 Leader 位置和优先级，以上信息均要与表组内表信息完全一致。
 - `opt_tg_partition_option`：定义表组的分区规则。表组的分区规则需要与表组内的表使用相同的分区类型和分区个数。

  对于 Range Columns、List Columns 和 Key 分区，必须指定列个数（`COLUMN_NUM`）。

## 示例

### 创建非模板化二级分区表组

- 创建非模板化 Range Columns + Range Columns 分区表表组。

  ```sql
  obclient> CREATE TABLEGROUP tg1_rr
         PARTITION BY RANGE COLUMNS 1
         SUBPARTITION BY RANGE COLUMNS 1
         (PARTITION p0 VALUES LESS THAN (100)
           (SUBPARTITION sp0 VALUES LESS THAN (2020)
           ,SUBPARTITION sp1 VALUES LESS THAN  (2021)
           ,SUBPARTITION sp2 VALUES LESS THAN (2022)
          ,SUBPARTITION sp3 VALUES LESS THAN  (2023)
           ),
         PARTITION p1 VALUES LESS THAN (200)
           (SUBPARTITION sp4 VALUES LESS THAN (2020)
           ,SUBPARTITION sp5 VALUES LESS THAN  (2021)
           ,SUBPARTITION sp6 VALUES LESS THAN (2022)
           ,SUBPARTITION sp7 VALUES LESS THAN  (2023)
           )
         );
  Query OK, 0 rows affected

  ```
 - 创建非模板化 Range Columns + Hash 分区表表组。

  ```sql
  obclient> CREATE TABLEGROUP tg1_rh
      PARTITION BY RANGE COLUMNS 1
      SUBPARTITION BY HASH  (
          PARTITION p0 VALUES LESS THAN (1990) (
              SUBPARTITION s0,
              SUBPARTITION s1
          ),
          PARTITION p1 VALUES LESS THAN (2000) (
              SUBPARTITION s2,
              SUBPARTITION s3
          ),
          PARTITION p2 VALUES LESS THAN (2010) (
              SUBPARTITION s4,
              SUBPARTITION s5
          )
      );
  Query OK, 0 rows affected

  ```
 - 创建非模板化 Range Columns + List Columns 分区表表组。

  ```sql
  obclient> CREATE TABLEGROUP tg1_rl
         PARTITION BY RANGE COLUMNS 1
         SUBPARTITION BY LIST COLUMNS 2
         (PARTITION p0 VALUES LESS THAN (100)
           (SUBPARTITION sp0 VALUES IN (('US',1),('US',2)),
            SUBPARTITION sp1 VALUES IN  (('CN',1),('CN',2))
           ),
          PARTITION p1 VALUES LESS THAN (200)
           (SUBPARTITION sp3 VALUES IN (('US',1),('US',2)),
            SUBPARTITION sp4 VALUES IN  (('CN',1),('CN',2))
           )
         );
  Query OK, 0 rows affected

  ```
 - 创建非模板化 List Columns + Hash 分区表表组。

  ```sql
  obclient> CREATE TABLEGROUP tg1_lh
         PARTITION BY LIST COLUMNS 1
         SUBPARTITION BY HASH
         (PARTITION p_northwest VALUES IN('OR', 'WA')
           (SUBPARTITION sp0,
            SUBPARTITION sp1
            ),
          PARTITION p_southwest VALUES IN('AZ', 'UT', 'NM')
           (SUBPARTITION sp2,
            SUBPARTITION sp3
            )
         );
  Query OK, 0 rows affected

  ```
 - 创建非模板化 List Columns + List Columns 分区表表组。

  ```sql
  obclient> CREATE TABLEGROUP tg1_ll
         PARTITION BY LIST COLUMNS 1
         SUBPARTITION BY LIST COLUMNS 1
         (PARTITION p0 VALUES IN (1,2,3)
          ( SUBPARTITION sp0 VALUES IN (2020)
          , SUBPARTITION sp1 VALUES IN (2021)
          , SUBPARTITION sp2 VALUES IN (2022)
          , SUBPARTITION sp3 VALUES IN (2023)
          ),
          PARTITION p1 VALUES IN (4,5,6)
          ( SUBPARTITION sp4 VALUES IN (2020)
          , SUBPARTITION sp5 VALUES IN (2021)
          , SUBPARTITION sp6 VALUES IN (2022)
          , SUBPARTITION sp7 VALUES IN (2023)
          )
         );
  Query OK, 0 rows affected

  ```
 - 创建非模板化 List Columns + Range Columns 分区表表组。

  ```sql
  obclient> CREATE TABLEGROUP tg1_lr
         PARTITION BY LIST COLUMNS 1
         SUBPARTITION BY RANGE COLUMNS 1
         (PARTITION p0 VALUES IN (1,2,3)
           ( SUBPARTITION sp0 VALUES LESS THAN (2020)
           , SUBPARTITION sp1 VALUES LESS THAN (2021)
           , SUBPARTITION sp2 VALUES LESS THAN (2022)
           , SUBPARTITION sp3 VALUES LESS THAN (2023)
           ),
          PARTITION p1 VALUES IN (4,5,6)
           ( SUBPARTITION sp4 VALUES LESS THAN (2020)
           , SUBPARTITION sp5 VALUES LESS THAN (2021)
           , SUBPARTITION sp6 VALUES LESS THAN (2022)
           , SUBPARTITION sp7 VALUES LESS THAN (2023)
           )
         );
  Query OK, 0 rows affected

  ```
 - 创建非模板化 Range Columns + Key 分区表表组。

  ```sql
  obclient> CREATE TABLEGROUP tg2_f_rck
         PARTITION BY RANGE COLUMNS 1
         SUBPARTITION BY KEY 1
         (PARTITION p0 VALUES LESS THAN(100)
           (SUBPARTITION sp0,
            SUBPARTITION sp1,
            SUBPARTITION sp2
           ),
          PARTITION p1 VALUES LESS THAN(200)
           (SUBPARTITION sp3,
            SUBPARTITION sp4,
            SUBPARTITION sp5
           ),
          PARTITION p2 VALUES LESS THAN(300)
           (SUBPARTITION sp6,
            SUBPARTITION sp7,
            SUBPARTITION sp8
           )
         );
  Query OK, 0 rows affected

  ```
 - 创建非模板化 List Columns + Key 分区表表组。

  ```sql
  obclient> CREATE TABLEGROUP tg2_f_lck
         PARTITION BY LIST COLUMNS 1
         SUBPARTITION BY KEY 1
         (PARTITION p0 VALUES IN('01','02')
           (SUBPARTITION sp0,
            SUBPARTITION sp1,
            SUBPARTITION sp2),
          PARTITION p1 VALUES IN('03','04')
           (SUBPARTITION sp3,
            SUBPARTITION sp4,
            SUBPARTITION sp5),
          PARTITION p2 VALUES IN('05','06')
           (SUBPARTITION sp6,
            SUBPARTITION sp7,
            SUBPARTITION sp8)
          );
  Query OK, 0 rows affected

  ```

### 创建模板化二级分区表组

- 创建模板化 Range Columns + Range Columns 分区表表组。

  ```sql
  obclient> CREATE TABLEGROUP tg2_m_rcrc
          PARTITION BY RANGE COLUMNS 1
          SUBPARTITION BY RANGE COLUMNS 1
          SUBPARTITION TEMPLATE
          (SUBPARTITION mp0 VALUES LESS THAN(2019),
           SUBPARTITION mp1 VALUES LESS THAN(2020),
           SUBPARTITION mp2 VALUES LESS THAN(2021)
          )
          (PARTITION p0 VALUES LESS THAN(100),
           PARTITION p1 VALUES LESS THAN(200),
           PARTITION p2 VALUES LESS THAN(300)
          );
  Query OK, 0 rows affected

  ```
 - 创建模板化 Range Columns + List Columns 分区表表组。

  ```sql
  obclient> CREATE TABLEGROUP tg2_m_rclc
         PARTITION BY RANGE COLUMNS 1
         SUBPARTITION BY LIST COLUMNS 1
         SUBPARTITION TEMPLATE
         (SUBPARTITION mp0 VALUES IN('A','B'),
          SUBPARTITION mp1 VALUES IN('C','D'),
          SUBPARTITION mp2 VALUES IN('E','F')
         )
         (PARTITION p0 VALUES LESS THAN(100),
          PARTITION p1 VALUES LESS THAN(200),
          PARTITION p2 VALUES LESS THAN(300)
         );
  Query OK, 0 rows affected

  ```
 - 创建模板化 Range Columns + Hash 分区表表组。

  ```sql
  obclient> CREATE TABLEGROUP tg2_m_rch
         PARTITION BY RANGE COLUMNS 1
         SUBPARTITION BY HASH SUBPARTITIONS 5
         (PARTITION p0 VALUES LESS THAN(100),
          PARTITION p1 VALUES LESS THAN(200),
          PARTITION p2 VALUES LESS THAN(300)
         );
  Query OK, 0 rows affected

  ```
 - 创建模板化 Range Columns + Key 分区表表组。

  ```sql
  obclient> CREATE TABLEGROUP tg2_m_rck
         PARTITION BY RANGE COLUMNS 1
         SUBPARTITION BY KEY 1 SUBPARTITIONS 3
         (PARTITION p0 VALUES LESS THAN(100),
          PARTITION p1 VALUES LESS THAN(200),
          PARTITION p2 VALUES LESS THAN(300)
         );
  Query OK, 0 rows affected

  ```
 - 创建模板化 List Columns + Range Columns 分区表表组。

  ```sql
  obclient> CREATE TABLEGROUP tg2_m_lcrc
         PARTITION BY LIST COLUMNS 1
         SUBPARTITION BY RANGE COLUMNS 1
         SUBPARTITION TEMPLATE
         (SUBPARTITION mp0 VALUES LESS THAN(100),
          SUBPARTITION mp1 VALUES LESS THAN(200),
          SUBPARTITION mp2 VALUES LESS THAN(300)
         )
         (PARTITION p0 VALUES IN('A','B'),
          PARTITION p1 VALUES IN('C','D'),
          PARTITION p2 VALUES IN('E','F')
         );
  Query OK, 0 rows affected

  ```
 - 创建模板化 List Columns + List Columns 分区表表组。

  ```sql
  obclient> CREATE TABLEGROUP tg2_m_lclc
         PARTITION BY LIST COLUMNS 1
         SUBPARTITION BY LIST COLUMNS 1
         SUBPARTITION TEMPLATE
         (SUBPARTITION mp0 VALUES IN('A','B'),
          SUBPARTITION mp1 VALUES IN('C','D'),
          SUBPARTITION mp2 VALUES IN('E','F')
         )
         (PARTITION p0 VALUES IN('01','02'),
          PARTITION p1 VALUES IN('03','04'),
          PARTITION p2 VALUES IN('05','06')
         );
  Query OK, 0 rows affected

  ```
 - 创建模板化 List Columns + Hash 分区表表组。

  ```sql
  obclient> CREATE TABLEGROUP tg2_m_lch
         PARTITION BY LIST COLUMNS 1
         SUBPARTITION BY HASH SUBPARTITIONS 5
         (PARTITION p0 VALUES IN('01','02'),
          PARTITION p1 VALUES IN('03','04'),
          PARTITION p2 VALUES IN('05','06')
         );
  Query OK, 0 rows affected

  ```
 - 创建模板化 List Columns + Key 分区表表组。

  ```sql
  obclient> CREATE TABLEGROUP tg2_m_lck
         PARTITION BY LIST COLUMNS 1
         SUBPARTITION BY KEY 1 SUBPARTITIONS 5
         (PARTITION p0 VALUES IN('01','02'),
          PARTITION p1 VALUES IN('03','04'),
          PARTITION p2 VALUES IN('05','06')
         );
  Query OK, 0 rows affected

  ```

 上一篇 下一篇 ![有帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*y6ocSqN8cqsAAAAAAAAAAAAAARQnAQ)![无帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*BG9IQJyLHF8AAAAAAAAAAAAAARQnAQ)![反馈](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*eTWdQKCRKHwAAAAAAAAAAAAAARQnAQ)[AI](https://www.oceanbase.com/obi) 咨询热线