Mysql—用存储过程和事件每月定时创建一张数据库表

摘要:
在每个月初备份sys_oper_Database表,以log为前缀,以date、month和year为后缀,例如:sys_oper_log_202007,然后创建一个新的_oper_log数据库表。首先创建存储过程:DELIMITER//CREATEPROCEDURECreate_table_sys_oper_log_month()BEGINDECLARE`@后缀`VARCHAR;声明`@sqlstrcreate`VARCHAR;声明`@sqlstrupdate`VARCHAR;SET`@suffix`=DATE_FORMAT;SET@sqlstrupdate=凹面;SET@sqlstrcreate=凹面;PREPAREstmtupdateFROM@sqlstrupdate ; 执行安装日期;PREPAREstmtcreateFROM@sqlstrcreate ; 执行创建;结束;然后创建一个事件并在每个月的第一天执行上述存储过程:DELIMITER$$SETGLOBALevent_schedur=1;CREATEVENTEvent_ create_ table_ every_monthONSCHEDULEEVERY1MONTHSTARTS'2020-07-0100:00:00'ONCOMPLETIONPPRESERVEENABLEDOBEGINCALLcreate_ table_sys_ operat_ log_ month();通过Navicate,END可以查看MySQL并每月定期创建一个包含存储过程和事件的数据库表。没关系!将此过程存储在数据库---存储过程中。

业务需求,把用户操作日志写入一张日志表sys_oper_log。
上线2个月来,有74万条记录了。

现考虑要分库分表了。每个月初先备份以sys_oper_log为前缀,日期年月为后缀的数据库表,比如:sys_oper_log_202007 ,然后再创建一张以sys_oper_log数据库表。

实现思路
Mysql如何每月自动建表?
一、新建事件每月调用存储过程
二、存储过程里面建表
1、获取当前时间,转换字符串
2、拼接sql语句建表

实现方法
把下面两段复制到sql,执行即可。

首先创建存储过程:

DELIMITER //
CREATE PROCEDURE create_table_sys_oper_log_month()
BEGIN
DECLARE `@suffix` VARCHAR(15);
DECLARE `@sqlstrcreate` VARCHAR(2560);
DECLARE `@sqlstrupdate` VARCHAR(2560);
SET `@suffix` = DATE_FORMAT(NOW(),'%Y%m');

SET @sqlstrupdate = CONCAT(
"alter table sys_oper_log rename to sys_oper_log_",
@suffix
);

