alter table APP_FILESLOG

PARTITION BY RANGE(TO_DAYS(TESTTIME))

(

               PARTITION y2014 VALUES LESS THAN (TO_DAYS("2015-01-01")),

               PARTITION y201501 VALUES LESS THAN (TO_DAYS("2015-02-01")),

               PARTITION y201502 VALUES LESS THAN (TO_DAYS("2015-03-01")),

               PARTITION y201503 VALUES LESS THAN (TO_DAYS("2015-04-01")),

               PARTITION y201504 VALUES LESS THAN (TO_DAYS("2015-05-01")),

               PARTITION y201505 VALUES LESS THAN (TO_DAYS("2015-06-01")),

               PARTITION y201506 VALUES LESS THAN (TO_DAYS("2015-07-01")),

               PARTITION y201507 VALUES LESS THAN (TO_DAYS("2015-08-01")),

               PARTITION y201508 VALUES LESS THAN (TO_DAYS("2015-09-01")),

               PARTITION y201509 VALUES LESS THAN (TO_DAYS("2015-10-01")),

               PARTITION y201510 VALUES LESS THAN (TO_DAYS("2015-11-01")),

               PARTITION y201511 VALUES LESS THAN (TO_DAYS("2015-12-01")),

               PARTITION y201512 VALUES LESS THAN (TO_DAYS("2016-01-01")),

               PARTITION y201601 VALUES LESS THAN (TO_DAYS("2016-02-01")),

               PARTITION y201602 VALUES LESS THAN (TO_DAYS("2016-03-01")),

               PARTITION y201603 VALUES LESS THAN (TO_DAYS("2016-04-01")),

               PARTITION y201604 VALUES LESS THAN (TO_DAYS("2016-05-01")),

               PARTITION y201605 VALUES LESS THAN (TO_DAYS("2016-06-01")),

               PARTITION y201606 VALUES LESS THAN (TO_DAYS("2016-07-01")),

               PARTITION y201607 VALUES LESS THAN (TO_DAYS("2016-08-01")),

               PARTITION y201608 VALUES LESS THAN (TO_DAYS("2016-09-01")),

               PARTITION y201609 VALUES LESS THAN (TO_DAYS("2016-10-01")),

               PARTITION y201610 VALUES LESS THAN (TO_DAYS("2016-11-01")),

               PARTITION y201611 VALUES LESS THAN (TO_DAYS("2016-12-01")),

               PARTITION y201612 VALUES LESS THAN (TO_DAYS("2017-01-01")),

               PARTITION y201701 VALUES LESS THAN (TO_DAYS("2017-02-01")),

               PARTITION y201702 VALUES LESS THAN (TO_DAYS("2017-03-01")),

               PARTITION y201703 VALUES LESS THAN (TO_DAYS("2017-04-01")),

               PARTITION y201704 VALUES LESS THAN (TO_DAYS("2017-05-01")),

               PARTITION y201705 VALUES LESS THAN (TO_DAYS("2017-06-01")),

               PARTITION y201706 VALUES LESS THAN (TO_DAYS("2017-07-01")),

               PARTITION y201707 VALUES LESS THAN (TO_DAYS("2017-08-01")),

               PARTITION y201708 VALUES LESS THAN (TO_DAYS("2017-09-01")),

               PARTITION y201709 VALUES LESS THAN (TO_DAYS("2017-10-01")),

               PARTITION y201710 VALUES LESS THAN (TO_DAYS("2017-11-01")),

               PARTITION y201711 VALUES LESS THAN (TO_DAYS("2017-12-01")),

               PARTITION y201712 VALUES LESS THAN (TO_DAYS("2018-01-01")),

               PARTITION y201801 VALUES LESS THAN (TO_DAYS("2018-02-01")),

               PARTITION y201802 VALUES LESS THAN (TO_DAYS("2018-03-01")),

               PARTITION y201803 VALUES LESS THAN (TO_DAYS("2018-04-01")),

               PARTITION y201804 VALUES LESS THAN (TO_DAYS("2018-05-01")),

               PARTITION y201805 VALUES LESS THAN (TO_DAYS("2018-06-01")),

               PARTITION y201806 VALUES LESS THAN (TO_DAYS("2018-07-01")),

               PARTITION y201807 VALUES LESS THAN (TO_DAYS("2018-08-01")),

               PARTITION y201808 VALUES LESS THAN (TO_DAYS("2018-09-01")),

               PARTITION y201809 VALUES LESS THAN (TO_DAYS("2018-10-01")),

               PARTITION y201810 VALUES LESS THAN (TO_DAYS("2018-11-01")),

               PARTITION y201811 VALUES LESS THAN (TO_DAYS("2018-12-01")),

               PARTITION y201812 VALUES LESS THAN (TO_DAYS("2019-01-01")),

               PARTITION other VALUES LESS THAN MAXVALUE

)

按月，然后按天分子区：

CREATE TABLE `t_test` (

 `t1` DATETIME DEFAULT NULL,

 `t2` VARCHAR(100) DEFAULT NULL,

 `t3` VARCHAR(100) DEFAULT NULL,

 `t4` VARCHAR(100) DEFAULT NULL

)

