跨年 增加分区分区

 

/*!50100 PARTITION BY RANGE (TO_DAYS ( create_date ))
(PARTITION p201705 VALUES LESS THAN (736846) ENGINE = InnoDB,
PARTITION p201706 VALUES LESS THAN (736876) ENGINE = InnoDB,
PARTITION p201707 VALUES LESS THAN (736907) ENGINE = InnoDB,
PARTITION p201708 VALUES LESS THAN (736938) ENGINE = InnoDB,
PARTITION p201709 VALUES LESS THAN (736968) ENGINE = InnoDB,
PARTITION p201710 VALUES LESS THAN (736999) ENGINE = InnoDB,
PARTITION p201711 VALUES LESS THAN (737029) ENGINE = InnoDB,
PARTITION p201712 VALUES LESS THAN (737060) ENGINE = InnoDB,
PARTITION p201801 VALUES LESS THAN (737091) ENGINE = InnoDB,
PARTITION p201802 VALUES LESS THAN (737119) ENGINE = InnoDB,
PARTITION p201803 VALUES LESS THAN (737150) ENGINE = InnoDB,
PARTITION p201804 VALUES LESS THAN (737180) ENGINE = InnoDB,
PARTITION p201805 VALUES LESS THAN (737211) ENGINE = InnoDB,
PARTITION p201806 VALUES LESS THAN (737241) ENGINE = InnoDB,
PARTITION p201807 VALUES LESS THAN (737272) ENGINE = InnoDB,
PARTITION p201808 VALUES LESS THAN (737303) ENGINE = InnoDB,
PARTITION p201809 VALUES LESS THAN (737333) ENGINE = InnoDB,
PARTITION p201810 VALUES LESS THAN (737364) ENGINE = InnoDB,
PARTITION p201811 VALUES LESS THAN (737394) ENGINE = InnoDB,
PARTITION p201812 VALUES LESS THAN (737425) ENGINE = InnoDB) */;

 

INSERT INTO pv_day (uid,pv,uv,ip,create_date) VALUES ("50015655","988","937","493","2019-01-02"),("34064714","892","886","328","2019-01-02"),("50019060","296","281","246","2019-01-02"),("50009393","837","771","511","2019-01-02"),("50011754","837","719","518","2019-01-02"),("50007156","1498","1447","680","2019-01-02"),("34051522","526","515","313","2019-01-02"),("50015728","516","489","379","2019-01-02"),("50015590","854","797","548","2019-01-02"),("50015080","343","335","226","2019-01-02"),("50007015
(1526, 'Table has no partition for value 737426')

 

alter table pv_day add partition(partition p201901 VALUES LESS THAN (737456) ENGINE = InnoDB);
alter table pv_day sit_day add partition(partition p201902 VALUES LESS THAN (737484) ENGINE = InnoDB);
alter table pv_day add partition(partition p201903 VALUES LESS THAN (737515) ENGINE = InnoDB);

增加分区后

/*!50100 PARTITION BY RANGE (TO_DAYS ( create_date ))
(PARTITION p201705 VALUES LESS THAN (736846) ENGINE = InnoDB,
PARTITION p201706 VALUES LESS THAN (736876) ENGINE = InnoDB,
PARTITION p201707 VALUES LESS THAN (736907) ENGINE = InnoDB,
PARTITION p201708 VALUES LESS THAN (736938) ENGINE = InnoDB,
PARTITION p201709 VALUES LESS THAN (736968) ENGINE = InnoDB,
PARTITION p201710 VALUES LESS THAN (736999) ENGINE = InnoDB,
PARTITION p201711 VALUES LESS THAN (737029) ENGINE = InnoDB,
PARTITION p201712 VALUES LESS THAN (737060) ENGINE = InnoDB,
PARTITION p201801 VALUES LESS THAN (737091) ENGINE = InnoDB,
PARTITION p201802 VALUES LESS THAN (737119) ENGINE = InnoDB,
PARTITION p201803 VALUES LESS THAN (737150) ENGINE = InnoDB,
PARTITION p201804 VALUES LESS THAN (737180) ENGINE = InnoDB,
PARTITION p201805 VALUES LESS THAN (737211) ENGINE = InnoDB,
PARTITION p201806 VALUES LESS THAN (737241) ENGINE = InnoDB,
PARTITION p201807 VALUES LESS THAN (737272) ENGINE = InnoDB,
PARTITION p201808 VALUES LESS THAN (737303) ENGINE = InnoDB,
PARTITION p201809 VALUES LESS THAN (737333) ENGINE = InnoDB,
PARTITION p201810 VALUES LESS THAN (737364) ENGINE = InnoDB,
PARTITION p201811 VALUES LESS THAN (737394) ENGINE = InnoDB,
PARTITION p201812 VALUES LESS THAN (737425) ENGINE = InnoDB,
PARTITION p201901 VALUES LESS THAN (737456) ENGINE = InnoDB,
PARTITION p201902 VALUES LESS THAN (737484) ENGINE = InnoDB,
PARTITION p201903 VALUES LESS THAN (737515) ENGINE = InnoDB) */;

 

按月分区、按照uid分区，从业务角度比较；