http://www.cnblogs.com/mliudong/p/3625522.html
ALTER TABLE E_info PARTITION BY RANGE(DATE(dt) DIV 100)
(
PARTITION p_2015_01 VALUES LESS THAN (201501),
PARTITION p_2015_02 VALUES LESS THAN (201502),
PARTITION p_2015_03 VALUES LESS THAN (201503),
PARTITION p_2015_04 VALUES LESS THAN (201504),
PARTITION p_2015_05 VALUES LESS THAN (201505),
PARTITION p_2015_06 VALUES LESS THAN (201506),
PARTITION p_2015_07 VALUES LESS THAN (201507),
PARTITION p_2015_08 VALUES LESS THAN (201508),
PARTITION p_2015_09 VALUES LESS THAN (201509),
PARTITION p_2015_10 VALUES LESS THAN (201510),
PARTITION p_2015_11 VALUES LESS THAN (201511),
PARTITION p_2015_12 VALUES LESS THAN (201512),
PARTITION p_catch_all VALUES LESS THAN MAXVALUE
);
如果是采用这种方法,也会报错:
mysql> CREATE TABLE t2 (
-> id int DEFAULT NULL,
-> name varchar(20) DEFAULT NULL,
-> t timestamp
-> )
-> partition by range(t)
-> (
-> PARTITION p201001 VALUES LESS THAN ('2010-02-01'),
->
-> PARTITION p201002 VALUES LESS THAN ('2010-02-03'),
->
-> PARTITION pcatchall VALUES LESS THAN MAXVALUE
-> );
ERROR 1697 (HY000): VALUES value for partition 'p201001' must have type INT
mysql>
我在mysql 5.6中试了一下,如果是timestamp类型,在创建range分区表时会报错:
mysql> CREATE TABLE t1 (
-> id int DEFAULT NULL,
-> name varchar(20) DEFAULT NULL,
-> t timestamp
-> )
-> partition by range(YEAR(t)*100+MONTH(t))
-> (
-> PARTITION p201001 VALUES LESS THAN (201002),
->
-> PARTITION p201002 VALUES LESS THAN (201003),
->
-> PARTITION p201003 VALUES LESS THAN (201004),
->
-> PARTITION p201004 VALUES LESS THAN (201005),
->
-> PARTITION p201005 VALUES LESS THAN (201006),
->
-> PARTITION p201006 VALUES LESS THAN (201007),
->
-> PARTITION p201007 VALUES LESS THAN (201008),
->
-> PARTITION p201008 VALUES LESS THAN (201009),
->
-> PARTITION p201009 VALUES LESS THAN (201010),
->
-> PARTITION pcatchall VALUES LESS THAN MAXVALUE
-> );
ERROR 1486 (HY000): Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
mysql>