Chinaunix首页 | 论坛 | 博客
  • 博客访问: 2147228
  • 博文数量: 283
  • 博客积分: 0
  • 博客等级: 民兵
  • 技术积分: 2100
  • 用 户 组: 普通用户
  • 注册时间: 2014-03-31 14:30
个人简介

自己慢慢积累。

文章分类

全部博文(283)

分类: Mysql/postgreSQL

2015-04-27 10:43:13

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)
);



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

删除表中的指定分区

  1. ALTER TABLE 表名 DROP PARTITION 分区名;   


追加表分区
  1. ALTER TABLE 表名 DROP PARTITION pmax;   
  2. ALTER TABLE 表名   
  3. ADD PARTITION (   
  4. PARTITION p201201 VALUES LESS THAN (to_days('2012-2-1')),   
  5. PARTITION pmax VALUES LESS THAN MAXVALUE);   

查看标分区信息
  1. SELECT    
  2.         partition_name part,     
  3.         partition_expression expr,     
  4.         partition_description descr,     
  5.         table_rows     
  6. FROM    
  7.         INFORMATION_SCHEMA.partitions     
  8. WHERE    
  9.         TABLE_SCHEMA = schema()    
  10.         AND TABLE_NAME='表名';   

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

  1. explain partitions   
  2. select … from 表名 where …;   

阅读(1149) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~