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 allowed in partitioning expressions.
In MySQL 5.6, range optimization can be used for
YEAR() functions. In addition,
beginning with MySQL 5.6.3,
UNIX_TIMESTAMP() is treated as
monotonic in partitioning expressions. See
Section 19.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
INT types or
DECIMAL. This means, for
example, that the following
TABLE statement fails with an error, as shown here:
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
EXTRACT() function with WEEK specifier.
The value returned by the
EXTRACT() function, when used
, depends on the
value of the
variable. For this reason, beginning with MySQL 5.6.2,
EXTRACT() is no longer
permitted as a partitioning function when it specifies the
WEEK. (Bug #54483)
Copyright © 1997, 2015, Oracle and/or its affiliates. All rights reserved. Legal Notices