关闭

使用mysql 存储过 对日志表按照天分区

511人阅读 评论(0) 收藏 举报


完整的存储过程如下:  时间格式如: 16060122   代表 2016年6月1日22点



先查看分区 :

  SELECT
  partition_name part, 
  partition_expression expr, 
  partition_description descr, 
  table_rows 
FROM
  INFORMATION_SCHEMA.partitions 
WHERE
  TABLE_SCHEMA = schema() 
  AND TABLE_NAME='factweblog'; 






CREATE DEFINER=`ETL002`@`localhost` PROCEDURE `partition_by_date`()
BEGIN
 declare currentYear INT   DEFAULT 16;
     DECLARE next_year INT DEFAULT 17;
     DECLARE janunary_max_day VARCHAR(32) DEFAULT '01-31';
     DECLARE march_max_day VARCHAR(32)  DEFAULT '03-31';
     DECLARE may_max_day VARCHAR(32) DEFAULT '05-31';
     DECLARE july_max_day VARCHAR(32) DEFAULT '07-31';
     DECLARE aug_max_day VARCHAR(32)  DEFAULT '08-31';
     DECLARE october_max_day VARCHAR(32) DEFAULT '10-31';
     DECLARE decem_max_day VARCHAR(32) DEFAULT '12-31';
     DECLARE april_max_day VARCHAR(32) DEFAULT '04-30';
     DECLARE june_max_day VARCHAR(32) DEFAULT '06-30';
     DECLARE novem_max_day VARCHAR(32) DEFAULT '11-30';
     DECLARE septem_max_day VARCHAR(32) DEFAULT '09-30';
     DECLARE febru_max_day VARCHAR(32) DEFAULT '02-22';
     DECLARE temp_month VARCHAR(32);
     DECLARE temp_day VARCHAR(32);
     DECLARE the_max_day_in_month int;
     DECLARE temp_strDate VARCHAR(32);a
DECLARE temp_strDate_2 VARCHAR(32);
 DECLARE temp_strDate_3 VARCHAR(32);
     DECLARE temp_partition blob;  


      
     DECLARE i int;
     DECLARE j int;
     #i 代表月份,j代表日期,i=6设置起始月份
     set i=6;
     set j=1;
   
     set febru_max_day=LAST_DAY(STR_TO_DATE(CONCAT(currentYear,'-',febru_max_day),'%Y-%m-%d %H:%i:%s'));#SET FEBRUARY LAST DAY
     set currentYear=YEAR(CURDATE());# GET CURRENT YEAR
       
 set currentYear=right(currentYear,2);
 set next_year=currentYear+1;


     set temp_partition=CONCAT('','ALTER TABLE factweblog PARTITION BY RANGE(StartTimeId) (');
 
     while(i<=12)do
         if i>9 THEN
          set temp_month=CONCAT('',i);
         else 
          set temp_month=CONCAT('0',i);
         end if;
                 if (temp_month = MONTH(CONCAT(currentYear,'-',janunary_max_day)) OR temp_month = MONTH(CONCAT(currentYear,'-',march_max_day)) OR temp_month = MONTH(CONCAT(currentYear,'-',may_max_day)) OR temp_month = MONTH(CONCAT(currentYear,'-',july_max_day)) OR temp_month = MONTH(CONCAT(currentYear,'-',aug_max_day)) OR temp_month = MONTH(CONCAT(currentYear,'-',october_max_day)) OR temp_month = MONTH(CONCAT(currentYear,'-',decem_max_day)))THEN
             set the_max_day_in_month = 31;
         elseif (temp_month = MONTH(CONCAT(currentYear,'-',april_max_day)) OR temp_month = MONTH(CONCAT(currentYear,'-',june_max_day)) OR temp_month = MONTH(CONCAT(currentYear,'-',septem_max_day)) OR temp_month = MONTH(CONCAT(currentYear,'-',novem_max_day)))THEN
              set the_max_day_in_month = 30;
         else
              set the_max_day_in_month = DAY(febru_max_day);
         end if;
         set j=1;
         while(j<=the_max_day_in_month)DO


              if j>9 THEN
                              set temp_day=CONCAT('',j);
              else 
                set temp_day=CONCAT('0',j);
              end if;
              set temp_strDate=CONCAT(currentYear,temp_month,temp_day,'00');  
  set temp_strDate_2=date_sub(CONCAT('20',currentYear,'-',temp_month,'-',temp_day),interval -1 day);
  set temp_strDate_3=concat(substring(replace(temp_strDate_2,'-',''),3,6),'00');
  
                            set temp_partition=CONCAT(temp_partition,'PARTITION p',currentYear,'',temp_month,'',temp_day,' VALUES LESS THAN(',temp_strDate_3,'),');
              SET j=j+1;
         end while; 
 
         set i=i+1;
     end while;
     set temp_strDate=CONCAT(next_year,'01','02','00');
     set temp_partition=CONCAT(temp_partition,'PARTITION p',next_year,'0101',' VALUES LESS THAN(',temp_strDate,'),');
     set temp_partition=CONCAT(temp_partition,'PARTITION theMaxPartition VALUES LESS THAN MAXVALUE');
     set temp_partition=CONCAT(temp_partition,');');
     SELECT temp_partition;
      SET @sql1=temp_partition;
     PREPARE sql2 FROM @sql1;
     EXECUTE sql2;
     DEALLOCATE PREPARE sql2;
end