SET @sqlstrcreate = CONCAT(
"CREATE TABLE `sys_oper_log` (
  `id` int(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `oper_id` int(20) DEFAULT NULL COMMENT '操作人id',
  `title` varchar(100) DEFAULT NULL COMMENT '操作模块',
  `business_type` int(2) DEFAULT NULL COMMENT '业务类型(0其它 1新增 2修改 3删除)',
  `req_method` varchar(200) DEFAULT NULL COMMENT '请求方法',
  `request_method` varchar(50) DEFAULT NULL COMMENT '请求方式',
  `operator_type` int(2) DEFAULT NULL COMMENT '操作类别(0其它 1后台用户 2手机端用户)',
  `oper_name` varchar(30) DEFAULT NULL COMMENT '操作人名称',
  `dept_name` varchar(30) DEFAULT NULL COMMENT '部门名称',
  `oper_url` varchar(100) DEFAULT NULL COMMENT '请求url',
  `oper_ip` varchar(20) DEFAULT NULL COMMENT '操作ip',
  `oper_location` varchar(20) DEFAULT NULL COMMENT '操作地点',
  `oper_param` varchar(300) DEFAULT NULL COMMENT '请求参数',
  `json_result` longtext CHARACTER SET utf8mb4 COMMENT '返回参数',
  `req_status` int(2) DEFAULT NULL COMMENT '操作状态(0正常 1异常)',
  `error_msg` varchar(2000) CHARACTER SET utf8mb4 DEFAULT NULL COMMENT '错误信息',
  `oper_time` datetime DEFAULT NULL COMMENT '操作时间',
  PRIMARY KEY (`id`),
  KEY `id` (`id`) USING HASH,
  KEY `oper_url` (`oper_url`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=358859 DEFAULT CHARSET=utf8 COMMENT='系统日志表';
"
);
PREPARE stmtupdate FROM @sqlstrupdate;
EXECUTE stmtupdate;
PREPARE stmtcreate FROM @sqlstrcreate;
EXECUTE stmtcreate;
END;

然后创建事件每月1日执行上面的存储过程:

DELIMITER $$
SET GLOBAL event_scheduler = 1;
CREATE EVENT event_create_table_every_month
ON SCHEDULE EVERY 1 MONTH
STARTS '2020-07-01 00:00:00'
ON  COMPLETION  PRESERVE
ENABLE
DO
BEGIN
CALL create_table_sys_oper_log_month();
END

  

通过Navicate可以查看到

Mysql用存储过程和事件每月定时创建一张数据库表

这样就OK了!

扩展知识:

一、什么是存储过程?

在一些语言中,有“过程”这种概念,procedure,和"函数",function,
PHP中没有过程,只有函数。
过程:封装了若干条语名,调用时,这些封装体执行。
函数:是一个有返回值的过程。
过程:是没有返回值的函数。

我们把若干条sql封装起来,取个名字,---过程。
把此过程存储在数据库中,---存储过程。

存储过程创建语法:
定义:
create procedure procedureName()
begin
--sql语句;
end$
查看:
show procedure status;
调用:
call procedure()

存储过程是可以编程的

即可以用变量,表达式,控制语句来完成复杂的功能。

在存储过程中,用declare声明变量
格式: declare 变量名 变量类型。

例:

create procedure p2()
begin 
declare age int default 18;
declare height int default 180;
select concat('年龄是',age,'身高是',height);
end$

有变量,就能运算,有运算,有运算就能控制。
运算的结果,如何赋值给变量。
set 变量名:=变量值

例:

create procedure p3()
begin
declare age int default 18;
set age:=age+20;
select concat(’20年后年龄是’,age);
end$

-- if/else控制结构
if condition then
statement
else
end if;

--p5 给存储过程传参
存储过程的括号里,可以声明参数,
语法是[in/out/inout] 参数名 参数类型
例:

create procedure p5(width int,height int)
begin 
select concat('你的面积是',width*height)  as area;
if width>height then
select '你很胖';
else if width<height then
select '你很瘦';
else
select '你很方';
end if;
end$

call p5(3,4)$

二、定时器

1、删除定时器
drop event create_login_history_table;
2、暂停定时器
ALTER EVENT create_login_history_table DISABLE;
3、启动定时器
ALTER EVENT create_login_history_table ENABLE;
4、修改定时器
ALTER EVENT create_login_history_table ON SCHEDULE EVERY 5 DAY;
5、一些定时例子
/*每10秒*/
create event create_login_history_table on schedule every 10 second do ...
/*2天后*/
CREATE EVENT create_login_history_table ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 2 DAY do ...
/*指定时间*/
CREATE EVENT create_login_history_table ON SCHEDULE AT TIMESTAMP '2017-08-01 00:00:00' do ...
/*每天*/
CREATE EVENT create_login_history_table ONSCHEDULE EVERY 1 DAY DO ...
/*每天定时执行,5天后停止执行*/
CREATE EVENT create_login_history_table ON SCHEDULE EVERY 1 DAY ENDS CURRENT_TIMESTAMP + INTERVAL 5 DAY DO  ...
/*5天后开启每天执行,一个月后停止执行*/
CREATE EVENT e_test ON SCHEDULE EVERY 1 DAY STARTS CURRENT_TIMESTAMP+ INTERVAL 5 DAY ENDS CURRENT_TIMESTAMP+ INTERVAL 1 MONTH DO ...

  

免责声明:文章转载自《Mysql—用存储过程和事件每月定时创建一张数据库表》仅用于学习参考。如对内容有疑问,请及时联系本站处理。

上篇Spring缓存注解@Cache使用CSS布局之display: tables布局下篇

宿迁高防,2C2G15M,22元/月;香港BGP,2C5G5M,25元/月 雨云优惠码:MjYwNzM=

相关文章

sysbench对自装MySQL数据库进行基准测试

一、 安装sysbench wget https://packagecloud.io/install/repositories/akopytov/sysbench/script.rpm.sh chmod +x script.rpm.sh ./script.rpm.sh yum install -y sysbench 二、准备测试表 sysbench //...

pg数据库的备份和恢复

1 备份恢复方法 sql转储 文件系统级备份 连续归档 2 sql转储 sql转储方法的思想就是创建一个由SQL命令组成的文件,当把这个文件返回数据库时候,数据库利用其中的sql命令重建与转储状态一样的数据库实例。postgresql提供的工具是pg_dump,这个工具的基本用法如下: 备份:pg_dump dbname > outfile 恢复...

Debian下自动备份MySQL数据库并上传到远程FTP服务器且删除指定日期前的备份Shell脚本

说明:  1、备份MySQL数据库存放目录/var/lib/mysql下面的osyunweidata数据库到/home/mysql_data里面,并且保存为osyunweidata_bak_2012_06_30.tar.gz的压缩文件格式(2012_06_30是指备份执行时当天的日期),最后只保留最近7天的备份 2、上传/home/mysql_data里面...

DockerCompose

一、Docker Compose 1、前言 我们知道使⽤⼀个 Dockerfile 模板⽂件,可以让⽤户很⽅便的定义⼀个单独的应⽤容器。然⽽,在⽇常⼯作中,经常会碰到需要多个容器相互配合来完成某项任务的情况。 例如要实现⼀个 Web 项⽬,除了 Web 服务容器本身,往往还需要再加上后端的数据库服务容器,甚⾄还包括负载均衡容器等,我们只能一个一个`写doc...

主机连接虚拟机的mysql 记录

   检查远程的虚拟机是否可以ping通过   查看虚拟机IP为192.168.38.128      cmd窗口ping 192.168.38.128,出现如下界面说明是可以的      检查虚拟机mysql的端口号是否对外开通   比如我们能用xshell等远程工具连接虚拟机上的linux,是因为开放了22端口   查看mysql的端口,在mysql命...

Hive metastore源码阅读(一)

不要问我为什么,因为爱,哈哈哈哈。。。进入正题,最近做项目顺带学习了下hive metastore的源码,进行下知识总结。 hive metastore的整体架构如图: 一、组成结构: 如图我们可以看到,hive metastore的组成结构分为 客户端 服务端 ,那么下来我们逐一进行分析: 1、客户端 从代码的角度来看:尼玛太多了。。我们从入口HIV...