CREATE TABLE tr (id INT, name VARCHAR(50), purchased DATE) PARTITION BY RANGE( YEAR(purchased) ) (
PARTITION p0 VALUES LESS THAN (1990),
PARTITION p1 VALUES LESS THAN (1995),
PARTITION p2 VALUES LESS THAN (2000),
PARTITION p3 VALUES LESS THAN (2005),
PARTITION p4 VALUES LESS THAN (2010),
PARTITION p5 VALUES LESS THAN (2015)
(1, 'desk organiser', '2003-10-15'),
(2, 'alarm clock', '1997-11-05'),
(3, 'chair', '2009-03-10'),
(4, 'bookcase', '1989-01-10'),
(5, 'exercise bike', '2014-05-09'),
(6, 'sofa', '1987-06-05'),
(7, 'espresso maker', '2011-11-22'),
(8, 'aquarium', '1992-08-04'),
(9, 'study desk', '2006-09-16'),
(10, 'lava lamp', '1998-12-25');
SELECT * FROM tr PARTITION (p2);
-- 删除某个分区,注意:在删除某个分区后,该分区的数据也会对应的被删除
ALTER TABLE tr DROP PARTITION p2;
-- 对于在创建时,没有设置 partition 的 table,alter 初次设置其 partition 的命令
ALTER TABLE senti_weibo_realtime_weibo PARTITION BY RANGE( to_days(post_time) ) (
PARTITION p0 VALUES LESS THAN (to_days("2016-01-01")),
PARTITION p1 VALUES LESS THAN (to_days("2017-01-01")),
PARTITION p2 VALUES LESS THAN (to_days("2018-01-01")),
PARTITION p3 VALUES LESS THAN (to_days("2018-03-01")),
PARTITION p4 VALUES LESS THAN (to_days("2018-06-01")),
PARTITION p5 VALUES LESS THAN (to_days("2018-08-01")),
PARTITION p6 VALUES LESS THAN (to_days("2018-10-01")),
PARTITION p7 VALUES LESS THAN (to_days("2018-12-01")),
PARTITION p8 VALUES LESS THAN (to_days("2019-01-01")),
PARTITION p9 VALUES LESS THAN (to_days("2019-03-01")),
PARTITION p10 VALUES LESS THAN (to_days("2019-05-01")),
PARTITION p11 VALUES LESS THAN (to_days("2019-07-01")),
PARTITION p12 VALUES LESS THAN (to_days("2019-09-01")),
PARTITION p13 VALUES LESS THAN (to_days("2019-11-01")),
PARTITION p14 VALUES LESS THAN (to_days("2020-01-01")),
PARTITION p15 VALUES LESS THAN (to_days("2020-03-01")),
PARTITION p16 VALUES LESS THAN (to_days("2020-05-01")),
PARTITION p17 VALUES LESS THAN (to_days("2020-07-01")),
PARTITION p18 VALUES LESS THAN (to_days("2020-09-01")),
PARTITION p19 VALUES LESS THAN MAXVALUE