MySQL基于datetime字段按小时创建分区报错求助

错误原因说明 #

错误1697 #

原生RANGE分区仅支持整数类型作为分区键输入值，直接将datetime类型的createdAt作为RANGE的分区参数，不符合类型要求，因此抛出需要INT类型的错误。

错误1493 #

分区上限值没有按照从小到大的顺序排列：首个分区pmin的上限为2021-11-17 08:00:00，后续分区上限为2020年的时间，2021年时间大于2020年，违反了RANGE类分区要求分区上限严格递增的规则，因此报错。

前置调整 #

MySQL要求如果表存在主键/唯一键，分区键必须是所有主键/唯一键的组成部分。当前表主键仅包含id，需要先调整为id+createdAt的联合主键：

ALTER TABLE TEST_1 DROP PRIMARY KEY, ADD PRIMARY KEY(id, createdAt);

正确分区实现 #

提供两种可行的实现方案，任选其一即可：

方案1：使用RANGE COLUMNS直接基于datetime分区 #

无需转换时间格式，直接指定严格递增的时间上限即可：

ALTER TABLE TEST_1 
PARTITION BY RANGE COLUMNS(createdAt) (
    PARTITION p2024052000 VALUES LESS THAN ('2024-05-20 01:00:00'),
    PARTITION p2024052001 VALUES LESS THAN ('2024-05-20 02:00:00'),
    PARTITION p2024052002 VALUES LESS THAN ('2024-05-20 03:00:00'),
    PARTITION p2024052003 VALUES LESS THAN ('2024-05-20 04:00:00'),
    PARTITION p2024052004 VALUES LESS THAN ('2024-05-20 05:00:00'),
    PARTITION pmax VALUES LESS THAN (MAXVALUE)
);

注意：将示例中的时间替换为你实际需要的起始时间即可，保证时间上限严格递增

方案2：转换为时间戳使用原生RANGE分区 #

将createdAt转换为UNIX时间戳整数后分区：

ALTER TABLE TEST_1 
PARTITION BY RANGE (UNIX_TIMESTAMP(createdAt)) (
    PARTITION p2024052000 VALUES LESS THAN (UNIX_TIMESTAMP('2024-05-20 01:00:00')),
    PARTITION p2024052001 VALUES LESS THAN (UNIX_TIMESTAMP('2024-05-20 02:00:00')),
    PARTITION p2024052002 VALUES LESS THAN (UNIX_TIMESTAMP('2024-05-20 03:00:00')),
    PARTITION p2024052003 VALUES LESS THAN (UNIX_TIMESTAMP('2024-05-20 04:00:00')),
    PARTITION p2024052004 VALUES LESS THAN (UNIX_TIMESTAMP('2024-05-20 05:00:00')),
    PARTITION pmax VALUES LESS THAN (MAXVALUE)
);

分区日常维护 #

要满足最多保留5个分区的需求，可配置定时任务（如MySQL事件调度器、系统crontab）每小时执行一次以下操作：

1. 新增下一小时的分区：拆分pmax分区新增对应时间区间的分区

ALTER TABLE TEST_1 REORGANIZE PARTITION pmax INTO (
    PARTITION p2024052005 VALUES LESS THAN ('2024-05-20 06:00:00'),
    PARTITION pmax VALUES LESS THAN (MAXVALUE)
);

2. 删除已处理完成的最早分区：

ALTER TABLE TEST_1 DROP PARTITION p2024052000;

删除分区操作直接释放磁盘空间，性能远高于批量删除数据，符合业务需求。

内容的提问来源于stack exchange，提问作者santhosh