表分区查看删除

-- 查看表分区

EXPLAIN PARTITIONS SELECT * FROM interface_log WHERE crt_date >= '2016-05-01 00:00:00' AND crt_date<='2016-10-01 00:00:00';

-- 删除分区：
ALTER TABLE interface_log DROP PARTITION p201613; 

-- 查看表分区
EXPLAIN PARTITIONS SELECT * FROM interface_log WHERE crt_date >= '2016-05-01 00:00:00' AND crt_date<='2016-10-01 00:00:00';


-- 对已有表创建表分区
ALTER TABLE interface_log 
PARTITION BY RANGE ( UNIX_TIMESTAMP(crt_date) ) (
    PARTITION p201611 VALUES LESS THAN ( UNIX_TIMESTAMP('2016-12-01 00:00:00') ),
    PARTITION p201612 VALUES LESS THAN ( UNIX_TIMESTAMP('2017-01-01 00:00:00') ),


    PARTITION p201701 VALUES LESS THAN (UNIX_TIMESTAMP('2017-02-01 00:00:00') ),
    PARTITION p201702 VALUES LESS THAN (UNIX_TIMESTAMP('2017-03-01 00:00:00') ),
    PARTITION p201703 VALUES LESS THAN (UNIX_TIMESTAMP('2017-04-01 00:00:00') ),
    PARTITION p201704 VALUES LESS THAN (UNIX_TIMESTAMP('2017-05-01 00:00:00') ),
    PARTITION p201705 VALUES LESS THAN (UNIX_TIMESTAMP('2017-06-01 00:00:00') ),
    PARTITION p201706 VALUES LESS THAN (UNIX_TIMESTAMP('2017-07-01 00:00:00') ),
    PARTITION p201707 VALUES LESS THAN (UNIX_TIMESTAMP('2017-08-01 00:00:00') ),
    PARTITION p201708 VALUES LESS THAN (UNIX_TIMESTAMP('2017-09-01 00:00:00') ),
    PARTITION p201709 VALUES LESS THAN (UNIX_TIMESTAMP('2017-10-01 00:00:00') ),
    PARTITION p201710 VALUES LESS THAN (UNIX_TIMESTAMP('2017-11-01 00:00:00') ),
    PARTITION p201711 VALUES LESS THAN (UNIX_TIMESTAMP('2017-12-01 00:00:00') ),
    PARTITION p201712 VALUES LESS THAN (UNIX_TIMESTAMP('2018-01-01 00:00:00') ),


    PARTITION p201801 VALUES LESS THAN (UNIX_TIMESTAMP('2018-02-01 00:00:00') ),
    PARTITION p201802 VALUES LESS THAN (UNIX_TIMESTAMP('2018-03-01 00:00:00') ),
    PARTITION p201803 VALUES LESS THAN (UNIX_TIMESTAMP('2018-04-01 00:00:00') ),
    PARTITION p201804 VALUES LESS THAN (UNIX_TIMESTAMP('2018-05-01 00:00:00') ),
    PARTITION p201805 VALUES LESS THAN (UNIX_TIMESTAMP('2018-06-01 00:00:00') ),
    PARTITION p201806 VALUES LESS THAN (UNIX_TIMESTAMP('2018-07-01 00:00:00') ),
    PARTITION p201807 VALUES LESS THAN (UNIX_TIMESTAMP('2018-08-01 00:00:00') ),
    PARTITION p201808 VALUES LESS THAN (UNIX_TIMESTAMP('2018-09-01 00:00:00') ),
    PARTITION p201809 VALUES LESS THAN (UNIX_TIMESTAMP('2018-10-01 00:00:00') ),
    PARTITION p201810 VALUES LESS THAN (UNIX_TIMESTAMP('2018-11-01 00:00:00') ),
    PARTITION p201811 VALUES LESS THAN (UNIX_TIMESTAMP('2018-12-01 00:00:00') ),
    PARTITION p201812 VALUES LESS THAN (UNIX_TIMESTAMP('2019-01-01 00:00:00') ),


    PARTITION p201901 VALUES LESS THAN (UNIX_TIMESTAMP('2019-02-01 00:00:00') ),
    PARTITION p201902 VALUES LESS THAN (UNIX_TIMESTAMP('2019-03-01 00:00:00') ),
    PARTITION p201903 VALUES LESS THAN (UNIX_TIMESTAMP('2019-04-01 00:00:00') ),
    PARTITION p201904 VALUES LESS THAN (UNIX_TIMESTAMP('2019-05-01 00:00:00') ),
    PARTITION p201905 VALUES LESS THAN (UNIX_TIMESTAMP('2019-06-01 00:00:00') ),
    PARTITION p201906 VALUES LESS THAN (UNIX_TIMESTAMP('2019-07-01 00:00:00') ),
    PARTITION p201907 VALUES LESS THAN (UNIX_TIMESTAMP('2019-08-01 00:00:00') )
);
-- 对已有表创建表分区
ALTER TABLE interface_log_qos 
PARTITION BY RANGE ( UNIX_TIMESTAMP(crt_date) ) (
    PARTITION p201611 VALUES LESS THAN ( UNIX_TIMESTAMP('2016-12-01 00:00:00') ),
    PARTITION p201612 VALUES LESS THAN ( UNIX_TIMESTAMP('2017-01-01 00:00:00') ),


    PARTITION p201701 VALUES LESS THAN (UNIX_TIMESTAMP('2017-02-01 00:00:00') ),
    PARTITION p201702 VALUES LESS THAN (UNIX_TIMESTAMP('2017-03-01 00:00:00') ),
    PARTITION p201703 VALUES LESS THAN (UNIX_TIMESTAMP('2017-04-01 00:00:00') ),
    PARTITION p201704 VALUES LESS THAN (UNIX_TIMESTAMP('2017-05-01 00:00:00') ),
    PARTITION p201705 VALUES LESS THAN (UNIX_TIMESTAMP('2017-06-01 00:00:00') ),
    PARTITION p201706 VALUES LESS THAN (UNIX_TIMESTAMP('2017-07-01 00:00:00') ),
    PARTITION p201707 VALUES LESS THAN (UNIX_TIMESTAMP('2017-08-01 00:00:00') ),
    PARTITION p201708 VALUES LESS THAN (UNIX_TIMESTAMP('2017-09-01 00:00:00') ),
    PARTITION p201709 VALUES LESS THAN (UNIX_TIMESTAMP('2017-10-01 00:00:00') ),
    PARTITION p201710 VALUES LESS THAN (UNIX_TIMESTAMP('2017-11-01 00:00:00') ),
    PARTITION p201711 VALUES LESS THAN (UNIX_TIMESTAMP('2017-12-01 00:00:00') ),
    PARTITION p201712 VALUES LESS THAN (UNIX_TIMESTAMP('2018-01-01 00:00:00') ),


    PARTITION p201801 VALUES LESS THAN (UNIX_TIMESTAMP('2018-02-01 00:00:00') ),
    PARTITION p201802 VALUES LESS THAN (UNIX_TIMESTAMP('2018-03-01 00:00:00') ),
    PARTITION p201803 VALUES LESS THAN (UNIX_TIMESTAMP('2018-04-01 00:00:00') ),
    PARTITION p201804 VALUES LESS THAN (UNIX_TIMESTAMP('2018-05-01 00:00:00') ),
    PARTITION p201805 VALUES LESS THAN (UNIX_TIMESTAMP('2018-06-01 00:00:00') ),
    PARTITION p201806 VALUES LESS THAN (UNIX_TIMESTAMP('2018-07-01 00:00:00') ),
    PARTITION p201807 VALUES LESS THAN (UNIX_TIMESTAMP('2018-08-01 00:00:00') ),
    PARTITION p201808 VALUES LESS THAN (UNIX_TIMESTAMP('2018-09-01 00:00:00') ),
    PARTITION p201809 VALUES LESS THAN (UNIX_TIMESTAMP('2018-10-01 00:00:00') ),
    PARTITION p201810 VALUES LESS THAN (UNIX_TIMESTAMP('2018-11-01 00:00:00') ),
    PARTITION p201811 VALUES LESS THAN (UNIX_TIMESTAMP('2018-12-01 00:00:00') ),
    PARTITION p201812 VALUES LESS THAN (UNIX_TIMESTAMP('2019-01-01 00:00:00') ),


    PARTITION p201901 VALUES LESS THAN (UNIX_TIMESTAMP('2019-02-01 00:00:00') ),
    PARTITION p201902 VALUES LESS THAN (UNIX_TIMESTAMP('2019-03-01 00:00:00') ),
    PARTITION p201903 VALUES LESS THAN (UNIX_TIMESTAMP('2019-04-01 00:00:00') ),
    PARTITION p201904 VALUES LESS THAN (UNIX_TIMESTAMP('2019-05-01 00:00:00') ),
    PARTITION p201905 VALUES LESS THAN (UNIX_TIMESTAMP('2019-06-01 00:00:00') ),
    PARTITION p201906 VALUES LESS THAN (UNIX_TIMESTAMP('2019-07-01 00:00:00') ),
    PARTITION p201907 VALUES LESS THAN (UNIX_TIMESTAMP('2019-08-01 00:00:00') )
);