网站地图    收藏   

主页 > 后端 > mysql数据库 >

Mysql实现分区功能(一) - mysql数据库栏目 - 自学

来源:自学PHP网    时间:2015-04-16 10:51 作者: 阅读:

[导读] 1 创建分区表CREATE TABLE `表名` ( `EQUIPMENTID` char(17) NOT NULL, `ATTRIBUTEID` char(4) NOT NULL, `VALUE` varchar(20) NOT NULL,...

1. 创建分区表

CREATE TABLE `表名` (   
    `EQUIPMENTID` char(17) NOT NULL,   
    `ATTRIBUTEID` char(4) NOT NULL,   
    `VALUE` varchar(20) NOT NULL,   
    `COLLECTTIME` datetime NOT NULL  
)    
ENGINE=InnoDB (适用大部分引擎,可根据需要调整)   
DEFAULT CHARSET=gbk(编码可根据需要修改)    
PARTITION BY RANGE (to_days(COLLECTTIME))    
(PARTITION pmin VALUES LESS THAN (to_days('2010-01-01')),   
PARTITION p201001 VALUES LESS THAN (to_days('2010-02-01')) ,    
PARTITION p201002 VALUES LESS THAN (to_days('2010-03-01')) ,   
PARTITION p201003 VALUES LESS THAN (to_days('2010-04-01')) ,    
PARTITION p201004 VALUES LESS THAN (to_days('2010-05-01')) ,   
PARTITION p201005 VALUES LESS THAN (to_days('2010-06-01')) ,    
PARTITION p201006 VALUES LESS THAN (to_days('2010-07-01')) ,   
PARTITION p201007 VALUES LESS THAN (to_days('2010-08-01')) ,    
PARTITION p201008 VALUES LESS THAN (to_days('2010-09-01')) ,   
PARTITION p201009 VALUES LESS THAN (to_days('2010-10-01')) ,    
PARTITION p201010 VALUES LESS THAN (to_days('2010-11-01')),   
PARTITION p201011 VALUES LESS THAN (to_days('2010-12-01')),   
PARTITION p201012 VALUES LESS THAN (to_days('2011-01-01')),   
PARTITION p201101 VALUES LESS THAN (to_days('2011-02-01')),   
PARTITION p201102 VALUES LESS THAN (to_days('2011-03-01')),   
PARTITION p201103 VALUES LESS THAN (to_days('2011-04-01')),   
PARTITION p201104 VALUES LESS THAN (to_days('2011-05-01')),   
PARTITION p201105 VALUES LESS THAN (to_days('2011-06-01')),   
PARTITION p201106 VALUES LESS THAN (to_days('2011-07-01')),   
PARTITION p201107 VALUES LESS THAN (to_days('2011-08-01')),   
PARTITION p201108 VALUES LESS THAN (to_days('2011-09-01')),   
PARTITION p201109 VALUES LESS THAN (to_days('2011-10-01')),   
PARTITION p201110 VALUES LESS THAN (to_days('2011-11-01')),   
PARTITION p201111 VALUES LESS THAN (to_days('2011-12-01')),   
PARTITION p201112 VALUES LESS THAN (to_days('2012-01-01')),   
PARTITION pmax VALUES LESS THAN MAXVALUE );   
2. 为现有表创建分区
alter table 表名   
PARTITION BY RANGE (to_days(COLLECTTIME))    
(PARTITION pmin VALUES LESS THAN (to_days('2010-01-01')),   
PARTITION p201001 VALUES LESS THAN (to_days('2010-02-01')) ,    
PARTITION p201002 VALUES LESS THAN (to_days('2010-03-01')) ,   
PARTITION p201003 VALUES LESS THAN (to_days('2010-04-01')) ,    
PARTITION p201004 VALUES LESS THAN (to_days('2010-05-01')) ,   
PARTITION p201005 VALUES LESS THAN (to_days('2010-06-01')) ,    
PARTITION p201006 VALUES LESS THAN (to_days('2010-07-01')) ,   
PARTITION p201007 VALUES LESS THAN (to_days('2010-08-01')) ,    
PARTITION p201008 VALUES LESS THAN (to_days('2010-09-01')) ,   
PARTITION p201009 VALUES LESS THAN (to_days('2010-10-01')) ,    
PARTITION p201010 VALUES LESS THAN (to_days('2010-11-01')),   
PARTITION p201011 VALUES LESS THAN (to_days('2010-12-01')),   
PARTITION p201012 VALUES LESS THAN (to_days('2011-01-01')),   
PARTITION p201101 VALUES LESS THAN (to_days('2011-02-01')),   
PARTITION p201102 VALUES LESS THAN (to_days('2011-03-01')),   
PARTITION p201103 VALUES LESS THAN (to_days('2011-04-01')),   
PARTITION p201104 VALUES LESS THAN (to_days('2011-05-01')),   
PARTITION p201105 VALUES LESS THAN (to_days('2011-06-01')),   
PARTITION p201106 VALUES LESS THAN (to_days('2011-07-01')),   
PARTITION p201107 VALUES LESS THAN (to_days('2011-08-01')),   
PARTITION p201108 VALUES LESS THAN (to_days('2011-09-01')),   
PARTITION p201109 VALUES LESS THAN (to_days('2011-10-01')),   
PARTITION p201110 VALUES LESS THAN (to_days('2011-11-01')),   
PARTITION p201111 VALUES LESS THAN (to_days('2011-12-01')),   
PARTITION p201112 VALUES LESS THAN (to_days('2012-01-01')),   
PARTITION pmax VALUES LESS THAN MAXVALUE );   
如果表中已有数据,分区时会自动进行分区存储,不必担心数据丢失或者手动分类数据. 

3. 删除表中的指定分区

ALTER TABLE 表名 DROP PARTITION 分区名;
4. 追加表分区
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);   
5. 查看表分区信息
SELECT    
        partition_name part,     
        partition_expression expr,     
        partition_description descr,     
        table_rows     
FROM    
        INFORMATION_SCHEMA.partitions     
WHERE    
				TABLE_NAME='表名'; 
6. 查看查询语句涉及分区信息
explain partitions   
select … from 表名 where …;

自学PHP网专注网站建设学习,PHP程序学习,平面设计学习,以及操作系统学习

京ICP备14009008号-1@版权所有www.zixuephp.com

网站声明:本站所有视频,教程都由网友上传,站长收集和分享给大家学习使用,如由牵扯版权问题请联系站长邮箱904561283@qq.com

添加评论