[MYSQL]按日期字段将已有数据分区

创建存储过程

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');