Mysql按周分区的方法

关注

原创最新推荐文章于 2026-05-11 13:48:09 发布 1.2w 阅读 AI 写同款· GEO 优化 ›

MySQL分区分表方案实践手册 MySQL分区分表方案实践手册 MySQL分区分表方案实践手册 MySQL分区分表方案实践手册 MySQL分区分表方案实践手册仅供个人学习, 禁止转载及其他商业用途. 立即下载

在一个项目中用到一个日志表，想根据按周来进行分区：

CREATE TABLE T_log(
 id INT(11) NOT NULL AUTO_INCREMENT,
  zoneid INT(11) NOT NULL,
  numcount INT(11) NOT NULL,
  ts TIMESTAMP NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id,ts)
) 
PARTITION BY RANGE (WEEK(ts)) (
    PARTITION p1 VALUES LESS THAN (1),
    PARTITION p2 VALUES LESS THAN (2),
    PARTITION p3 VALUES LESS THAN (3),
    ...
    PARTITION p52 VALUES LESS THAN (52),
    PARTITION p53 VALUES LESS THAN MAXVALUE);

错误码: 1564
This partition function is not allowed
结果报错，week()函数不能作为分区函数。试了weekofyear()函数也不行。

换成yearweek()函数，可以，但又遇到了新问题：

CREATE TABLE T_log(
 id INT(11) NOT NULL AUTO_INCREMENT,
  zoneid INT(11) NOT NULL,
  numcount INT(11) NOT NULL,
  ts TIMESTAMP NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id,ts)
) 
PARTITION BY RANGE (YEARWEEK(ts)) (
    PARTITION p1 VALUES LESS THAN (201401),
    PARTITION p2 VALUES LESS THAN (201402),
    PARTITION p3 VALUES LESS THAN (201403),
    
    PARTITION p51 VALUES LESS THAN (201452),
    PARTITION p52 VALUES LESS THAN MAXVALUE);

错误码: 1486
Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed

报错：常量、随机或者依赖时区的表达式不能作为分区函数。

把ts列换成datetime类型，创建成功。

CREATE TABLE T_log(
 id INT(11) NOT NULL AUTO_INCREMENT,
  zoneid INT(11) NOT NULL,
  numcount INT(11) NOT NULL,
  ts DATETIME NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id,ts)
) 
PARTITION BY RANGE (YEARWEEK(ts)) (
    PARTITION p1 VALUES LESS THAN (201401),
    PARTITION p2 VALUES LESS THAN (201402),
    PARTITION p3 VALUES LESS THAN (201403),
    
    PARTITION p51 VALUES LESS THAN (201452),
    PARTITION p52 VALUES LESS THAN MAXVALUE);