【 使用环境 】生产环境
【 OB or 其他组件 】OBserver
【 使用版本 】4.2.1 BP10
【问题描述】hash+range 创建二级分区表（通过二级分区模板SUBPARTITION TEMPLATE），如何批量删除所有一级分区下同名的二级子分区。
该表从分库分表模式迁入OB，表结构示例如下：
CREATE TABLE T_test ( ID bigint(20) NOT NULL , Options bigint(20) NOT NULL DEFAULT ‘0’ , Type smallint(6) NOT NULL , part_Sharding bigint(20) NOT NULL , CreateTime bigint(20) NOT NULL
PRIMARY KEY (ID, part_Sharding, CreateTime),
) DEFAULT CHARSET=utf8mb4 TABLEGROUP = tg_mutil
PARTITION BY HASH(part_Sharding)
SUBPARTITION BY RANGE (CreateTime)
SUBPARTITION TEMPLATE
(SUBPARTITION p20250109 values less than (1736352000000),
SUBPARTITION p20250116 values less than (1736956800000),
SUBPARTITION p20250123 values less than (1737561600000),
SUBPARTITION p20250130 values less than (1738166400000),
SUBPARTITION p20250206 values less than (1738771200000),
SUBPARTITION p20250213 values less than (1739376000000),
SUBPARTITION p20250220 values less than (1739980800000),
SUBPARTITION p20250227 values less than (1740585600000),
SUBPARTITION p20250306 values less than (1741190400000),
SUBPARTITION p20250313 values less than (1741795200000),
SUBPARTITION p20250320 values less THAN MAXVALUE)PARTITIONS 300;
– 创建存储过程
DELIMITER //
CREATE PROCEDURE delete_subpartition_batch()
BEGIN
– 声明变量
DECLARE v_partition_name VARCHAR(255);
DECLARE v_done INT DEFAULT 0;
DECLARE cur_partition CURSOR FOR
– 查询所有一级分区的名称
SELECT partition_name
FROM information_schema.partitions
WHERE table_name = ‘T_test’
AND subpartition_name IS NULL;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = 1;
-- 打开游标
OPEN cur_partition;
-- 循环遍历一级分区
partition_loop: LOOP
-- 从游标中获取一级分区名称
FETCH cur_partition INTO v_partition_name;
IF v_done THEN
LEAVE partition_loop;
END IF;
-- 构造删除指定二级分区的 SQL 语句
SET @delete_sql = CONCAT('ALTER TABLE T_test DROP PARTITION ', v_partition_name, 'p20250109');
-- 准备并执行动态 SQL
PREPARE stmt FROM @delete_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END LOOP;
-- 关闭游标
CLOSE cur_partition;
SET @table_name = 'T_test';
SET @subpartition_name = 'p20250109';
SELECT CONCAT('ALTER TABLE ', @table_name, ' TRUNCATE SUBPARTITION ', @subpartition_name, ';')
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME = @table_name AND SUBPARTITION_NAME = @subpartition_name;
-- 将生成的 SQL 执行即可
(2) 重建表以移除不需要的二级分区
如果需要彻底移除某些二级分区，可以考虑重建表。具体步骤如下：
创建一个新表，定义所需的分区结构（排除不需要的二级分区）。
使用 INSERT INTO ... SELECT 将原表数据迁移到新表。
替换原表为新表。
示例：
CREATE TABLE T_test_new (
ID bigint(20) NOT NULL,
Options bigint(20) NOT NULL DEFAULT '0',
Type smallint(6) NOT NULL,
part_Sharding bigint(20) NOT NULL,
CreateTime bigint(20) NOT NULL,
PRIMARY KEY (ID, part_Sharding, CreateTime)
) DEFAULT CHARSET=utf8mb4 TABLEGROUP = tg_mutil
PARTITION BY HASH(part_Sharding)
SUBPARTITION BY RANGE (CreateTime)
SUBPARTITION TEMPLATE (
SUBPARTITION p20250116 values less than (1736956800000),
SUBPARTITION p20250123 values less than (1737561600000),
-- 其他需要保留的二级分区
) PARTITIONS 300;
INSERT INTO T_test_new SELECT * FROM T_test;
RENAME TABLE T_test TO T_test_old, T_test_new TO T_test;
DROP TABLE T_test_old;
4. 总结
问题原因：HASH + RANGE 的二级分区表在 OceanBase 数据库中不支持删除二级分区。
