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

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

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

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

首先创建存储过程：

  1. DELIMITER //
  2. CREATE PROCEDURE create_table_sys_oper_log_month()
  3. BEGIN
  4. DECLARE `@suffix` VARCHAR(15);
  5. DECLARE `@sqlstrcreate` VARCHAR(2560);
  6. DECLARE `@sqlstrupdate` VARCHAR(2560);
  7. SET `@suffix` = DATE_FORMAT(NOW(),\'%Y%m\');
  8.  
  9. SET @sqlstrupdate = CONCAT(
  10. "alter table sys_oper_log rename to sys_oper_log_",
  11. @suffix
  12. );
  13.  
  14. SET @sqlstrcreate = CONCAT(
  15. "CREATE TABLE `sys_oper_log` (
  16.   `id` int(20) NOT NULL AUTO_INCREMENT COMMENT \'主键\',
  17.   `oper_id` int(20) DEFAULT NULL COMMENT \'操作人id\',
  18.   `title` varchar(100) DEFAULT NULL COMMENT \'操作模块\',
  19.   `business_type` int(2) DEFAULT NULL COMMENT \'业务类型（0其它 1新增 2修改 3删除）\',
  20.   `req_method` varchar(200) DEFAULT NULL COMMENT \'请求方法\',
  21.   `request_method` varchar(50) DEFAULT NULL COMMENT \'请求方式\',
  22.   `operator_type` int(2) DEFAULT NULL COMMENT \'操作类别（0其它 1后台用户 2手机端用户）\',
  23.   `oper_name` varchar(30) DEFAULT NULL COMMENT \'操作人名称\',
  24.   `dept_name` varchar(30) DEFAULT NULL COMMENT \'部门名称\',
  25.   `oper_url` varchar(100) DEFAULT NULL COMMENT \'请求url\',
  26.   `oper_ip` varchar(20) DEFAULT NULL COMMENT \'操作ip\',
  27.   `oper_location` varchar(20) DEFAULT NULL COMMENT \'操作地点\',
  28.   `oper_param` varchar(300) DEFAULT NULL COMMENT \'请求参数\',
  29.   `json_result` longtext CHARACTER SET utf8mb4 COMMENT \'返回参数\',
  30.   `req_status` int(2) DEFAULT NULL COMMENT \'操作状态（0正常 1异常）\',
  31.   `error_msg` varchar(2000) CHARACTER SET utf8mb4 DEFAULT NULL COMMENT \'错误信息\',
  32.   `oper_time` datetime DEFAULT NULL COMMENT \'操作时间\',
  33.   PRIMARY KEY (`id`),
  34.   KEY `id` (`id`) USING HASH,
  35.   KEY `oper_url` (`oper_url`) USING BTREE
  36. ) ENGINE=InnoDB AUTO_INCREMENT=358859 DEFAULT CHARSET=utf8 COMMENT=\'系统日志表\';
  37. "
  38. );
  39. PREPARE stmtupdate FROM @sqlstrupdate;
  40. EXECUTE stmtupdate;
  41. PREPARE stmtcreate FROM @sqlstrcreate;
  42. EXECUTE stmtcreate;
  43. END;

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

  1. DELIMITER $$
  2. SET GLOBAL event_scheduler = 1;
  3. CREATE EVENT event_create_table_every_month
  4. ON SCHEDULE EVERY 1 MONTH
  5. STARTS \'2020-07-01 00:00:00\'
  6. ON  COMPLETION  PRESERVE
  7. ENABLE
  8. DO
  9. BEGIN
  10. CALL create_table_sys_oper_log_month();
  11. END

　　

通过Navicate可以查看到

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

这样就OK了！

扩展知识：

一、什么是存储过程？

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

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

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

存储过程是可以编程的

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

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

例：

  1. create procedure p2()
  2. begin 
  3. declare age int default 18;
  4. declare height int default 180;
  5. select concat(\'年龄是\',age,\'身高是\',height);
  6. end$

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

例：

  1. create procedure p3()
  2. begin
  3. declare age int default 18;
  4. set age:=age+20;
  5. select concat(’20年后年龄是’,age);
  6. end$

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

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

  1. create procedure p5(width int,height int)
  2. begin 
  3. select concat(\'你的面积是\',width*height)  as area;
  4. if width>height then
  5. select \'你很胖\';
  6. else if width<height then
  7. select \'你很瘦\';
  8. else
  9. select \'你很方\'；
  10. end if;
  11. end$

call p5(3,4)$

二、定时器

  1. drop event create_login_history_table;
  1. ALTER EVENT create_login_history_table DISABLE;
  1. ALTER EVENT create_login_history_table ENABLE;
  1. ALTER EVENT create_login_history_table ON SCHEDULE EVERY 5 DAY;
  1. /*每10秒*/
  2. create event create_login_history_table on schedule every 10 second do ...
  3. /*2天后*/
  4. CREATE EVENT create_login_history_table ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 2 DAY do ...
  5. /*指定时间*/
  6. CREATE EVENT create_login_history_table ON SCHEDULE AT TIMESTAMP \'2017-08-01 00:00:00\' do ...
  7. /*每天*/
  8. CREATE EVENT create_login_history_table ONSCHEDULE EVERY 1 DAY DO ...
  9. /*每天定时执行，5天后停止执行*/
  10. CREATE EVENT create_login_history_table ON SCHEDULE EVERY 1 DAY ENDS CURRENT_TIMESTAMP + INTERVAL 5 DAY DO  ...
  11. /*5天后开启每天执行，一个月后停止执行*/
  12. CREATE EVENT e_test ON SCHEDULE EVERY 1 DAY STARTS CURRENT_TIMESTAMP+ INTERVAL 5 DAY ENDS CURRENT_TIMESTAMP+ INTERVAL 1 MONTH DO ...

　　

 

版权声明：本文为bao-bei原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://www.cnblogs.com/bao-bei/p/13201747.html