CREATE TABLE `t_test` (

    `id` INT (11),

    `dates` DATETIME

);

ALTER TABLE t_test ADD PRIMARY KEY (id);

ALTER TABLE t_test MODIFY id INT AUTO_INCREMENT;

DELIMITER //

CREATE  PROCEDURE `add_vote_memory`(n INT)

BEGIN

  DECLARE i INT DEFAULT 1;

  DECLARE datess VARCHAR(256);

    WHILE (i <= n ) DO

    -- 随机时间 这里生成的都是2018年的

    SELECT   CONCAT(2018,'-',LPAD(FLOOR(1 + (RAND() * 12)),2,0),'-',LPAD(FLOOR(1 + (RAND() * 28)),2,0)) INTO datess;

      INSERT INTO t_test(dates ) VALUES (datess);

            SET i=i+1;

    END WHILE;

END //

-- 调用存储过程 生成500000条

CALL add_vote_memory(500000);

CREATE TABLE t_range_test (

    id INT,

    dates DATETIME

)PARTITION BY RANGE COLUMNS ( dates ) (

    -- 第一组小于2018-01-01的

    PARTITION p1 VALUES LESS THAN  ( '' ),

    PARTITION p2 VALUES LESS THAN  ( '' ),

    PARTITION p3 VALUES LESS THAN  ( '' ),

    PARTITION p4 VALUES LESS THAN  ( '' ),

    PARTITION p5 VALUES LESS THAN  ( '' ),

    PARTITION p6 VALUES LESS THAN  ( '' ),

    PARTITION p7 VALUES LESS THAN  ( '' ),

    PARTITION p8 VALUES LESS THAN  ( '' ),

    PARTITION p9 VALUES LESS THAN  ( '' ),

    PARTITION p10 VALUES LESS THAN ( '' ),

    PARTITION p11 VALUES LESS THAN ( '' ),

    PARTITION p12 VALUES LESS THAN ( '' ),

    -- 最后一组小于2019-01-01大于2018-12-01的

    PARTITION p13 VALUES LESS THAN ( '' )

);

INSERT INTO t_range_test SELECT * FROM t_test;

EXPLAIN PARTITIONS SELECT * FROM t_range_test WHERE dates >= '' AND dates<='';

-- 查询这个表有多少分区

-- 查询每一个分区对应的数量

SELECT t.`PARTITION_NAME`,t.`SUBPARTITION_NAME`,t.`TABLE_ROWS` FROM information_schema.`PARTITIONS` t WHERE t.`TABLE_NAME` = 't_range_test';

-- 给表重新定义分区

ALTER TABLE t_range_test PARTITION BY RANGE COLUMNS ( dates ) (

    -- 第一组小于2018-01-01的

    PARTITION p1 VALUES LESS THAN  ( '' ),

    PARTITION p2 VALUES LESS THAN  ( '' ),

    PARTITION p3 VALUES LESS THAN  ( '' ),

    PARTITION p4 VALUES LESS THAN  ( '' ),

    PARTITION p5 VALUES LESS THAN  ( '' ),

    PARTITION p6 VALUES LESS THAN  ( '' ),

    PARTITION p7 VALUES LESS THAN  ( '' ),

    PARTITION p8 VALUES LESS THAN  ( '' ),

    PARTITION p9 VALUES LESS THAN  ( '' ),

    PARTITION p10 VALUES LESS THAN ( '' ),

    PARTITION p11 VALUES LESS THAN ( '' ),

    PARTITION p12 VALUES LESS THAN ( '' ),

    PARTITION p13 VALUES LESS THAN ( '' ),

    -- 最后一组小于2020-01-01大于2019-01-01的

    PARTITION p14 VALUES LESS THAN ( '' )

);