创建存储过程
DELIMITER $$
USE `quote`$$
DROP PROCEDURE IF EXISTS `auto_create_partition_for_exist_data`$$
CREATE DEFINER = `root` @`localhost` PROCEDURE `auto_create_partition_for_exist_data` (
IN beginDate DATE,
IN endDate DATE,
IN table_name VARCHAR (50),
IN fieldTemp VARCHAR (50)
)
BEGIN
DECLARE nowdate DATE ;
DECLARE endtmp DATE ;
DECLARE dt VARCHAR (256) ;
DECLARE dtTemp VARCHAR (256) ;
DECLARE partitionTemp TEXT ;
SET nowdate = DATE_FORMAT(beginDate, '%Y%m%d') ;
SET endtmp = DATE_FORMAT(endDate, '%Y%m%d') ;
SET partitionTemp = CONCAT(
'ALTER TABLE ',
table_name,
' PARTITION BY RANGE COLUMNS(',
fieldTemp,
')('
) ;
WHILE
nowdate <= endtmp DO -- SELECT nowdate;
SET dt = DATE_FORMAT(nowdate, '%Y-%m-%d') ;
SET dtTemp = DATE_FORMAT(nowdate, '%Y%m%d') ;
SET partitionTemp = CONCAT(
partitionTemp,
'PARTITION p',
dtTemp,
' VALUES LESS THAN(',
"'",
dt,
"'",
')'
) ;
IF nowdate != endtmp
THEN SET partitionTemp = CONCAT(partitionTemp, ',') ;
END IF ;
SET nowdate = DATE_ADD(nowdate, INTERVAL 1 DAY) ;
END WHILE ;
SET partitionTemp = CONCAT(partitionTemp, ')') ;
SELECT
dt,
partitionTemp ;
SET @v_sql = partitionTemp ;
PREPARE stmt FROM @v_sql ;
EXECUTE stmt ;
DEALLOCATE PREPARE stmt ;
END $$
DELIMITER ;
调用存储过程
CALL auto_create_partition_for_exist_data('2019-10-24', '2019-12-01', 't_quote_history_property_value', 'create_time');