-- 创建分区表
create table sales(
		order_name varchar(32),
    order_date datetime not null
)ENGINE= InnoDB PARTITION BY RANGE(YEAR(order_date))(
    PARTITION P_2017 VALUES LESS THAN(2017),
    PARTITION P_2018 VALUES LESS THAN(2018),
    PARTITION P_2019 VALUES LESS THAN(2019),
    PARTITION P_catchall VALUES LESS THAN MAXVALUE
);
-- 插入数据
INSERT INTO `test`.`sales` (`order_name`, `order_date`) VALUES ('1', '2021-07-02 17:03:45');
INSERT INTO `test`.`sales` (`order_name`, `order_date`) VALUES ('2', '2020-07-02 17:04:13');
INSERT INTO `test`.`sales` (`order_name`, `order_date`) VALUES ('3', '2019-07-02 17:04:13');
INSERT INTO `test`.`sales` (`order_name`, `order_date`) VALUES ('4', '2018-07-02 17:04:13');
INSERT INTO `test`.`sales` (`order_name`, `order_date`) VALUES ('5', '2017-07-02 17:04:13');

-- 查看数据
select * from sales

-- 查看分区
select 
  partition_name part,  
  partition_expression expr,  
  partition_description descr,  
  table_rows  
from information_schema.partitions  where 
  table_schema = schema()  
  and table_name='sales'; 
	
-- 删除分区
alter table sales drop partition P_2017,P_2018,P_2019,P_2020,P_2021; 

-- 重新建分区
ALTER TABLE sales PARTITION by RANGE(YEAR(order_date))(
    PARTITION P_2017 VALUES LESS THAN(2017),
    PARTITION P_2018 VALUES LESS THAN(2018),
    PARTITION P_2019 VALUES LESS THAN(2019),
		PARTITION P_2020 VALUES LESS THAN(2020),
		PARTITION P_2021 VALUES LESS THAN(2021),
    PARTITION P_catchall VALUES LESS THAN MAXVALUE
);


-- 分析分区
ALTER TABLE emp analyze partition p1,p2;



 

最后修改于 2021-07-02 17:30:35
如果觉得我的文章对你有用,请随意赞赏
扫一扫支付
上一篇