Mysql分区的技能
1、 查看分区信息
(1)explain partitions select * from TDM_YTMF_BRAND_CATE_GDS_STC_D
语法:explain partitions select 语句
(2)
SELECT partition_name part, partition_expression expr, partition_description descr, table_rows FROM INFORMATION_SCHEMA.partitions WHERE TABLE_SCHEMA = schema() AND TABLE_NAME='TDM_YTMF_BRAND_CATE_GDS_STC_D';
语法:
SELECT partition_name part, partition_expression expr, partition_description descr, table_rows FROM INFORMATION_SCHEMA.partitions WHERE TABLE_SCHEMA = schema() AND TABLE_NAME=表名;
2.创建表的时候创建分区
CREATE TABLE `vehicle_coord_range` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID', `region_code` varchar(64) DEFAULT NULL COMMENT '城市(区域)CODE', `plate_num` varchar(64) DEFAULT NULL COMMENT '车牌号', `lon` varchar(64) DEFAULT NULL COMMENT '经度', `lat` varchar(64) DEFAULT NULL COMMENT '纬度', `remark` varchar(255) DEFAULT NULL COMMENT '备注', `createTime` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', `create_by` bigint(20) DEFAULT NULL COMMENT '创建者', `create_date` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', `update_by` bigint(20) DEFAULT NULL COMMENT '更新者', `update_date` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间', `remarks` varchar(255) DEFAULT NULL COMMENT '备注信息', `del_flag` varchar(64) DEFAULT '0' COMMENT '逻辑删除标记(0:显示;1:隐藏)', `direction` varchar(64) DEFAULT NULL COMMENT '行驶方向', PRIMARY KEY (`id`,`create_date`) ) ENGINE=InnoDB AUTO_INCREMENT=172203 DEFAULT CHARSET=utf8 COMMENT='订单(行程) 线路实时坐标' partition by range (TO_DAYS(create_date))( #range分区结构 PARTITION vehicle_coord_1 VALUES LESS THAN (TO_DAYS('2017-05-22')), PARTITION vehicle_coord_2 VALUES LESS THAN (TO_DAYS('2017-05-23')) );
3.新增分区
ALTER TABLE TDM_YTMF_BRAND_CATE_GDS_STC_D ADD PARTITION( PARTITION P_20170404 VALUES IN (20170404) );
语法:
ALTER TABLE 表名 ADD PARTITION( PARTITION 分区名称VALUES IN (值 );