MySQL range columns分区表案例及验证是否实现了分区剪裁

创建分区表

CREATE TABLE range_columns ( 
    id INT,
    hiredate DATETIME
)
PARTITION BY RANGE COLUMNS(hiredate) (
    PARTITION p1 VALUES LESS THAN ( '20151202' ),
    PARTITION p2 VALUES LESS THAN ( '20151203' ),
    PARTITION p3 VALUES LESS THAN ( '20151204' ),
    PARTITION p4 VALUES LESS THAN ( '20151205' ),
    PARTITION p5 VALUES LESS THAN ( '20151206' ),
    PARTITION p6 VALUES LESS THAN ( '20151207' ),
    PARTITION p7 VALUES LESS THAN ( '20151208' ),
    PARTITION p8 VALUES LESS THAN ( '20151209' ),
    PARTITION p9 VALUES LESS THAN ( '20151210' ),
    PARTITION p10 VALUES LESS THAN ('20151211' )
);

插入数据到不同的分区

INSERT INTO range_columns (id, hiredate)
VALUES (1, '2015-12-01 10:00:00'), 
       (2, '2015-12-02 11:00:00'), 
       (3, '2015-12-03 12:00:00'),
       (4, '2015-12-04 13:00:00'),
       (5, '2015-12-05 14:00:00'),
       (6, '2015-12-06 15:00:00'),
       (7, '2015-12-07 16:00:00'),
       (8, '2015-12-08 17:00:00'),
       (9, '2015-12-09 18:00:00'),
       (10, '2015-12-10 19:00:00');

查询分区数据

SELECT * FROM range_columns partition(p1); 
SELECT * FROM range_columns partition(p2); 
SELECT * FROM range_columns partition(p3); 
SELECT * FROM range_columns partition(p4); 
SELECT * FROM range_columns partition(p5); 
SELECT * FROM range_columns partition(p6); 
SELECT * FROM range_columns partition(p7); 
SELECT * FROM range_columns partition(p8); 
SELECT * FROM range_columns partition(p9);

验证是否实现了分区剪裁

explain SELECT * FROM range_columns partition(p1);

---