mysql - 使用 TIMESTAMP 月份进行分区

Question

我有一个员工表，我需要根据时间戳的月份对其进行分区。我尝试了一些类型但没有用。

CREATE TABLE employees (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30),
hired DATE NOT NULL DEFAULT '1970-01-01',
separated TIMESTAMP NOT NULL DEFAULT '9999-12-31',
job_code INT,
store_id INT
)
PARTITION BY RANGE ( date_format(separated,'%m') ) (
PARTITION p0 VALUES LESS THAN (01),
PARTITION p1 VALUES LESS THAN (02),
PARTITION p2 VALUES LESS THAN (03),
 ----
PARTITION p4 VALUES LESS THAN (04)

);

创建此表时显示不允许使用此分区函数如果我使用 MONTH(seperated) 它适用于 DATE 类型但它返回 invalid default value for separated

Answer 1

我们如何在我们的项目中做到这一点(可能不是正确的方式，但对我们有用)-(MySQL 5.1.60)

PARTITION p201 VALUES LESS THAN (TO_DAYS('2012-01-01')),
PARTITION p202 VALUES LESS THAN (TO_DAYS('2012-02-01')),
PARTITION p203 VALUES LESS THAN (TO_DAYS('2012-03-01')),
PARTITION p204 VALUES LESS THAN (TO_DAYS('2012-04-01')),
PARTITION p205 VALUES LESS THAN (TO_DAYS('2012-05-01')),
PARTITION p206 VALUES LESS THAN (TO_DAYS('2012-06-01')),
PARTITION p207 VALUES LESS THAN (TO_DAYS('2012-07-01')),
PARTITION p208 VALUES LESS THAN (TO_DAYS('2012-08-01')),
PARTITION p209 VALUES LESS THAN (TO_DAYS('2012-09-01')),
PARTITION p210 VALUES LESS THAN (TO_DAYS('2012-10-01')),
PARTITION p211 VALUES LESS THAN (TO_DAYS('2012-11-01')),
PARTITION p212 VALUES LESS THAN (TO_DAYS('2012-12-01'))

也就是2012年整年的分区