创建表分区语句:


CREATE TABLE `pub_point_test` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`longitude` double(255,10) DEFAULT NULL,

`latitude` double(255,10) DEFAULT NULL,

`license` varchar(255) DEFAULT NULL,

`gatherTime` bigint(10) NOT NULL,

`clientId` varchar(255) DEFAULT NULL,

`speed` int(11) DEFAULT NULL,

`address` varchar(255) DEFAULT NULL,

`datetime` datetime DEFAULT NULL,

`vehicleId` int(10) DEFAULT NULL,

`mileage` double(255,3) DEFAULT '0.000' COMMENT '单位:百米',

`trackId` varchar(255) DEFAULT NULL,

PRIMARY KEY (`id`,`gatherTime`),

KEY `license_index` (`license`) USING BTREE,

KEY `gatherTime_index` (`gatherTime`) USING BTREE

) ENGINE=InnoDB AUTO_INCREMENT=2594032 DEFAULT CHARSET=gbk


partition by range(gatherTime)

subpartition by hash(id)

subpartitions 20

(

partition p20170101 values less than ((TO_DAYS('2016-01-01'))),

partition p20170102 values less than ((TO_DAYS('2016-01-02')))


)



添加子分区语句

alter table pub_point_test add partition

(

partition p20170103 values less than ((TO_DAYS('2016-01-03'))),

partition p20170104 values less than ((TO_DAYS('2016-01-04')))

)