1. 背景
OceanBase MySQL 模式不支持 Oracle 的间隔分区（Interval Partition），且动态分区调度不稳定的情况，我们可以通过Event Scheduler + 参数化存储过程实现分区的自动添加与删除，保持分区窗口滚动。本方案提供了通用的存储过程，支持按分钟、小时、天分区，参数化设计可复用到任意符合规范的分区表。
2. 前提条件
- OceanBase 版本 4.x（MySQL 模式）
- 已开启事件调度器：
SET GLOBAL event_scheduler = ON;
- 表采用
RANGE 分区，分区函数为 TO_SECONDS(时间列)
- 分区键必须包含在所有主键和唯一索引中
- 执行操作的用户具有
ALTER、EVENT、EXECUTE 权限
3. 通用存储过程
3.1 存储过程代码
DELIMITER $$
CREATE PROCEDURE manage_partitions(
IN p_table_name VARCHAR(64), -- 表名
IN p_partition_key VARCHAR(64), -- 分区键列名（仅用于可读性，实际函数固定为 TO_SECONDS）
IN p_part_prefix VARCHAR(10), -- 分区名前缀，如 'p' 或 'part'
IN p_keep_count INT, -- 保留的分区个数
IN p_interval INT, -- 每个分区覆盖的时间间隔（数值）
IN p_interval_unit VARCHAR(10) -- 时间单位：DAY / HOUR / MINUTE
)
BEGIN
DECLARE v_now DATETIME DEFAULT NOW();
DECLARE v_last_boundary DATETIME;
DECLARE v_new_boundary DATETIME;
DECLARE v_old_part VARCHAR(64);
DECLARE v_last_part_name VARCHAR(64);
DECLARE v_sql TEXT;
DECLARE v_cur_count INT DEFAULT 0;
DECLARE v_interval_second INT DEFAULT 0;
-- 1. 将间隔转换为秒
SET v_interval_second = CASE UPPER(p_interval_unit)
WHEN 'MINUTE' THEN p_interval * 60
WHEN 'HOUR' THEN p_interval * 3600
WHEN 'DAY' THEN p_interval * 86400
ELSE p_interval * 60
END;
-- 2. 获取当前最后一个分区名称
SELECT PARTITION_NAME INTO v_last_part_name
FROM information_schema.partitions
WHERE table_name = p_table_name AND PARTITION_NAME IS NOT NULL
ORDER BY PARTITION_ORDINAL_POSITION DESC
LIMIT 1;
-- 3. 计算新分区边界（基于最后一个分区边界加上间隔）
IF v_last_part_name IS NOT NULL THEN
SET v_last_boundary = STR_TO_DATE(SUBSTRING(v_last_part_name, LENGTH(p_part_prefix) + 1), '%Y%m%d%H%i%s');
SET v_new_boundary = DATE_ADD(v_last_boundary, INTERVAL v_interval_second SECOND);
ELSE
-- 表尚无分区时，以当前时间 + 保留数*间隔 作为初始边界
SET v_new_boundary = DATE_ADD(v_now, INTERVAL p_keep_count * p_interval MINUTE);
END IF;
-- 4. 添加新分区（忽略已存在的错误）
SET @new_part = CONCAT(p_part_prefix, DATE_FORMAT(v_new_boundary, '%Y%m%d%H%i%s'));
SET @boundary_str = DATE_FORMAT(v_new_boundary, '%Y-%m-%d %H:%i:%s');
SET v_sql = CONCAT(
'ALTER TABLE ', p_table_name,
' ADD PARTITION (PARTITION ', @new_part,
' VALUES LESS THAN (TO_SECONDS(''', @boundary_str, ''')))'
);
BEGIN
DECLARE CONTINUE HANDLER FOR 1504, 1526 BEGIN END;
SET @ddl = v_sql;
PREPARE stmt FROM @ddl;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END;
-- 5. 若分区总数超过保留数，删除最旧的分区（忽略不存在的错误）
SELECT COUNT(*) INTO v_cur_count
FROM information_schema.partitions
WHERE table_name = p_table_name AND PARTITION_NAME IS NOT NULL;
IF v_cur_count > p_keep_count THEN
SELECT PARTITION_NAME INTO v_old_part
FROM information_schema.partitions
WHERE table_name = p_table_name AND PARTITION_NAME IS NOT NULL
ORDER BY PARTITION_ORDINAL_POSITION
LIMIT 1;
SET v_sql = CONCAT('ALTER TABLE ', p_table_name, ' DROP PARTITION ', v_old_part);
BEGIN
DECLARE CONTINUE HANDLER FOR 1505, 1517 BEGIN END;
SET @ddl = v_sql;
PREPARE stmt FROM @ddl;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END;
END IF;
END$$
DELIMITER ;
3.2 参数说明
| 参数 |
示例 |
说明 |
p_table_name |
'test_log' |
分区表名称 |
p_partition_key |
'create_time' |
分区键列名，当前版本用于可读性，实际要求表的分区函数为 TO_SECONDS(该列) |
p_part_prefix |
'p' |
分区命名前缀，如 p 则生成分区名 p20260708162500 |
p_keep_count |
5 |
保持的分区数量，超过后自动删除最旧分区 |
p_interval |
5 |
每个分区覆盖的时间间隔数值 |
p_interval_unit |
'MINUTE' |
间隔单位，支持 DAY、HOUR、MINUTE |
3.3 调用示例
-- 每5分钟一个分区，保留5个分区
CALL manage_partitions('test_log', 'create_time', 'p', 5, 5, 'MINUTE');
-- 每天一个分区，保留18天
CALL manage_partitions('order_log', 'log_date', 'part', 18, 1, 'DAY');
-- 每小时一个分区，保留48小时
CALL manage_partitions('sensor_data', 'ts', 'p', 48, 1, 'HOUR');
4. 测试用例（每5分钟一个分区，保留5个）
以下测试基于当前时间 16:15 进行演示。
4.1 创建表并初始化5个分区
CREATE TABLE test_log (
id bigint NOT NULL AUTO_INCREMENT,
create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
data VARCHAR(100),
PRIMARY KEY (id, create_time)
) PARTITION BY RANGE (TO_SECONDS(create_time)) (
PARTITION p20260708160000 VALUES LESS THAN (TO_SECONDS('2026-07-08 16:00:00')),
PARTITION p20260708160500 VALUES LESS THAN (TO_SECONDS('2026-07-08 16:05:00')),
PARTITION p20260708161000 VALUES LESS THAN (TO_SECONDS('2026-07-08 16:10:00')),
PARTITION p20260708161500 VALUES LESS THAN (TO_SECONDS('2026-07-08 16:15:00')),
PARTITION p20260708162000 VALUES LESS THAN (TO_SECONDS('2026-07-08 16:20:00'))
);
4.2 手动调用一次观察结果
CALL manage_partitions('test_log', 'create_time', 'p', 5, 5, 'MINUTE');
-- 查看分区
SELECT PARTITION_NAME, PARTITION_DESCRIPTION
FROM information_schema.partitions
WHERE table_name = 'test_log'
ORDER BY PARTITION_ORDINAL_POSITION;
预期：新增分区 p20260708162500，最旧分区 p20260708160000 被删除，总数保持5个。
4.3 创建定时事件（每天定时执行）
CREATE EVENT daily_manage_test_partition
ON SCHEDULE EVERY 1 DAY
STARTS '2026-07-08 16:25:00'
ON COMPLETION PRESERVE
ENABLE
DO
CALL manage_partitions('test_log', 'create_time', 'p', 5, 5, 'MINUTE');
4.4 验证自动化效果
-- 等待数分钟后查询
SELECT NOW(), PARTITION_NAME
FROM information_schema.partitions
WHERE table_name = 'test_log'
ORDER BY PARTITION_ORDINAL_POSITION;
5. 事件管理命令
-- 查看所有事件
SHOW EVENTS;
-- 查看事件详情
SELECT EVENT_NAME, STATUS, LAST_EXECUTED, NEXT_EXECUTION_TIME
FROM information_schema.EVENTS WHERE EVENT_NAME = 'auto_partition_5min'\G
-- 停用/启用事件
ALTER EVENT auto_partition_5min DISABLE;
ALTER EVENT auto_partition_5min ENABLE;
-- 删除事件
DROP EVENT IF EXISTS auto_partition_5min;
6. 注意事项
- 调度器开启：
SET GLOBAL event_scheduler = ON;，重启后需重新设置或写入配置文件。
- 分区键约束：分区列必须包含在所有主键和唯一索引中，否则无法创建分区表。
- 分区函数固定：存储过程假定分区函数为
TO_SECONDS(列名)，若你的表使用 TO_DAYS() 或 UNIX_TIMESTAMP()，需相应修改存储过程中的表达式。
- 初始分区名必须匹配前缀：存储过程从分区名提取边界时间，因此初始分区命名必须符合
前缀+时间戳 格式（如 p20260708160000），否则需先调整或由存储过程重新生成。
- 时区：存储过程使用
NOW() 和 CURDATE()，请确保数据库时区与业务一致。
- 错误处理：已内置忽略“分区已存在”“分区不存在”等常见错误，保证事件可重复执行。
- 分区上限：OceanBase 单表建议分区数不超过 8192 个，请结合保留策略评估。
- 空间回收：
DROP PARTITION 立即释放数据，但磁盘空间回收需依赖后续合并（Major Freeze），不影响业务。
7. 清理环境
DROP EVENT IF EXISTS auto_partition_5min;
DROP TABLE IF EXISTS test_log;
DROP PROCEDURE IF EXISTS manage_partitions;
8. 常见问题
Q：手动调用成功，事件没有执行？
A：检查 event_scheduler 是否为 ON；查看 NEXT_EXECUTION_TIME 是否已过；确保存储过程存在且未报错。
Q：删除分区时提示 ERROR 1507？
A：初始分区名不符合 前缀+时间戳 规则，导致动态计算的旧分区名不存在。改用基于 PARTITION_ORDINAL_POSITION 直接获取最旧分区名可解决（本存储过程已采用该方法）。
Q：分区边界没有对齐整点？
A：新分区边界基于最后一个分区的边界增加间隔，若初始边界对齐，后续将保持对齐。若需要强制对齐到整点（如每5分钟的0、5、10…），可在存储过程中对 v_now 进行截断处理。
