Mysqlは日単位でテーブルパーティション化されます

2081 ワード

1.データベース構造:テーブル名:income
id   income int_date
プライマリ・キー:id,int_date.int_を押すからdateパーティションなので、カラムのプライマリ・キーを追加する必要があります.
ストアド・プロシージャ:
DELIMITER $$
USE newdatabase$$

DROP PROCEDURE
IF EXISTS `create_Partition_income`$$

CREATE PROCEDURE `create_Partition_income`()label :
BEGIN
	/*     ,          ,                 。         23   ,Vlaue in    list  Rang less than*/
	SELECT
		REPLACE(
			partition_name,
			'incomep',
			''
		)INTO @PName
	FROM
		INFORMATION_SCHEMA. PARTITIONS
	WHERE
		table_name = 'income'
	ORDER BY
		partition_ordinal_position DESC
	LIMIT 1 ;
	IF ISNULL(@PName)THEN
		/*        ,      */
  SELECT
				MIN(int_date)INTO @minIntDate
			FROM
				income ;
			SET @maxIntDate = CAST(
				DATE_FORMAT(now(), '%Y%m%d')AS UNSIGNED INTEGER
			);

  /*	select @minIntDate,@maxIntDate ;*/
			SET @partStr='';
			WHILE @minIntDate <= @maxIntDate DO


			SET @partStr = CONCAT(
				@partStr,
				'PARTITION incomep' ,@minIntDate,
				' VALUES in (' ,@minIntDate,
				') ,'
			);
	 
	 
			END
			WHILE ;

			select @partStr;

			SET @listpart = CONCAT(
				' ALTER TABLE income PARTITION by list (int_date) (' ,@partStr,
				'PARTITION incomep' ,@maxIntDate+1 ,
				' VALUES in (' ,@maxIntDate+1,
				') )'
			); SELECT
				@listpart ; PREPARE stmt
			FROM
				@listpart ; EXECUTE stmt ; DEALLOCATE PREPARE stmt ;
			ELSE

			SET @PName = CAST(@PName AS UNSIGNED INTEGER);
			SET @TodayDate = CAST(
				DATE_FORMAT(now(), '%Y%m%d')AS UNSIGNED INTEGER
			); SELECT
				@TodayDate ;
			IF (@TodayDate+1) <=@PName THEN
				LEAVE label ; #       
			END
			IF ;
			SET @maxIntDate = @PName + 1 ; SELECT
				@maxIntDate ;
			SET @strsql = CONCAT(
				' ALTER TABLE income add PARTITION (PARTITION incomep' ,@maxIntDate,
				' VALUES in (' ,@maxIntDate,
				') )'
			); SELECT
				@strsql ; PREPARE stmt2
			FROM
				@strsql ; EXECUTE stmt2 ; DEALLOCATE PREPARE stmt2 ;
			END
			IF ; END$$
DELIMITER ;

毎日1回実行