欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

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

程序员文章站 2024-03-09 10:00:35
...

创建存储过程

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

 

相关标签: Mysql