mysql - 为什么在日期时间列上对 MySQL 表进行分区会产生依赖于时区的错误？

Question

我遇到过几个问题，比如我的问题:

• > MySQL table partitioning on timestamp
• > MySQL Database Error: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed

还有一些。他们都解释了同样的问题，但他们不是我的问题。

我的问题是我的列是datetime NOT NULL 并且我通过表id 将其设为主键:

PRIMARY KEY (id, start_time)

现在我使用下面的命令对我的表进行分区:

alter table MY_TABLE partition by range (UNIX_TIMESTAMP(start_time)) ( 
partition p2012 values less than (UNIX_TIMESTAMP('2012-01-01 00:00:00')),
partition p2013 values less than (UNIX_TIMESTAMP('2013-01-01 00:00:00')), 
partition p2014 values less than (UNIX_TIMESTAMP('2014-01-01 00:00:00')), 
partition p2015 values less than (UNIX_TIMESTAMP('2015-01-01 00:00:00')), 
partition p2016 values less than (UNIX_TIMESTAMP('2016-01-01 00:00:00')),
partition p2017 values less than (UNIX_TIMESTAMP('2017-01-01 00:00:00')), 
partition pfuture values less than (MAXVALUE));

一切似乎都是正确的，当我describe MY_TABLE时，它提供了以下信息:

start_time        | datetime         | NO   | PRI | 0000-00-00 00:00:00

当运行上面的命令进行分区时，我得到以下错误:

ERROR 1486 (HY000): Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed

datetime 不像 timestamp 那样依赖时区，也不是常量。这种错误的原因可能是什么？

Answer 1

22.2.1 RANGE Partitioning

...

Partition the table by RANGE COLUMNS, using a DATE or DATETIME column as the partitioning column.

...

尝试:

ALTER TABLE `MY_TABLE`
PARTITION BY RANGE COLUMNS (`start_time`)
(
  PARTITION `p2012` VALUES LESS THAN ('2012-01-01 00:00:00'),
  PARTITION `p2013` VALUES LESS THAN ('2013-01-01 00:00:00'), 
  PARTITION `p2014` VALUES LESS THAN ('2014-01-01 00:00:00'), 
  PARTITION `p2015` VALUES LESS THAN ('2015-01-01 00:00:00'), 
  PARTITION `p2016` VALUES LESS THAN ('2016-01-01 00:00:00'),
  PARTITION `p2017` VALUES LESS THAN ('2017-01-01 00:00:00'), 
  PARTITION `pfuture` VALUES LESS THAN (MAXVALUE)
);

参见 db-fiddle .