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;