mysql表添加分区

mysql表添加分区

CREATE TABLE `t_dealer_goods_sales_bk` (  `ORDER_ID` VARCHAR(32) DEFAULT NULL COMMENT '订单id',  `ORDER_DATE` DATE DEFAULT NULL COMMENT '订单创建日期',  `COMPLETION_DATE` DATE DEFAULT NULL COMMENT '订单完成日期',  `ORDER_TYPE` TINYINT(4) DEFAULT NULL COMMENT '订单类型（业务类型）：1，及时达业务2，店内业务（地推活动）；3，线上自提业务；4，（次日达）； 5，厂商直供业务（大闸蟹）；6，虚拟订单（售券、秒券）；7，2c业务；8，POS机业务',  `OPEN_APP_ID` TINYINT(4) DEFAULT NULL COMMENT '0，..  1，360',  `DELIVER_MODE` TINYINT(3) DEFAULT NULL COMMENT '1，门店配送；  ',  `CITY_ID` MEDIUMINT(9) DEFAULT NULL COMMENT '城市id',  `CUST_ID` INT(11) DEFAULT NULL COMMENT '用户id',  `DEALER_ID` INT(11) DEFAULT NULL COMMENT '门店id',  `SELL_PF` TINYINT(4) DEFAULT NULL COMMENT '销售平台',  `PAY_MD` TINYINT(4) DEFAULT NULL COMMENT '支付方式',  `GOODS_ID` INT(11) DEFAULT NULL COMMENT '商品id',  `SOURCE_ID` INT(4) DEFAULT NULL COMMENT '商品来源id',  `GOODS_SALES_AMT` DECIMAL(10,2) DEFAULT NULL COMMENT '商品销售额',  `SELL_NUM` DECIMAL(10,2) DEFAULT NULL COMMENT '销售数量',  `GIVE_NUM` DECIMAL(10,2) DEFAULT NULL COMMENT '赠送销量',  `RETURN_AMT` DECIMAL(10,4) DEFAULT NULL COMMENT '返款额',  `PRICE_DIFF_SUBSIDY` DECIMAL(10,4) DEFAULT NULL COMMENT '商品差价补贴',  `DEALER_PROFITS_AMT` DECIMAL(10,2) DEFAULT NULL COMMENT '门店毛利',  `AXF_PROFITS_AMT` DECIMAL(10,2) DEFAULT NULL COMMENT '爱鲜蜂毛利',  `USE_COUPON_AMT` DECIMAL(10,4) DEFAULT NULL COMMENT '优惠券使用金额',  KEY `idx_dealer_goods_sales_order_date` (`ORDER_DATE`),  KEY `idx_dealer_goods_sales_comdate` (`COMPLETION_DATE`)) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT='门店商品销售明细表'PARTITION BY RANGE  COLUMNS(ORDER_DATE)(PARTITION p20151217 VALUES LESS THAN ('2015-12-18') ENGINE = INNODB, PARTITION p20151218 VALUES LESS THAN ('2015-12-19') ENGINE = INNODB, PARTITION p20151219 VALUES LESS THAN ('2015-12-20') ENGINE = INNODB, PARTITION p20151220 VALUES LESS THAN ('2015-12-21') ENGINE = INNODB, PARTITION p20151221 VALUES LESS THAN ('2015-12-22') ENGINE = INNODB, PARTITION p20151222 VALUES LESS THAN ('2015-12-23') ENGINE = INNODB, PARTITION p20151223 VALUES LESS THAN ('2015-12-24') ENGINE = INNODB, PARTITION p20151224 VALUES LESS THAN ('2015-12-25') ENGINE = INNODB, PARTITION p20151225 VALUES LESS THAN ('2015-12-26') ENGINE = INNODB, PARTITION p20151226 VALUES LESS THAN ('2015-12-27') ENGINE = INNODB, PARTITION p20151227 VALUES LESS THAN ('2015-12-28') ENGINE = INNODB, PARTITION p20151228 VALUES LESS THAN ('2015-12-29') ENGINE = INNODB, PARTITION p20151229 VALUES LESS THAN ('2015-12-30') ENGINE = INNODB, PARTITION p20151230 VALUES LESS THAN ('2015-12-31') ENGINE = INNODB, PARTITION p20151231 VALUES LESS THAN ('2016-01-01') ENGINE = INNODB, PARTITION p20160101 VALUES LESS THAN ('2016-01-02') ENGINE = INNODB, PARTITION p20160102 VALUES LESS THAN ('2016-01-03') ENGINE = INNODB, PARTITION p20160103 VALUES LESS THAN ('2016-01-04') ENGINE = INNODB, PARTITION p20160104 VALUES LESS THAN ('2016-01-05') ENGINE = INNODB, PARTITION p20160105 VALUES LESS THAN ('2016-01-06') ENGINE = INNODB, PARTITION p20160106 VALUES LESS THAN ('2016-01-07') ENGINE = INNODB, PARTITION p20160107 VALUES LESS THAN ('2016-01-08') ENGINE = INNODB, PARTITION p20160108 VALUES LESS THAN ('2016-01-09') ENGINE = INNODB, PARTITION p20160109 VALUES LESS THAN ('2016-01-10') ENGINE = INNODB, PARTITION p20160110 VALUES LESS THAN ('2016-01-11') ENGINE = INNODB, PARTITION p20160111 VALUES LESS THAN ('2016-01-12') ENGINE = INNODB, PARTITION p20160112 VALUES LESS THAN ('2016-01-13') ENGINE = INNODB, PARTITION p20160113 VALUES LESS THAN ('2016-01-14') ENGINE = INNODB, PARTITION p20160114 VALUES LESS THAN ('2016-01-15') ENGINE = INNODB, PARTITION p20160115 VALUES LESS THAN ('2016-01-16') ENGINE = INNODB, PARTITION pmax VALUES LESS THAN (MAXVALUE) ENGINE = INNODB) ;<pre code_snippet_id="1699068" snippet_file_name="blog_20160527_2_5963562" name="code" class="sql">自动分区函数 第一个参数表名 第二个参数分区开始日期 第三个分区结束日期CALL dw.sp_tool_maintain_partition('t_dealer_goods_sales_bk','2015-12-18',DATE_ADD(CURDATE(),INTERVAL 600 DAY));INSERT INTO t_dealer_goods_sales_bk SELECT * FROM t_dealer_goods_sales;DROP TABLE t_dealer_goods_sales;RENAME TABLE t_dealer_goods_sales_bk TO t_dealer_goods_sales;