Mysql实现分区功能(一)_MySQL
程序员文章站
2022-06-11 15:42:35
...
bitsCN.com
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 …;bitsCN.com
上一篇: 请教一个简单的url重写问题
下一篇: A-Fast-Rcnn代码运行过程记录