完整的存储过程如下:  时间格式如: 16060122   代表 2016年6月1日22点



先查看分区 :

  SELECT
  partition_name part, 
  partition_expression expr, 
  partition_description descr, 
  table_rows 
FROM
  INFORMATION_SCHEMA.partitions 
WHERE
  TABLE_SCHEMA = schema() 
  AND TABLE_NAME='factweblog'; 






CREATE DEFINER=`ETL002`@`localhost` PROCEDURE `partition_by_date`()
BEGIN
 declare currentYear INT   DEFAULT 16;
     DECLARE next_year INT DEFAULT 17;
     DECLARE janunary_max_day VARCHAR(32) DEFAULT '01-31';
     DECLARE march_max_day VARCHAR(32)  DEFAULT '03-31';
     DECLARE may_max_day VARCHAR(32) DEFAULT '05-31';
     DECLARE july_max_day VARCHAR(32) DEFAULT '07-31';
     DECLARE aug_max_day VARCHAR(32)  DEFAULT '08-31';
     DECLARE october_max_day VARCHAR(32) DEFAULT '10-31';
     DECLARE decem_max_day VARCHAR(32) DEFAULT '12-31';
     DECLARE april_max_day VARCHAR(32) DEFAULT '04-30';
     DECLARE june_max_day VARCHAR(32) DEFAULT '06-30';
     DECLARE novem_max_day VARCHAR(32) DEFAULT '11-30';
     DECLARE septem_max_day VARCHAR(32) DEFAULT '09-30';
     DECLARE febru_max_day VARCHAR(32) DEFAULT '02-22';
     DECLARE temp_month VARCHAR(32);
     DECLARE temp_day VARCHAR(32);
     DECLARE the_max_day_in_month int;
     DECLARE temp_strDate VARCHAR(32);a
DECLARE temp_strDate_2 VARCHAR(32);
 DECLARE temp_strDate_3 VARCHAR(32);
     DECLARE temp_partition blob;  


      
     DECLARE i int;
     DECLARE j int;
     #i 代表月份,j代表日期,i=6设置起始月份
     set i=6;
     set j=1;
   
     set febru_max_day=LAST_DAY(STR_TO_DATE(CONCAT(currentYear,'-',febru_max_day),'%Y-%m-%d %H:%i:%s'));#SET FEBRUARY LAST DAY
     set currentYear=YEAR(CURDATE());# GET CURRENT YEAR
       
 set currentYear=right(currentYear,2);
 set next_year=currentYear+1;


     set temp_partition=CONCAT('','ALTER TABLE factweblog PARTITION BY RANGE(StartTimeId) (');
 
     while(i<=12)do
         if i>9 THEN
          set temp_month=CONCAT('',i);
         else 
          set temp_month=CONCAT('0',i);
         end if;
                 if (temp_month = MONTH(CONCAT(currentYear,'-',janunary_max_day)) OR temp_month = MONTH(CONCAT(currentYear,'-',march_max_day)) OR temp_month = MONTH(CONCAT(currentYear,'-',may_max_day)) OR temp_month = MONTH(CONCAT(currentYear,'-',july_max_day)) OR temp_month = MONTH(CONCAT(currentYear,'-',aug_max_day)) OR temp_month = MONTH(CONCAT(currentYear,'-',october_max_day)) OR temp_month = MONTH(CONCAT(currentYear,'-',decem_max_day)))THEN
             set the_max_day_in_month = 31;
         elseif (temp_month = MONTH(CONCAT(currentYear,'-',april_max_day)) OR temp_month = MONTH(CONCAT(currentYear,'-',june_max_day)) OR temp_month = MONTH(CONCAT(currentYear,'-',septem_max_day)) OR temp_month = MONTH(CONCAT(currentYear,'-',novem_max_day)))THEN
              set the_max_day_in_month = 30;
         else
              set the_max_day_in_month = DAY(febru_max_day);
         end if;
         set j=1;
         while(j<=the_max_day_in_month)DO


              if j>9 THEN
                              set temp_day=CONCAT('',j);
              else 
                set temp_day=CONCAT('0',j);
              end if;
              set temp_strDate=CONCAT(currentYear,temp_month,temp_day,'00');  
  set temp_strDate_2=date_sub(CONCAT('20',currentYear,'-',temp_month,'-',temp_day),interval -1 day);
  set temp_strDate_3=concat(substring(replace(temp_strDate_2,'-',''),3,6),'00');
  
                            set temp_partition=CONCAT(temp_partition,'PARTITION p',currentYear,'',temp_month,'',temp_day,' VALUES LESS THAN(',temp_strDate_3,'),');
              SET j=j+1;
         end while; 
 
         set i=i+1;
     end while;
     set temp_strDate=CONCAT(next_year,'01','02','00');
     set temp_partition=CONCAT(temp_partition,'PARTITION p',next_year,'0101',' VALUES LESS THAN(',temp_strDate,'),');
     set temp_partition=CONCAT(temp_partition,'PARTITION theMaxPartition VALUES LESS THAN MAXVALUE');
     set temp_partition=CONCAT(temp_partition,');');
     SELECT temp_partition;
      SET @sql1=temp_partition;
     PREPARE sql2 FROM @sql1;
     EXECUTE sql2;
     DEALLOCATE PREPARE sql2;
end

0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:3647次
    • 积分:86
    • 等级:
    • 排名:千里之外
    • 原创:5篇
    • 转载:0篇
    • 译文:0篇
    • 评论:0条
    文章存档