表分区举例

table_name.jgsj  字段 为 datetime  字段


添加表分区

ALTER TABLE `table_name`  PARTITION BY RANGE(TO_SECONDS(jgsj))  
( 
 PARTITION part_14    VALUES LESS THAN (TO_SECONDS('2014-12-31 23:59:59')),  
 PARTITION part_15_1  VALUES LESS THAN (TO_SECONDS('2015-01-31 23:59:59')),  
 PARTITION part_15_2  VALUES LESS THAN (TO_SECONDS('2015-02-28 23:59:59')),  
 PARTITION part_15_3  VALUES LESS THAN (TO_SECONDS('2015-03-31 23:59:59')),  
 PARTITION part_15_4  VALUES LESS THAN (TO_SECONDS('2015-04-30 23:59:59')),  
 PARTITION part_15_5  VALUES LESS THAN (TO_SECONDS('2015-05-31 23:59:59')),  
 PARTITION part_15_6  VALUES LESS THAN (TO_SECONDS('2015-06-30 23:59:59')),  
 PARTITION part_15_7  VALUES LESS THAN (TO_SECONDS('2015-07-31 23:59:59')),  
 PARTITION part_15_8  VALUES LESS THAN (TO_SECONDS('2015-08-31 23:59:59')),  
 PARTITION part_15_9  VALUES LESS THAN (TO_SECONDS('2015-09-30 23:59:59')),  
 PARTITION part_15_10  VALUES LESS THAN (TO_SECONDS('2015-10-31 23:59:59')),  
 PARTITION part_15_11  VALUES LESS THAN (TO_SECONDS('2015-11-30 23:59:59')),  
 PARTITION part_15_12  VALUES LESS THAN (TO_SECONDS('2015-12-31 23:59:59')),
 PARTITION part_16_1  VALUES LESS THAN (TO_SECONDS('2016-01-31 23:59:59')),  
 PARTITION part_16_2  VALUES LESS THAN (TO_SECONDS('2016-02-28 23:59:59')),  
 PARTITION part_16_3  VALUES LESS THAN (TO_SECONDS('2016-03-31 23:59:59')),  
 PARTITION part_16_4  VALUES LESS THAN (TO_SECONDS('2016-04-30 23:59:59')),  
 PARTITION part_16_5  VALUES LESS THAN (TO_SECONDS('2016-05-31 23:59:59')),  
 PARTITION part_16_6  VALUES LESS THAN (TO_SECONDS('2016-06-30 23:59:59')),  
 PARTITION part_16_7  VALUES LESS THAN (TO_SECONDS('2016-07-31 23:59:59')),  
 PARTITION part_16_8  VALUES LESS THAN (TO_SECONDS('2016-08-31 23:59:59')),  
 PARTITION part_16_9  VALUES LESS THAN (TO_SECONDS('2016-09-30 23:59:59')),  
 PARTITION part_16_10  VALUES LESS THAN (TO_SECONDS('2016-10-31 23:59:59')),  
 PARTITION part_16_11  VALUES LESS THAN (TO_SECONDS('2016-11-30 23:59:59')),  
 PARTITION part_16_12  VALUES LESS THAN (TO_SECONDS('2016-12-31 23:59:59')),
 PARTITION part_17_1  VALUES LESS THAN (TO_SECONDS('2017-01-31 23:59:59')),  
 PARTITION part_17_2  VALUES LESS THAN (TO_SECONDS('2017-02-28 23:59:59')),  
 PARTITION part_17_3  VALUES LESS THAN (TO_SECONDS('2017-03-31 23:59:59')),  
 PARTITION part_17_4  VALUES LESS THAN (TO_SECONDS('2017-04-30 23:59:59')),  
 PARTITION part_17_5  VALUES LESS THAN (TO_SECONDS('2017-05-31 23:59:59')),  
 PARTITION part_17_6  VALUES LESS THAN (TO_SECONDS('2017-06-30 23:59:59')),  
 PARTITION part_17_7  VALUES LESS THAN (TO_SECONDS('2017-07-31 23:59:59')),  
 PARTITION part_17_8  VALUES LESS THAN (TO_SECONDS('2017-08-31 23:59:59')),  
 PARTITION part_17_9  VALUES LESS THAN (TO_SECONDS('2017-09-30 23:59:59')),  
 PARTITION part_17_10  VALUES LESS THAN (TO_SECONDS('2017-10-31 23:59:59')),  
 PARTITION part_17_11  VALUES LESS THAN (TO_SECONDS('2017-11-30 23:59:59')),  
 PARTITION part_17_12  VALUES LESS THAN (TO_SECONDS('2017-12-31 23:59:59')),
 PARTITION part_18_1  VALUES LESS THAN (TO_SECONDS('2018-01-31 23:59:59')),  
 PARTITION part_18_2  VALUES LESS THAN (TO_SECONDS('2018-02-28 23:59:59')),  
 PARTITION part_18_3  VALUES LESS THAN (TO_SECONDS('2018-03-31 23:59:59')),  
 PARTITION part_18_4  VALUES LESS THAN (TO_SECONDS('2018-04-30 23:59:59')),  
 PARTITION part_18_5  VALUES LESS THAN (TO_SECONDS('2018-05-31 23:59:59')),  
 PARTITION part_18_6  VALUES LESS THAN (TO_SECONDS('2018-06-30 23:59:59')),  
 PARTITION part_18_7  VALUES LESS THAN (TO_SECONDS('2018-07-31 23:59:59')),  
 PARTITION part_18_8  VALUES LESS THAN (TO_SECONDS('2018-08-31 23:59:59')),  
 PARTITION part_18_9  VALUES LESS THAN (TO_SECONDS('2018-09-30 23:59:59')),  
 PARTITION part_18_10  VALUES LESS THAN (TO_SECONDS('2018-10-31 23:59:59')),  
 PARTITION part_18_11  VALUES LESS THAN (TO_SECONDS('2018-11-30 23:59:59')),  
 PARTITION part_18_12  VALUES LESS THAN (TO_SECONDS('2018-12-31 23:59:59')),
 PARTITION part_other VALUES LESS THAN (MAXVALUE)
);



如果表中已有数据,分区时会自动进行分区存储,不必担心数据丢失或者手动分类数据.

删除表中的指定分区

ALTER TABLE 表名 DROP PARTITION 分区名;

追加表分区

ALTER TABLE 表名 DROP PARTITION pmax;
ALTER TABLE 表名
ADD PARTITION (
PARTITION p201201 VALUES LESS THAN (to_days('2012-2-1')),
PARTITION pmax VALUES LESS THAN MAXVALUE);

查看标分区信息

SELECT
        partition_name part,
        partition_expression expr,
        partition_description descr,
        table_rows
FROM
        INFORMATION_SCHEMA.partitions
WHERE
        TABLE_SCHEMA = schema()
        AND TABLE_NAME='表名';

查看查询语句涉及分区信息

explain partitions
select … from 表名 where …;