PARTITION BY RANGE (TO_DAYS(t1))

SUBPARTITION BY HASH(MONTH(t1)) SUBPARTITIONS 31

(

       PARTITION y2014 VALUES LESS THAN (TO_DAYS("2015-01-01")),

       PARTITION y201501 VALUES LESS THAN (TO_DAYS("2015-02-01")),

       PARTITION y201502 VALUES LESS THAN (TO_DAYS("2015-03-01")),

       PARTITION y201503 VALUES LESS THAN (TO_DAYS("2015-04-01")),

       PARTITION y201504 VALUES LESS THAN (TO_DAYS("2015-05-01")),

       PARTITION y201505 VALUES LESS THAN (TO_DAYS("2015-06-01")),

       PARTITION y201506 VALUES LESS THAN (TO_DAYS("2015-07-01")),

       PARTITION y201507 VALUES LESS THAN (TO_DAYS("2015-08-01")),

       PARTITION y201508 VALUES LESS THAN (TO_DAYS("2015-09-01")),

       PARTITION y201509 VALUES LESS THAN (TO_DAYS("2015-10-01")),

       PARTITION y201510 VALUES LESS THAN (TO_DAYS("2015-11-01")),

       PARTITION y201511 VALUES LESS THAN (TO_DAYS("2015-12-01")),

       PARTITION y201512 VALUES LESS THAN (TO_DAYS("2016-01-01")),

       PARTITION y201601 VALUES LESS THAN (TO_DAYS("2016-02-01")),

       PARTITION y201602 VALUES LESS THAN (TO_DAYS("2016-03-01")),

       PARTITION y201603 VALUES LESS THAN (TO_DAYS("2016-04-01")),

       PARTITION y201604 VALUES LESS THAN (TO_DAYS("2016-05-01")),

       PARTITION y201605 VALUES LESS THAN (TO_DAYS("2016-06-01")),

       PARTITION y201606 VALUES LESS THAN (TO_DAYS("2016-07-01")),

       PARTITION y201607 VALUES LESS THAN (TO_DAYS("2016-08-01")),

       PARTITION y201608 VALUES LESS THAN (TO_DAYS("2016-09-01")),

       PARTITION y201609 VALUES LESS THAN (TO_DAYS("2016-10-01")),

       PARTITION y201610 VALUES LESS THAN (TO_DAYS("2016-11-01")),

       PARTITION y201611 VALUES LESS THAN (TO_DAYS("2016-12-01")),

       PARTITION y201612 VALUES LESS THAN (TO_DAYS("2017-01-01")),

       PARTITION y201701 VALUES LESS THAN (TO_DAYS("2017-02-01")),

       PARTITION y201702 VALUES LESS THAN (TO_DAYS("2017-03-01")),

       PARTITION y201703 VALUES LESS THAN (TO_DAYS("2017-04-01")),

       PARTITION y201704 VALUES LESS THAN (TO_DAYS("2017-05-01")),

       PARTITION y201705 VALUES LESS THAN (TO_DAYS("2017-06-01")),

       PARTITION y201706 VALUES LESS THAN (TO_DAYS("2017-07-01")),

       PARTITION y201707 VALUES LESS THAN (TO_DAYS("2017-08-01")),

       PARTITION y201708 VALUES LESS THAN (TO_DAYS("2017-09-01")),

       PARTITION y201709 VALUES LESS THAN (TO_DAYS("2017-10-01")),

       PARTITION y201710 VALUES LESS THAN (TO_DAYS("2017-11-01")),

       PARTITION y201711 VALUES LESS THAN (TO_DAYS("2017-12-01")),

       PARTITION y201712 VALUES LESS THAN (TO_DAYS("2018-01-01")),

       PARTITION y201801 VALUES LESS THAN (TO_DAYS("2018-02-01")),

       PARTITION y201802 VALUES LESS THAN (TO_DAYS("2018-03-01")),

       PARTITION y201803 VALUES LESS THAN (TO_DAYS("2018-04-01")),

       PARTITION y201804 VALUES LESS THAN (TO_DAYS("2018-05-01")),

       PARTITION y201805 VALUES LESS THAN (TO_DAYS("2018-06-01")),

       PARTITION y201806 VALUES LESS THAN (TO_DAYS("2018-07-01")),

       PARTITION y201807 VALUES LESS THAN (TO_DAYS("2018-08-01")),

       PARTITION y201808 VALUES LESS THAN (TO_DAYS("2018-09-01")),

       PARTITION y201809 VALUES LESS THAN (TO_DAYS("2018-10-01")),

       PARTITION y201810 VALUES LESS THAN (TO_DAYS("2018-11-01")),

       PARTITION y201811 VALUES LESS THAN (TO_DAYS("2018-12-01")),

       PARTITION y201812 VALUES LESS THAN (TO_DAYS("2019-01-01")),

       PARTITION other VALUES LESS THAN MAXVALUE

)