---
title: "分区操作 - OceanBase 数据库 V4.2.5 | OceanBase 文档中心"
description: 分区操作 OceanBase 数据库 MySQL 模式下的分区操作包括重分区、添加分区、删除分区和清空分区。 重分区 重分区的语法如下： ALTER TABLE table_name PARTITION BY (partition_definitions); 重分区示例 将 Hash 分区更改为 Key 分区方式的示…
---
切换语言

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

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

# 分区操作

更新时间：2026-07-15 20:42:47

[编辑](https://github.com/oceanbase/oceanbase-doc/edit/V4.2.5/zh-CN/700.reference/500.sql-reference/100.sql-syntax/200.common-tenant-of-mysql-mode/700.ddl-function-of-mysql-mode/800.partitioning-operations-of-mysql-mode.md)  

OceanBase 数据库 MySQL 模式下的分区操作包括重分区、添加分区、删除分区和清空分区。

## 重分区

重分区的语法如下：

```sql
ALTER TABLE table_name PARTITION BY (partition_definitions);

```

### 重分区示例

#### 将 Hash 分区更改为 Key 分区方式的示例

```sql
obclient> CREATE TABLE tbl2(c1 INT, c2 DATETIME, PRIMARY KEY(c1, c2))
PARTITION BY HASH(c1) PARTITIONS 4;
Query OK, 0 rows affected

obclient> ALTER TABLE tbl2 PARTITION BY KEY(c1) PARTITIONS 10;
Query OK, 0 rows affected

```

#### 将一级 Hash 分区表修改为模板化 Hash+Range 二级分区表的示例

```sql
obclient> ALTER TABLE tbl2
       PARTITION BY HASH(c1)
                SUBPARTITION BY RANGE COLUMNS(c2)
                SUBPARTITION TEMPLATE(
                SUBPARTITION p1 VALUES LESS THAN ('2016-10-10'),
                SUBPARTITION p2 VALUES LESS THAN ('2116-3-30')) PARTITIONS 2;
Query OK, 0 rows affected

```

#### 将 Hash + Range 二级分区表转为 Range Columns+Range Columns 分区示例

```sql
obclient> ALTER TABLE tbl2
   PARTITION BY RANGE COLUMNS(c1)
    SUBPARTITION BY RANGE COLUMNS(c2)
  (
  PARTITION p0 VALUES LESS THAN (100)
  (SUBPARTITION sp0 VALUES LESS THAN ('2020-01-01')
  ,SUBPARTITION sp1 VALUES LESS THAN  ('2021-01-01')
  ,SUBPARTITION sp2 VALUES LESS THAN ('2022-01-01')
  ,SUBPARTITION sp3 VALUES LESS THAN  ('2023-01-01')
  ),
  PARTITION p1 VALUES LESS THAN (200)
  (SUBPARTITION sp4 VALUES LESS THAN ('2020-01-01')
  ,SUBPARTITION sp5 VALUES LESS THAN  ('2021-01-01')
  ,SUBPARTITION sp6 VALUES LESS THAN ('2022-01-01')
  ,SUBPARTITION sp7 VALUES LESS THAN  ('2023-01-01'))
  );
Query OK, 0 rows affected

```

## 添加分区

添加分区的语法如下：

```sql
ALTER TABLE table_name ADD PARTITION (partition_definition)

```

添加分区的示例如下：

```sql
obclient> CREATE TABLE t_log_part_by_range (
      log_id      bigint NOT NULL
      , log_value varchar(50)
      , log_date  timestamp NOT NULL  
  ) PARTITION BY RANGE(UNIX_TIMESTAMP(log_date))
  (
      PARTITION M202001 VALUES LESS THAN(UNIX_TIMESTAMP('2020/02/01'))
      , PARTITION M202002 VALUES LESS THAN(UNIX_TIMESTAMP('2020/03/01'))
      , PARTITION M202003 VALUES LESS THAN(UNIX_TIMESTAMP('2020/04/01'))
      , PARTITION M202004 VALUES LESS THAN(UNIX_TIMESTAMP('2020/05/01'))
      , PARTITION M202005 VALUES LESS THAN(UNIX_TIMESTAMP('2020/06/01'))
  );
  Query OK, 0 rows affected

  obclient> ALTER TABLE t_log_part_by_range ADD PARTITION
           (PARTITION M202006 VALUES LESS THAN(UNIX_TIMESTAMP('2020/07/01'))
           );

```

## 删除分区

删除分区的语法如下：

```sql
ALTER TABLE table_name DROP PARTITION partition_name;

```

删除分区的示例如下：

```sql
obclient> ALTER TABLE t_log_part_by_range DROP PARTITION M202006;
Query OK, 0 rows affected

```

## 清空分区

清空分区的语法如下:

```sql
ALTER TABLE table_name TRUNCATE PARTITION partition_name;

```

清空分区的示例如下：

  obclient> ALTER TABLE t_log_part_by_range TRUNCATE PARTITION M202001, M202002;
  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) 咨询热线