MySQL 日期时间分区问题

        使用日期列进行分区,在工作中非常常见,但有时候总会遇到一些奇怪的问题。特此记录首次用TIMESTAMP列做分区时遇到的问题(因为此列收到数据库时区影响是不支持分区的,下文会详细描述)

        因为需求中,需要保存到毫秒,又感觉TIMESTAMP占用空间较小,所以开始时采用TIMESTAMP(3)数据类型。关于数据类型保存到毫秒,以及占用空间见MySQL 日期时间类型精确到毫秒_宝哥66的博客-CSDN博客

        因为是日志表数据量较大,所以采取按天分区,方便查询较快。查询资料,使用函数UNIX_TIMESTAMP写出sql

ALTER TABLE `s_schedule_log` 
PARTITION BY RANGE COLUMNS (start_time) (
PARTITION p202204 VALUES LESS THAN (UNIX_TIMESTAMP('2022-05-01 00:00:00.000')),
PARTITION p20220501 VALUES LESS THAN (UNIX_TIMESTAMP('2022-05-02 00:00:00.000'))
);

        报错:Error Code: 1659. Field 'start_time' is of a not allowed type for this type of partitioning。字段“start_time”是此类分区不允许的类型。既然如此就不采用列分区了,直接采用范围分区进行尝试

ALTER TABLE `s_schedule_log` 
PARTITION BY RANGE (UNIX_TIMESTAMP(start_time)) (
PARTITION p202204 VALUES LESS THAN (UNIX_TIMESTAMP('2022-05-01 00:00:00.000')),
PARTITION p20220501 VALUES LESS THAN (UNIX_TIMESTAMP('2022-05-02 00:00:00.000'))
);

        报错:Error Code: 1697. VALUES value for partition 'p202204' must have type INT。分区“p202204”的 VALUES 值必须为 INT 类型。此时使用select UNIX_TIMESTAMP('2022-05-01 00:00:00.000');发现打印果然是小数。决定寻找其他函数,网上居多都是使用这个函数,没有毫秒值时,确实可以使用,但有毫秒值就不行了,因长时间找不到合适的函数,还投机取巧了下,反正是想按天分区时间还有毫秒部分就无所谓了。使用UNIX_TIMESTAMP('2022-05-01 00:00:00')

ALTER TABLE `s_schedule_log` 
PARTITION BY RANGE (UNIX_TIMESTAMP(start_time)) (
PARTITION p202204 VALUES LESS THAN (UNIX_TIMESTAMP('2022-05-01 00:00:00')),
PARTITION p20220501 VALUES LESS THAN (UNIX_TIMESTAMP('2022-05-02 00:00:00'))
);

        报错:Error Code: 1491. The PARTITION function returns the wrong type。分区函数返回错误的类型。这次说的就是UNIX_TIMESTAMP(start_time)这部分有问题了,类型不对,还是得INT。特意还查了下官网,int,字符串,date类型都是直接支持的,带有时间的可以使用UNIX_TIMESTAMP函数,就带有毫秒的麻烦点儿,最后终于找到函数to_days(发现还是日期函数不熟,日期函数库估计有很多值得学习的)

ALTER TABLE `s_schedule_log` 
PARTITION BY RANGE (to_days(start_time)) (
PARTITION p202204 VALUES LESS THAN (to_days('2022-05-01 00:00:00')),
PARTITION p20220501 VALUES LESS THAN (to_days('2022-05-02 00:00:00'))
);

        报错:Error Code: 1486. Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed。常量、随机 或 依赖时区的表达式在分区函数中是不允许的。这不就是说start_time受时区影响了吗,被限制了。此时死心不得不将数据类型改为DATETIME(3)然后继续执行以上sql。发现依然
        报错:Error Code: 1503. A PRIMARY KEY must include all columns in the table's partitioning function。主键必须包含分区函数所有的列。也就是start_time不在主键中。(说实话有点崩溃了,感觉遇到了分区会遇到的所有问题,不过话说回来,遇到问题才能在学习中成长,解决问题是学习的过程中的宝贵的经验)。回头去看官网发现官网示例几乎都是没有创建主键或索引的,所以任意列创建分区都可以。此处已经尝试start_time创建索引后重试依然是这个错误。修改主键

ALTER TABLE `s_schedule_log` 
CHANGE COLUMN `start_time` `start_time` DATETIME(3) NOT NULL COMMENT '开始时间' ,
DROP PRIMARY KEY,
ADD PRIMARY KEY USING BTREE (`log_id`, `start_time`);
;

        继续执行以上分区SQL,创建分区成功!!!

        分区创建成功,随之需要自动分区,请参考MySQL 定时新增分区_宝哥66的博客-CSDN博客

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值