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

最新推荐文章于 2023-09-19 10:52:53 发布
最新推荐文章于 2023-09-19 10:52:53 发布
阅读量2.1k 收藏
点赞数 1
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/lilizhenzhu/article/details/52348328
版权


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

pearl915
关注
  • 1
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
MySQL分区表自动创建及删除存储过程
01-28
用存储过程实现了MySQL数据库分区表的自动创建和自动删除功能。亲测有效。希望有用。
mysql日志文件开启及详解：General_log 和 Binlog
热门推荐
Abysscarry的博客
04-15 1万+
背景： 周末归纳下mysql的日志文件，其中general_log在mysql入侵中已经用到过，binlog即将会用到。注：mysql版本为5.7.20 General_log 详解 1.介绍 开启 general log 将所有到达MySQL Server的SQL语句记录下来。 一般不会开启开功能，因为log的量会非常庞大。但个别情况下可能会临时的开一会儿general ...
MySQL分区表的正确使用方法，适用场景，建立分区的条件
qq_43842093的博客
08-20 4675
MySQL 数据库中的数据是以文件的形势存在磁盘上的，默认放在目录下面，我们可以通过命令来查看：我们进入到这个目录下，就可以看到我们定义的所有数据库了，一个数据库就是一个文件夹，一个库中，有其对应的表的信息，如下：.frm.myi.myd*.frm：这个是表定义，是描述表结构的文件。*.myd：这个是数据信息文件，是表的数据文件。*.myi：这个是索引信息文件。如果存储引擎是InnoDB.frm.ibd*.frm：表结构文件。*.ibd：表数据和索引的文件。
mysql日志详解
桀骜浮沉的博客
03-20 277
mysql日志详解 1.错误日志my.cnf 默认开启 log_error=/data/mysql/logs/error.log //错误日志路径 2.一般查询日志，记录客户端所有操作 general_log = 0 //默认值是0，即不开启，可设置为1 general_log_file = /data/mysql/logs/general.log //指定日志位置及名称 log-output=[value,.....] //用于定义general_log和slow_log的输出目标 value=
【mysql】日志表-表分区
在知识的海洋里遨游
12-03 1965
mysq，把日志表由普通表转为分区表，设计是按照ID+transDate 来分区，按照交易量，每个月一个区。
mysql服务器常规日志及慢日志
hjkl52wh的博客
01-31 308
mysql数据库常规日志、慢日志 配置及使用说明
MySQL分区表的正确使用方法
12-15
MySQL分区表概述 我们经常遇到一张表里面保存了上亿甚至过十亿的记录，这些表里面保存了大量的历史记录。 对于这些历史数据的清理是一个非常头疼事情，由于所有的数据都一个普通的表里。所以只能是启用一个或多个带...
mysql实现自动创建与删除分区
08-19
实现mysql按时间分区方式自动创建与删除分区，包括创建/删除日志记录，通过存储过程与事件联合实现，自动创建数量与删除数量可动态配置
新版 MySQL DBA 高级视频 基于MySQL 5.7 MySQL 8.0版本.rar
07-17
│ 7_MySQL存储引擎.mp4 │ 8_MySQL第三范式设计讲解.mp4 │ 9_MySQL数据库设计工具.mp4 │ ├─新版MySQL DBA综合实战班 第06天 │ 1_课堂作业讲解.mp4 │ 2_InnoDB内核之事务和多版本控制.mp4 │ 3_InnoDB底层...
CentOS6.7 mysql5.6.33修改数据文件位置的方法
01-19
1、查看mysql存储目录 #安装目录 --basedir=/usr #数据存储位置 --datadir=/home/mysqlData/mysql #用户 --user=mysql #日志 --log-error=/home/mysqlData/mysql/localhost.localdomain.err #端口 --port=3306 2、...
基于MySql的海量日志分析系统设计
04-17
基于MySql的海量日志分析系统设计 主要内容： 日志分析系统查询需求分析 访问特点分析 基于性能考虑的系统体系架构 基于需求的mysql优化及表设计 基于需求的memcache使用 其他开源工具的使用 总结
MySQL创建分区表，并按天自动分区
雅冰石的专栏
04-14 6241
#创建测试数据库 create database csl_test character set utf8 collate utf8_unicode_ci; #创建测试表 注：要分区的字段 需要为主键。 use csl_test; # 切换到刚刚创建的测试数据库 create table t_partition_test ( pk_id bigint(20) not null auto_increment, time datetime not null, msg varchar(2...
Mysql按天自动表分区教程
最新发布
weixin_43249277的博客
09-19 1305
在使用mysql时，往往用一张表来存放数据，不同的业务可能产生的数据量也不同，有的业务可能一天需要插入几万条数据，也有的则更多，即使量很小日积月累数据库表的数据堆积也会越来越多，需要人工去删除数据，当数据达到百万或千万级别时，mysql查询速度明显下降，性能也随之下降，解决该问题的办法有很多，比如添加索引、优化SQL，但这都只是治标不治本的做法，无法从根本上提高Mysql的性能和查询速度.文章通过自动创建分区表，按天创建分区，定时按天新增分区、定时删除分区，对数据表进行优化。
mysql 日志表分表_mysql如何查询多样同样的表/sql分表查询、java项目日志表分表的开发思路/按月分表...
weixin_28756347的博客
02-05 805
之前开发的一个监控系统，数据库的日志表是单表，虽然现在数据还不大并且做了查询sql优化，不过以后数据库的日志表数据肯定会越来越庞大，将会导致查询缓慢，所以把日志表改成分表，日志表可以按时间做水平分表，我是按月分的，每个月一张表，这时候的问题是数据库有多张同样的分表如何根据条件查询？在进行分页的时候如何计算总记录数？如何查询出所有分表？每个月的新表是如何创建？系统如何自动创建?不确定哪个分表的情况如...
MySQL数据库七种日志介绍
iuu77的博客
02-26 1931
bin log是一种数据库Server层（和存储引擎无关），以二进制形式存储在磁盘中的逻辑日志。bin log记录了数据库所有的DDL和DML操作（不包括SELECT 和 SHOW）bin log也叫做归档日志，因为他不像redo log一样循环写擦除之前的记录，而是会一直记录日志。如果单个日志文件大小超过 max_binlog_size，就会创建新的文件继续写入。bin log日志的内容格式其实就是执行SQL命令的反向逻辑，（和undo log有点类似）。
MySQL运维之日志、主从复制、分库分表
m0_63932570的博客
11-01 348
主库的binlog记录着全部数据变更的数据，从库iothread发起请求，读取返回binlog日志信息，并写入到自身的relay_log。从库的sqllog读取relay_log数据变化，并放映到自身数据库的数据变化。最后在从库中再次执行主库那条数据改变的sql。个人练习建议直接关闭防火墙即可，在实际的生产中都是关闭3306端口。这个mysql_query.log就是创建产生的查询日志文件。由于这份查询日志比较大，故平时没用到的话都是关闭掉的。但这条命令是看不到数据的，只有加上-v才能看到数据。
mysql按天执行分区表sql_Mysql按天自动表分区教程
weixin_39520199的博客
01-18 544
Mysql按天自动表分区教程前言什么是表分区？创建测试数据库创建测试表手动进行分区批量进行分区单条增加分区删除分区命令插入数据查看表分区创建增加分区和删除分区代码手动调用存储程序命令开启事件新建event事件前言在使用mysql时，往往用一张表来存放数据，不同的业务可能产生的数据量也不同，有的业务可能一天需要插入几万条数据，也有的则更多，即使量很小日积月累数据库表的数据堆积也会越来越多，需要人工去...
always on sql 收缩日志_原来MySQL的日志文件也是非常的重要的
weixin_39570838的博客
12-30 224
MySQL中有八种日志文件，分别是：重做日志（redo log），回滚日志（undo log），二进制日志（binlog），错误日志（errorlog），慢查询日志（slow query log），一般查询日志（general log），中继日志（relay log），DDL日志 （metadata log），他们分别都有各自的作用，而且默认情况下，服务器的日志文件都位于数据目录（datadir）...
Mysql数据分区---日志表数据过大问题处理
m18066975852的博客
03-01 538
1.建表 2.自动创建分区 3.自动删除分区 4.创建一个Event,每月执行一次 5.测试
mysql数据量达到多少、或者什么情况下需要分区分表
08-10
例如，对于大量的日志数据，可以按照日期进行分区，每个分区只存储一段时间的数据，便于后续的查询和维护操作。 需要注意的是，分区分表需要综合考虑数据库的设计、硬件配置和业务需求，不是简单地根据数据量大小...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值