This section discusses limitations in MySQL Partitioning relating specifically to functions used in partitioning expressions.
Only the MySQL functions shown in the following list are allowed in partitioning expressions:
FLOOR()
(see CEILING() and FLOOR())UNIX_TIMESTAMP()
(withTIMESTAMP
columns)
In MySQL 8.4, partition pruning is supported for
the TO_DAYS()
,
TO_SECONDS()
,
YEAR()
, and
UNIX_TIMESTAMP()
functions. See
Section 26.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
EXTRACT() function with WEEK specifier.
The value returned by the
EXTRACT()
function, when used
as EXTRACT(WEEK FROM
, depends on the
value of the
col
)default_week_format
system
variable. For this reason,
EXTRACT()
is not permitted as a
partitioning function when it specifies the unit as
WEEK
. (Bug #54483)
See Section 14.6.2, “Mathematical Functions”, for more information about the return types of these functions, as well as Section 13.1, “Numeric Data Types”.