在 MySQL 中，分区表是一种优化大型表性能的有效手段。对于日期类型的列，MySQL 的查询优化器在处理分区表达式时有一些限制，特别是当涉及到日期函数的时候。具体来说，优化器倾向于支持那些可以被简单地转换为整数的日期函数，这样可以在分区键上进行有效的数值比较。

以下是一些 MySQL 支持的用于日期类型转换的函数，这些函数可以被有效地用于分区定义中：

  1. YEAR(): 返回日期中的年份部分。
  2. TO_DAYS(): 将日期转换为从某个基准日期（如 ‘0000-00-00’）开始的天数。
  3. TO_SECONDS(): 将时间间隔转换为秒数（通常不直接用于日期，但可以用于包含时间的数据）。
  4. UNIX_TIMESTAMP(): 返回 Unix 时间戳，即从 ‘1970-01-01 00:00:00’ UTC 到指定时间的秒数。

示例

假设我们有一个名为 sales 的表，其中记录了销售数据，并且我们想要根据销售日期来进行分区。

使用 YEAR()
CREATE TABLE sales (
    sale_id INT,
    sale_date DATE,
    amount DECIMAL(10,2)
)
PARTITION BY RANGE (YEAR(sale_date))
(
    PARTITION p0 VALUES LESS THAN (2020),
    PARTITION p1 VALUES LESS THAN (2021),
    PARTITION p2 VALUES LESS THAN (2022),
    PARTITION p3 VALUES LESS THAN MAXVALUE
);
使用 TO_DAYS()
CREATE TABLE sales (
    sale_id INT,
    sale_date DATE,
    amount DECIMAL(10,2)
)
PARTITION BY RANGE (TO_DAYS(sale_date))
(
    PARTITION p0 VALUES LESS THAN (736776),  -- 2019-01-01
    PARTITION p1 VALUES LESS THAN (737141),  -- 2020-01-01
    PARTITION p2 VALUES LESS THAN (737506),  -- 2021-01-01
    PARTITION p3 VALUES LESS THAN MAXVALUE
);
使用 UNIX_TIMESTAMP()
CREATE TABLE sales (
    sale_id INT,
    sale_date DATETIME,
    amount DECIMAL(10,2)
)
PARTITION BY RANGE (UNIX_TIMESTAMP(sale_date))
(
    PARTITION p0 VALUES LESS THAN (1546272000),  -- 2019-01-01 00:00:00
    PARTITION p1 VALUES LESS THAN (1609459200),  -- 2021-01-01 00:00:00
    PARTITION p2 VALUES LESS THAN (1640995200),  -- 2022-01-01 00:00:00
    PARTITION p3 VALUES LESS THAN MAXVALUE
);

不支持的函数

MySQL 查询优化器不支持使用其他更复杂的日期函数来定义分区键，例如 DATE_ADD()、DATE_SUB() 或者 TIMESTAMPDIFF() 等，因为它们不能简单地转换为一个可以直接比较的整数值。

例如，下面这样的分区定义是不支持的：

CREATE TABLE sales (
    sale_id INT,
    sale_date DATE,
    amount DECIMAL(10,2)
)
PARTITION BY RANGE (TIMESTAMPDIFF(MONTH, '2020-01-01', sale_date))
(
    PARTITION p0 VALUES LESS THAN (12),
    PARTITION p1 VALUES LESS THAN (24),
    PARTITION p2 VALUES LESS THAN (36),
    PARTITION p3 VALUES LESS THAN MAXVALUE
);

在上面的例子中，TIMESTAMPDIFF() 函数虽然可以计算月份差，但由于它不是一个简单的整数转换函数，所以 MySQL 的查询优化器无法高效地处理这种分区定义。因此，为了获得最佳的性能和分区管理，建议使用上述支持的函数。