【Mysql】自动分区

创建存储过程

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `add_partition_for_nowday_procedure`(IN `tableName` VARCHAR(50))
BEGIN
  DECLARE nowdate DATE ;
  SET @nowDate = 
  (SELECT 
    NOW()) ;
  SET @partitionName = DATE_FORMAT(@nowDate, 'p%Y%m%d') ;
  SET @nextdate = DATE_ADD(@nowDate, INTERVAL 1 DAY) ;
  SET @nextDateString = DATE_FORMAT(@nextdate, '%Y-%m-%d') ;
  SET @size = 
  (SELECT 
    COUNT(*) 
  FROM
    information_schema.partitions 
  WHERE CONVERT(table_name USING utf8) = tableName 
    AND CONVERT(partition_name USING utf8) = @partitionName) ;
  SELECT 
    tableName,
    @size ;
  IF @size = 0 
  THEN SET @partitionTemp = CONCAT(
    'ALTER TABLE ',
    tableName,
    ' ADD PARTITION (PARTITION ',
    @partitionName,
    ' VALUES LESS THAN (',
    '"',
    @nextDateString,
    '"',
    '))') ;
    SELECT 
	@partitionTemp ;
    SET @v_sql = @partitionTemp ;
    PREPARE stmt FROM @v_sql ;
    EXECUTE stmt ;
    DEALLOCATE PREPARE stmt ;
  END IF ;
END$$
DELIMITER ;

创建事件

DELIMITER $$

CREATE	EVENT `quote`.`auto_add_partition_event`

ON SCHEDULE EVERY 1 DAY STARTS '2019-12-06 00:00:00'

DO
	BEGIN
	    CALL add_partition_for_nowday_procedure ('t_quote_history') ;
	      CALL add_partition_for_nowday_procedure (
		't_quote_history_property_value'
	      ) ;
	END$$

DELIMITER ;