(1)MySQL按日期分区。
CREATE TABLE `mf_fundreturnranktrans` (
`ID` bigint(20) NOT NULL COMMENT 'ID',
`InnerCode` int(11) NOT NULL COMMENT '基金内部编码',
`EndDate` datetime NOT NULL COMMENT '截止日期',
`TypeCode` int(11) NOT NULL COMMENT '基金分类代码',
`TypeName` varchar(100) NOT NULL COMMENT '基金分类描述',
`IndexCycle` int(11) NOT NULL COMMENT '指标周期',
`FundReturn` decimal(18,9) DEFAULT NULL COMMENT '基金收益率(%)',
`FundReturnMean` decimal(18,9) DEFAULT NULL COMMENT '同类基金收益率均值(%)',
`UpdateTime` datetime NOT NULL COMMENT '更新日期',
`JSID` bigint(20) NOT NULL COMMENT 'JSID',
primary KEY `IX_MF_FundReturnRankTrans` (`InnerCode`,`EndDate`,`TypeName`,`IndexCycle`),
UNIQUE KEY `IX_MF_FundReturnRankTrans_ID` (`ID`,`EndDate`),
UNIQUE KEY `IX_MF_FundReturnRankTrans_JSID` (`JSID`,`EndDate`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
partition by range columns (`EndDate`) --写法这里略有差异
(
partition p_20171201 values less than('2020-01-01'),
partition p_20180101 values less than('2020-02-01'),
partition p_20180201 values less than('2020-03-01')
);
(2)按数字分区。
CREATE TABLE tiiia_bta_1.t_tiiia_0_bak2 (
`trade_date` int(8) NOT NULL,
`client_id` varchar(20) DEFAULT NULL,
`fund_account` varchar(18) NOT NULL,
`money_type` varchar(3) NOT NULL,
`day_yields` decimal(19,3) DEFAULT NULL,
`day_yield_rate` decimal(9,4) DEFAULT NULL,
`updatetime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`fund_account`,`money_type`,`trade_date`),
KEY `inx_t_tiiia_stkacct_dy_0_trade_date` (`trade_date`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=gbk ROW_FORMAT=DYNAMIC
partition by range(trade_date)
(
partition p_20171201 values less than(20171201),
partition p_20180101 values less than(20180101),
partition p_20180201 values less than(20180201)
);
(3)添加分区
alter table tiiia_bta_1.`t_tiiia_0_bak2` add partition (partition p_20180301 values less than(20180301));
总结:
对于分区表,所有的主键和唯一键都必须包含分区字段,否则无法创建成功。