mysql 创建按月分区的自动脚本

mysql 创建按月分区的自动脚本

一、创建分区测试表

DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` varchar(20) DEFAULT NULL COMMENT '名称',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`,`create_time`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=9976111 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='测试表'
 PARTITION BY RANGE  COLUMNS(create_time)
(PARTITION P202208 VALUES LESS THAN ('2022-08-01') ENGINE = InnoDB,
 PARTITION P202209 VALUES LESS THAN ('2022-09-01') ENGINE = InnoDB,
 PARTITION P202210 VALUES LESS THAN ('2022-10-01') ENGINE = InnoDB,
 PARTITION P202211 VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB);

二、创建自动分区存储过程

CREATE DEFINER=`root`@`%` PROCEDURE `sp_create_partition`( IN db_name VARCHAR ( 100 ), IN tab_name VARCHAR ( 100 ), IN date_end date )
BEGIN
	
	SELECT partition_name INTO @partition_name
	FROM INFORMATION_SCHEMA.PARTITIONS WHERE CONVERT(TABLE_SCHEMA USING utf8) = db_name AND CONVERT(table_name USING utf8) = tab_name 
	ORDER BY partition_ordinal_position DESC LIMIT 1;
	
	if @partition_name is not null and @partition_name!='' then
		
		SET @dStart = STR_TO_DATE( REPLACE(concat(@partition_name,'01'),'P',''),'%Y%m%d' );
		SET @dLast = CAST( DATE_FORMAT(date_end,"%Y-%m-01") AS Date);
	
		IF @dStart IS NOT NULL AND @dLast is not null and @dStart < @dLast THEN
				
			SET @sqlTxt = CONCAT( 'alter table `', db_name, '`.', tab_name, ' reorganize partition P', date_format(@dStart,'%Y%m'), ' into (' );
			
			SET @idx = @dStart;
			WHILE @idx <= @dLast DO
			
				IF @idx < @dLast THEN
					SET @sqlTxt = CONCAT( @sqlTxt, 'partition P', date_format(@idx,'%Y%m'), ' values less than (''', date_format(@idx,'%Y-%m-01'), ''') ENGINE = InnoDB,' );
				ELSE 
					SET @sqlTxt = CONCAT( @sqlTxt, 'partition P', date_format(@idx,'%Y%m'), ' values less than (MAXVALUE) ENGINE = InnoDB)' );
				END IF;
				
				SET @idx = DATE_ADD(@idx,INTERVAL 1 month);
			
			END WHILE;
			
			-- 执行SQL
			PREPARE stmt FROM @sqlTxt;
			EXECUTE stmt;
			DEALLOCATE PREPARE stmt;
				
		END IF;
	END IF;
	
END

三、调用测试

-- 共三个参数： db：数据库名    test：分区表的名字    2023-12-01：截止月份
call sp_create_partition('db','test','2023-12-01');