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

最新推荐文章于 2023-05-20 00:44:58 发布

wangdaxia163

最新推荐文章于 2023-05-20 00:44:58 发布

阅读量682

点赞数 1

CC 4.0 BY-SA版权

分类专栏： Mysql 分表

原文链接：https://www.cnblogs.com/bao-bei/p/13201747.html

分表同时被 2 个专栏收录

2 篇文章

订阅专栏

Mysql

1 篇文章

订阅专栏

本文介绍了一种Mysql数据库的分库分表策略，通过每月自动创建新表并备份旧表，解决大量日志数据导致的性能瓶颈。利用存储过程和事件调度实现了自动化流程。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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

文章转载地址：https://www.cnblogs.com/bao-bei/p/13201747.html

现考虑要分库分表了。每个月初先备份以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、删除定时器

1	`drop` `event create_login_history_table;`

2、暂停定时器

1	`ALTER` `EVENT create_login_history_table DISABLE;`

3、启动定时器

1	`ALTER` `EVENT create_login_history_table ENABLE;`

4、修改定时器

1	`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—用存储过程和事件每月定时创建一张数据库表

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

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