mysql数据库定时⽣成表_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 $$
mysql存储过程使用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
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 ...
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论