--- title: _enable_add_between_range_partitions 租户隐藏配置项的使用说明-OceanBase数据库使用指南 description: 了解OceanBase数据库在实际应用中关于_enable_add_between_range_partitions 租户隐藏配置项的使用说明相关的常见问题和使用技巧，帮助您快速解决_enable_add_between_range_partitions 租户隐藏配置项的使用说明的难题。 --- 切换语言 - 中文站 - 简体中文 - International - English - 日本站 - 日本語 划线反馈 # _enable_add_between_range_partitions 租户隐藏配置项的使用说明 更新时间：2026-06-10 01:56 适用版本： V3.1.x、V3.2.x 内容类型：TechNote OceanBase 数据库禁止对于 range 分区的分区表删除分区后，再添加之前的分区，执行 add partition 会报 `VALUES LESS THAN value must be strictly increasing for each partition`。这么做的目的是为了保证分区内数据的准确性，防止新增旧分区后，有的分区中存在不属于该分区的数据。如果需要解除这个限制需要打开隐藏参数 `_enable_add_between_range_partitions`。但使用该参数需要注意：表中的数据跟要新增的旧分区不能存在交集。 - 直接添加之前删除分区，会直接报错。如果开启租户侧隐含参数 `_enable_add_between_range_partitions` 后执行成功。测试例子如下。 1. 创建分区表 ENT_SHAREHOLDER_ALTER_HIS 。 ```shell MySQL [test]> CREATE TABLE ENT_SHAREHOLDER_ALTER_HIS ( ENT_NAME varchar(900) COLLATE utf8mb4_bin NOT NULL, ACC_DATE date NOT NULL ) partition by range columns(ACC_DATE) (partition p20230924 values less than ('2023-09-24'), partition p20230925 values less than ('2023-09-25'), partition p20230926 values less than ('2023-09-26'), partition p20230927 values less than ('2023-09-27'), partition p20230928 values less than ('2023-09-28'), partition p20230929 values less than ('2023-09-29'), partition p20230930 values less than ('2023-09-30'), partition p20231001 values less than ('2023-10-01'), partition p20231002 values less than ('2023-10-02')); Query OK, 0 rows affected (2.018 sec) ``` 2. 删除分区 p20230926 。 ```shell MySQL [test]> alter table ENT_SHAREHOLDER_ALTER_HIS drop partition p20230926; Query OK, 0 rows affected (0.17 sec) ``` 3. 添加旧分区 p20230926 。 ```shell MySQL [test]> alter table ENT_SHAREHOLDER_ALTER_HIS add partition (partition p20230926 values less than ('2023-09-26')); ERROR 1493 (HY000): VALUES LESS THAN value must be strictly increasing for each partition ``` 4. 设置租户配置项 `_enable_add_between_range_partitions` 。 ```shell MySQL [oceanbase]> alter system set _enable_add_between_range_partitions=true tenant='xxxx'; ``` 5. 设置租户配置项 `_enable_add_between_range_partitions` 之后添加旧分区 p20230926 。 ```shell MySQL [test]> alter table ENT_SHAREHOLDER_ALTER_HIS add partition (partition p20230926 values less than ('2023-09-26')); Query OK, 0 rows affected (0.15 sec) ``` - 如果在添加旧分区时候，此时有之前数据插入进来，会导致分区裁剪后的数据的正确性，测试例子如下。 1. 删除分区 p20230926 ```shell MySQL [test]> alter table ENT_SHAREHOLDER_ALTER_HIS drop partition p20230926; Query OK, 0 rows affected (0.061 sec) ``` 2. 向分区表 ENT_SHAREHOLDER_ALTER_HIS 插入数据。 ```shell MySQL [test2]> insert into ENT_SHAREHOLDER_ALTER_HIS values ('a','2023-09-26'); Query OK, 1 row affected (0.027 sec) ``` ``` ```shell MySQL [test2]> insert into ENT_SHAREHOLDER_ALTER_HIS values ('a','2023-09-25'); Query OK, 1 row affected (0.013 sec) ``` 3. 查询分区数据。 ```shell MySQL [test2]> select * from ENT_SHAREHOLDER_ALTER_HIS partition (p20230927); ``` 输出结果如下： ```shell +----------+------------+ | ENT_NAME | ACC_DATE | +----------+------------+ | a | 2023-09-26 | | a | 2023-09-26 | | a | 2023-09-25 | +----------+------------+ 3 rows in set (0.047 sec) ``` 4. 添加旧分区 p20230926 。 ```shell MySQL [test]> alter table ENT_SHAREHOLDER_ALTER_HIS add partition (partition p20230926 values less than ('2023-09-26')); Query OK, 0 rows affected (0.21 sec) ``` 5. 查询分区数据。 ```shell MySQL [test]> select * from ENT_SHAREHOLDER_ALTER_HIS partition (p20230926); Empty set (0.08 sec) ``` 6. 通过 ACC_DATE 字段查询分区表 ENT_SHAREHOLDER_ALTER_HIS 。 ```shell MySQL [test]> select * from ENT_SHAREHOLDER_ALTER_HIS where ACC_DATE ='2023-09-25' ; Empty set (0.12 sec) ``` 7. 展示通过 ACC_DATE 字段查询分区表 ENT_SHAREHOLDER_ALTER_HIS 的执行计划。 ```shell MySQL [test]> explain select * from ENT_SHAREHOLDER_ALTER_HIS where ACC_DATE ='2023-09-25'; ``` 输出结果如下： ```shell +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Query Plan | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ======================================================== |ID|OPERATOR |NAME |EST. ROWS|COST| -------------------------------------------------------- |0 |TABLE SCAN|ent_shareholder_alter_his|990 |383 | ======================================================== Outputs & filters: ------------------------------------- 0 - output([ent_shareholder_alter_his.ENT_NAME], [ent_shareholder_alter_his.ACC_DATE]), filter(nil), access([ent_shareholder_alter_his.ACC_DATE], [ent_shareholder_alter_his.ENT_NAME]), partitions(p10) | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.036 sec) ``` 上述测试例子可以看到，开启隐藏参数 `_enable_add_between_range_partitions` 后，当表中数据与新增的旧分区存在交集时候，进行添加分区操作后，分区裁剪下存在数据正确性问题。 #### 说明 计划中 `p10` 裁剪出来的分区是新增的 `p20230926`，综上所述不建议打开 `_enable_add_between_range_partitions`来添加之前被删除的分区，如有依赖，需要保证，开启隐藏参数后添加之前的分区前，保证没有新增旧分区数据写入。 ## 适用版本 OceanBase 数据库 V3.x 版本。 上一篇 [设置租户变量 max_connections 无效](https://www.oceanbase.com/knowledge-base/oceanbase-database-1000000000207666) 下一篇 [如何设置租户全局只读](https://www.oceanbase.com/knowledge-base/oceanbase-database-20000000082) ![有帮助](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) 咨询热线