Mysql 分区表

DROP TABLE IF EXISTS `frank_test`;
CREATE TABLE `frank_test` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键id',
`gid` bigint(20) DEFAULT '0' COMMENT '基础表id',
`frankly_code` varchar(200) DEFAULT '' COMMENT '提单编号',
`total_amount` bigint(20) DEFAULT '0' COMMENT '产品的总数量',
`total_weight` double(15,3) DEFAULT '0.000' COMMENT '总重量',
`weight_unit` varchar(20) DEFAULT 'T' COMMENT '重量单位',
`total_volume` decimal(20,3) DEFAULT '0.000' COMMENT '总体积',
`volume_unit` varchar(20) DEFAULT 'teu' COMMENT '体积单位',
`businesses_id` bigint(20) DEFAULT '0' COMMENT '采购商id',
`businesses_name` varchar(255) DEFAULT '' COMMENT '采购商名称',
`businesses_did` varchar(50) DEFAULT '0' COMMENT '采购商地址id',
`businesses_address` varchar(500) DEFAULT '' COMMENT '采购商详情地址',
`suppliers_id` bigint(20) DEFAULT '0' COMMENT '供应商id',
`suppliers_name` varchar(200) DEFAULT '' COMMENT '供应商名称',
`suppliers_did` varchar(50) DEFAULT '0' COMMENT '供应商地址id',
`suppliers_address` varchar(500) DEFAULT '' COMMENT '供应商详情地址',
`product_description` longtext COMMENT '产品描述',
`frankly_time` datetime DEFAULT NULL COMMENT '提单日期',
`contact_id` bigint(20) DEFAULT '0' COMMENT '通知人id',
`contact_name` varchar(50) DEFAULT '' COMMENT '通知人姓名',
`create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`update_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '修改时间',
`is_deleted` int(11) DEFAULT '0' COMMENT '是否删除0=正常，1=删除',
`qiyun_prot_id` bigint(11) DEFAULT '0' COMMENT '起运港id',
`qiyun_prot_name` varchar(200) DEFAULT NULL COMMENT '起运港名称',
`mudi_prot_id` bigint(11) DEFAULT '0' COMMENT '目的港id',
`mudi_prot_name` varchar(200) DEFAULT '' COMMENT '目的港名称',
`mudi_country` varchar(20) DEFAULT '' COMMENT '目的国',
`mudi_district_did` bigint(11) DEFAULT '0' COMMENT '目的国id',
`fact_weight` double(15,3) DEFAULT '0.000' COMMENT '实际重量（折算之后）',
`originer_country` varchar(200) DEFAULT '' COMMENT '原产国',
`originer_country_id` bigint(50) DEFAULT '0' COMMENT '原产国id',
`vessel_name` varchar(200) DEFAULT '' COMMENT '承运商名称',
`vessel_id` bigint(11) DEFAULT '0' COMMENT '承运商id',
`hs_code` varchar(10) DEFAULT '',
PRIMARY KEY (`id`,frankly_time),
KEY `qiyun_prot_id` (`qiyun_prot_id`) USING BTREE,
KEY `businesses_id` (`businesses_id`) USING BTREE,
KEY `suppliers_id` (`suppliers_id`) USING BTREE,
KEY `key_total_volume` (`total_volume`) USING BTREE,
KEY `key_fact_weight` (`fact_weight`) USING BTREE,
KEY `key_time` (`frankly_time`) USING BTREE,
KEY `key_hscode` (`hs_code`) USING BTREE,
KEY `key_suppliers_did` (`suppliers_did`) USING BTREE,
KEY `key_businesses_did` (`businesses_did`) USING BTREE,
KEY `key_busi` (`hs_code`,`frankly_time`,`businesses_did`,`businesses_name`) USING BTREE,
KEY `key_supp` (`hs_code`,`frankly_time`,`suppliers_did`,`suppliers_name`) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='提单信息表'
PARTITION BY RANGE (YEAR(frankly_time))
(
PARTITION frank08 VALUES LESS THAN (2009) ENGINE = MyISAM,
PARTITION frank10 VALUES LESS THAN (2011) ENGINE = MyISAM,
PARTITION frank11 VALUES LESS THAN (2012) ENGINE = MyISAM,
PARTITION frank12 VALUES LESS THAN (2013) ENGINE = MyISAM,
PARTITION frank13 VALUES LESS THAN (2014) ENGINE = MyISAM,
PARTITION frank14 VALUES LESS THAN (2015) ENGINE = MyISAM,
PARTITION frank15 VALUES LESS THAN (2016) ENGINE = MyISAM,
PARTITION frank16 VALUES LESS THAN (2017) ENGINE = MyISAM,
PARTITION frank17 VALUES LESS THAN MAXVALUE ENGINE = MyISAM);