This section discusses limitations in MySQL Partitioning relating specifically to functions used in partitioning expressions.
Only the MySQL functions shown in the following table are supported in partitioning expressions.
ABS() | CEILING() (see
CEILING() and
FLOOR()) | DAY() |
DAYOFMONTH() | DAYOFWEEK() | DAYOFYEAR() |
DATEDIFF() | EXTRACT() | FLOOR() (see
CEILING() and
FLOOR()) |
HOUR() | MICROSECOND() | MINUTE() |
MOD() | MONTH() | QUARTER() |
SECOND() | TIME_TO_SEC() | TO_DAYS() |
TO_SECONDS() (implemented in MySQL 5.5.0) | UNIX_TIMESTAMP() (permitted in MySQL
5.5.1 and later, with
TIMESTAMP columns) | WEEKDAY() |
YEAR() | YEARWEEK() |
In MySQL 5.5, partition pruning is supported only
for the TO_DAYS(),
TO_SECONDS(), and
YEAR() functions. See
Section 18.4, “Partition Pruning”, for more information.
CEILING() and
FLOOR().
Each of these functions returns an integer only if it is
passed an argument of an exact numeric type, such as one of
the INT types or
DECIMAL. This means, for
example, that the following CREATE
TABLE statement fails with an error, as shown here:
mysql>CREATE TABLE t (c FLOAT) PARTITION BY LIST( FLOOR(c) )(->PARTITION p0 VALUES IN (1,3,5),->PARTITION p1 VALUES IN (2,4,6)->);ERROR 1490 (HY000): The PARTITION function returns the wrong type
See Section 11.6.2, “Mathematical Functions”, for more information about the return types of these functions, as well as Section 10.2, “Numeric Types”.