mysql 分区不允许时间,MySQL数据库错误：（子）分区函数中不允许使用常量，随机或时区相关的表达式...

I was tried to make partition by the timestamp in mysql table. But it is returning an error

CREATE TABLE tblemployeepunch (

fld_id int(11) NOT NULL AUTO_INCREMENT,

fld_date Varchar(15) DEFAULT NULL,

fld_rawpunchdate varchar(25) DEFAULT NULL,

fld_rawpunchtime varchar(25) DEFAULT NULL,

fld_cardno varchar(50) DEFAULT NULL,

fld_reasoncard varchar(20) DEFAULT NULL,

fld_mode varchar(20) DEFAULT NULL,

fld_punchdatetime varchar(50) DEFAULT NULL,

fld_crtdate TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

UNIQUE KEY fld_id (fld_id,fld_crtdate),

KEY in_timesheet (fld_cardno,fld_punchdatetime,fld_mode,fld_rawpunchtime),

KEY in_emppunch (fld_cardno,fld_rawpunchdate,fld_punchdatetime)

)

PARTITION BY HASH(DAYOFYEAR(fld_crtdate))

PARTITIONS 12;

解决方案

Well, the error message is quite clear. Don't partition by values which are in a timezone-dependent column. Use a column which has datatype date, datetime or int (by calculating DAYOFYEAR(fld_crtdate) beforehand in a separate column) or any other.