ERROR 1564 (HY000)
When we used round() function for the partition key, we got ERROR 1564 (HY000).
mysql> alter table sales partition by range(unix_timestamp(order_time)) partitions 1 (partition p2013 values less than (round(unix_timestamp('2014-01-01 00:00:00'))) engine=innodb);
ERROR 1564 (HY000): This partition function is not allowed
OK, round() is not allowed, and what function should I use to convert it back to integer?
According to MySQL :: MySQL 8.0 Reference Manual :: 24.6.3 Partitioning Limitations Relating to Functions. There're 23 functions are allowable to be used for partition key, round() is not included.
- ABS()
- CEILING()
- DATEDIFF()
- DAY()
- DAYOFMONTH()
- DAYOFWEEK()
- DAYOFYEAR()
- EXTRACT()
- FLOOR()
- HOUR()
- MICROSECOND()
- MINUTE()
- MOD()
- MONTH()
- QUARTER()
- SECOND()
- TIME_TO_SEC()
- TO_DAYS()
- TO_SECONDS()
- UNIX_TIMESTAMP() (with TIMESTAMP columns)
- WEEKDAY()
- YEAR()
- YEARWEEK()