MySQL表分區功能基礎 創建分區表 www.2cto.com 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 ); 為現有表創建分區 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 ); 如果表中已有數據,分區時會自動進行分區存儲,不必擔心數據丟失或者手動分類數據. 刪除表中的指定分區 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 …; 該操作只查看查詢語句相關的分區信息,不會返回查詢結果.
