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

原创 2016年08月28日 22:22:12


完整的存储过程如下:  时间格式如: 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

版权声明:本文为博主原创文章,未经博主允许不得转载。

MySQL分表处理的实现方法(按月份分的表)

MySQL分表处理用于处理海量的数据库,下面就为您详细介绍MySQL分表处理的实现方法,希望对您能够有所启迪。 面对海量数据的表的时候,我们需要对它进行分表处理,我是按月份分的表,即每月都要建立一张...
  • rongwenbin
  • rongwenbin
  • 2014年10月21日 11:10
  • 5485

MySQL日志表按月分区

MySQL日志表按月自动分区
  • zhaolei742
  • zhaolei742
  • 2015年08月02日 11:40
  • 181

日志表设计一例分析

关于日志关系表设计一列分析。
  • yueliangdao0608
  • yueliangdao0608
  • 2014年05月21日 11:05
  • 7510

MySQL 通用查询日志(General Query Log)

同大多数关系型数据库一样,日志文件是MySQL数据库的重要组成部分。MySQL有几种不同的日志文件,通常包括错误日志文件,二进制日志,通用日志,慢查询日志,等等。这些日志可以帮助我们定义mysqld内...
  • robinson_0612
  • robinson_0612
  • 2014年10月04日 13:40
  • 26827

mysql分表分区方案实测:MyISAM引擎分表 or mysql表分区

mysql分表分区方案实测:MyISAM引擎分表 or mysql表分区
  • shandalue
  • shandalue
  • 2016年07月26日 14:32
  • 3784

[日志处理工作之六]kafka的分区策略 and 构建flume或logstash的采集与解析agent集群

Kafka的分区策略
  • bigstar863
  • bigstar863
  • 2015年06月29日 17:02
  • 1387

mysql自动按时间分区实例

#删除表 DROP TABLE `records`; #重新建表 CREATE TABLE `records`( `id` BIGINT(20) AUTO_INCREMENT, `advert_...
  • Guns_NRoses
  • Guns_NRoses
  • 2016年11月30日 18:11
  • 2597

MYSQL之表分区----按日期分区

错误的按日期分区例子 最直观的方法,就是直接用年月日这种日期格式来进行常规的分区: PLAIN TEXT CODE: mysql> create table rms (d date) -> ...
  • zhang168
  • zhang168
  • 2015年07月16日 14:23
  • 8584

Mysql PARTITION 数据表分区技术日期

参考:http://www.sunzhenghua.com/mysql-myisam-innodb-partition-range-list-hash 在这一章节里, 我们来了解下 ...
  • jueshengtianya
  • jueshengtianya
  • 2014年05月13日 14:08
  • 875

MYSQL(分表)千万级数据量的优化方法积累

转载http://blog.sina.com.cn/s/blog_85ead02a0101csci.html 1、分库分表 很明显,一个主表(也就是很重要的表,例如用户表)无限制的增长势必严重影响...
  • zhaoliang831214
  • zhaoliang831214
  • 2016年10月11日 17:32
  • 4256
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:使用mysql 存储过 对日志表按照天分区
举报原因:
原因补充:

(最多只允许输入30个字)