mysql按日期自动分区_mysql 按日期分区

1、建立分区表

CREATE TABLE sale_data (

sale_date DATETIME NOT NULL,

ale_item VARCHAR(2) NOT NULL,

sale_money DECIMAL(10,2) NOT NULL

)

PARTITION BY RANGE ((year(sale_date)*100+month(sale_date))*100+day(sale_date)) (

PARTITION s0401 VALUES LESS THAN (0402),

PARTITION s0402 VALUES LESS THAN (0403),

PARTITION s0403 VALUES LESS THAN (0404),

PARTITION s0404 VALUES LESS THAN (0405),

PARTITION s0405 VALUES LESS THAN (0406),

PARTITION s0406 VALUES LESS THAN (0407),

PARTITION p1 VALUES LESS THAN (MAXVALUE)

);

2、增加分区

PARTITION p1 VALUES LESS THAN (MAXVALUE) 这句要去掉，才可以增加分区

ALTER TABLE sale_data

ADD PARTITION (PARTITION s0402 VALUES LESS THAN (0403));

3、删除分区

ALTER TABLE sale_data DROP PARTITION s0406 ;

4、正常使用<