MySQL 分区错误 - 错误代码 : 1486

Question

MySQL 在表上创建分区时抛出错误。

错误代码:1486不允许在(子)分区函数中使用常量、随机或时区相关的表达式。

我试过以下查询:

 alter table test.tbl1 
 partition by range(unix_timestamp(sys_time))
(
PARTITION p20151001 VALUES LESS THAN (unix_timestamp('2015-10-01')),
PARTITION p20151101 VALUES LESS THAN (unix_timestamp('2015-11-01')),
PARTITION p20151201 VALUES LESS THAN (unix_timestamp('2015-12-01')),
PARTITION p20160101 VALUES LESS THAN (unix_timestamp('2016-01-01')),
PARTITION p20160201 VALUES LESS THAN (unix_timestamp('2016-02-01')),
PARTITION p20160301 VALUES LESS THAN (unix_timestamp('2016-03-01'))
);

我该如何解决这个问题。提前致谢

Answer 1

读到这里可能你使用的是MYSQL 5.1:

https://dev.mysql.com/tech-resources/articles/mysql_55_partitioning.html

Another pain point in MySQL 5.1 is the handling of date columns. You can't use them directly, but you need to convert such columns using either YEAR or TO_DAYS

如果您的列 sys_time 是一个 DATETIME，您不需要指定时间戳来对其进行分区，您只需要执行 TO_DAYS，因为你不是按年做的:

alter table test.tbl1 
partition by range (TO_DAYS(sys_time))
(
PARTITION p20151001 VALUES LESS THAN (TO_DAYS('2015-10-01')),
PARTITION p20151101 VALUES LESS THAN (TO_DAYS('2015-11-01')),
PARTITION p20151201 VALUES LESS THAN (TO_DAYS('2015-12-01')),
PARTITION p20160101 VALUES LESS THAN (TO_DAYS('2016-01-01')),
PARTITION p20160201 VALUES LESS THAN (TO_DAYS('2016-02-01')),
PARTITION p20160301 VALUES LESS THAN (TO_DAYS('2016-03-01'))
);

如果 sys_time 是一个 TIMESTAMP 那么你不需要将你的时间戳转换为时间戳，我已经把它超出了参数范围:

 alter table test.tbl1 
 partition by range(sys_time)
(
PARTITION p20151001 VALUES LESS THAN (unix_timestamp('2015-10-01')),
PARTITION p20151101 VALUES LESS THAN (unix_timestamp('2015-11-01')),
PARTITION p20151201 VALUES LESS THAN (unix_timestamp('2015-12-01')),
PARTITION p20160101 VALUES LESS THAN (unix_timestamp('2016-01-01')),
PARTITION p20160201 VALUES LESS THAN (unix_timestamp('2016-02-01')),
PARTITION p20160301 VALUES LESS THAN (unix_timestamp('2016-03-01'))
);