mysql自动创建分区

 call Insert_Partition('2018-07-07','2019-01-01');

 

存储过程

BEGIN
 
DECLARE nowdate date;
DECLARE endtmp date;
declare dt VARCHAR(256);
declare partitionTemp VARCHAR(256);

set nowdate = DATE_FORMAT(beginDate,'%Y%m%d');
set endtmp = DATE_FORMAT(endDate,'%Y%m%d');
WHILE nowdate<endtmp 
DO

-- SELECT nowdate;
set dt = date_format(nowdate, '%Y%m%d');
-- select dt;
set partitionTemp = concat('ALTER TABLE 表名 ADD PARTITION (PARTITION p',dt,' VALUES LESS THAN (to_days(',"'",dt,"'",')))');


-- SELECT partitionTemp;  -- 这个是做打印测试，没什么作用


SET @v_sql=partitionTemp;   -- 注意很重要，将连成成的字符串赋值给一个变量（可以之前没有定义，但要以@开头）
PREPARE stmt FROM @v_sql;  -- 预处理需要执行的动态SQL，其中stmt是一个变量
    EXECUTE stmt;      -- 执行SQL语句
    DEALLOCATE PREPARE stmt;



set nowdate = DATE_ADD(nowdate,INTERVAL 1 DAY);

END WHILE;
 
END

 

加分区：
ALTER TABLE kin_info ADD PARTITION (PARTITION p20180704 VALUES LESS THAN (TO_DAYS ('2018-07-04')));
删除分区：
ALTER TABLE kin_info DROP PARTITION p20180718;