分区简单使用

DataAtlas

创建分区：

CREATE TABLE IF NOT EXISTS `partition_test` (
  `stat_date` date NOT NULL,
  `user_name` VARCHAR(255)
) 
PARTITION BY RANGE ( TO_DAYS(`stat_date`)) (
PARTITION P_20161213 VALUES LESS THAN (TO_DAYS('20161213')),
PARTITION P_20161214 VALUES LESS THAN (TO_DAYS('20161214')),
PARTITION P_20161215 VALUES LESS THAN (TO_DAYS('20161215')),
PARTITION P_20161216 VALUES LESS THAN (TO_DAYS('20161216')),
PARTITION P_20161217 VALUES LESS THAN (TO_DAYS('20161217')),
PARTITION P_MAX VALUES LESS THAN (MAXVALUE)
);

可以以日期间隔来区分。
插入相关数据:

INSERT INTO partition_test VALUES 
('2016-12-13', 'haha'),
('2016-12-14', 'haha'),
('2016-12-15', 'haha'),
('2016-12-16', 'haha'),
('2016-12-17', 'haha');

使用方式说明

1.删除分区并且删除数据（值得注意)：

ALTER TABLE partition_test DROP PARTITION P_20161214;

2.仅仅删除分区而不删除数据(假设删除20161214)：

ALTER TABLE partition_test REORGANIZE PARTITION P_20161214, P_20161215 INTO(
    PARTITION P_20161215 VALUES LESS THAN (TO_DAYS('20161215'))
);

3.添加分区方式1(如果采用前面的RANGE的方式，只能是往高处添加)：

ALTER TABLE partition_test ADD PARTITION (
    PARTITION P_20161217 VALUES LESS THAN (TO_DAYS('20161217'))
);

4.添加分区方式2(假设插入20161214):

ALTER TABLE partition_test REORGANIZE PARTITION P_20161215 INTO(
    PARTITION P_20161214 VALUES LESS THAN (TO_DAYS('20161214')),
    PARTITION P_20161215 VALUES LESS THAN (TO_DAYS('20161215'))
);