MySql-存储过程和事件，每月定时创建一张表

夏寻.

已于 2023-06-16 14:50:55 修改

阅读量1.1k

点赞数

文章标签： mysql java 数据库

于 2023-06-16 14:46:31 首次发布

本文链接：https://blog.csdn.net/weixin_51435347/article/details/131245366

版权

一、存储过程基本命令

二、Mysql如何每月自动建表？

三、MySQL定时创建表和事件的SQL语句

四、水平分表思考

1.查询多表整合

2.app端和后台管理怎么进行分页查询

一、存储过程基本命令

1.创建存储过程语法

语法 --由 [] 所包裹的可有，也可无

CREATE PROCEDURE 存储过程名称 ([参数IN\OUT\OUTIN[...]])
begin
-- SQL语句,我们所执行的逻辑，我们的存储过程中的逻辑都放在begin和end中间;
end

实例

CREATE PROCEDURE getListUser()
begin
SELECT * FROM sys_user;
end

2.调用存储过程

call 存储过程名称([指定的参数，无参数则不传]) //call test()

3.查看数据库全部存储过程

SHOW PROCEDURE STATUS --mysql8.0及以上版本

4.查看指定存储过程的定义

SHOW CREATE PROCEDURE 存储过程名称； --Create Procedure字段是创建信息

二、Mysql如何每月自动建表？

一、新建事件每月调用存储过程
二、存储过程里面建表
1、获取当前时间，转换字符串
2、拼接sql语句建表

三、MySQL定时创建表和事件的SQL语句

我们先开启mysql事件，

1，通过动态参数修改：SET GLOBAL event_scheduler = ON (临时重启后失效)

;注意：还是要在my.cnf中添加event_scheduler=ON。

因为如果没有添加的话，mysql重启事件又会回到原来的状态了。

MySQL8.0版本event_scheduler默认是1。

-- 查看事件计划
SELECT @@event_scheduler
-- 打开事件计划
SET GLOBAL event_scheduler = 1 (临时重启后永久)
-- 存储过程，每月创建一张表
CREATE PROCEDURE create_table()
BEGIN
declare str_date varchar(16);
SET str_date = date_format(now(),"%Y%m");

SET @sqlcmd1 = CONCAT('CREATE TABLE db_mail_message_',str_date,"(apptaskid varchar(100) NOT NULL,taskname varchar(200) NOT NULL,appsenduid varchar(200) NOT NULL,appreceiveuid varchar(100) NOT NULL,sendtime varchar(50) NOT NULL,handletime varchar(50) NOT NULL,url varchar(500) NOT NULL,remark varchar(500) NOT NULL,endtime varchar(50) NOT NULL,imapuid varchar(100) NOT NULL,addstatus int(1) NOT NULL DEFAULT '0',readstatus int(1) NOT NULL DEFAULT '0',deletestatus int(1) NOT NULL DEFAULT '0',completestatus int(1) NOT NULL DEFAULT '0',PRIMARY KEY (apptaskid),KEY db_imapuid_index (imapuid)) ENGINE=MyISAM DEFAULT CHARSET=utf8;");
PREPARE p1 FROM @sqlcmd1;
EXECUTE p1;
DEALLOCATE PREPARE p1;
END
-- 创建事件：每隔一秒自动调用存储过程
CREATE EVENT IF NOT EXISTS e_test
ON SCHEDULE EVERY 1 SECOND
ON COMPLETION PRESERVE
DO CALL test();
-- 创建事件：每月1号1点自动调用存储过程(未测试)
CREATE EVENT if not exists e_test on schedule every 1 MONTH STARTS DATE_ADD(DATE_ADD(DATE_SUB(CURDATE(),INTERVAL DAY(CURDATE())-1 DAY), INTERVAL 1 MONTH),INTERVAL 1 HOUR) on completion preserve do call test();
-- 开启事件
alter event e_test ON COMPLETION PRESERVE ENABLE;
-- 关闭事件
alter event e_test ON COMPLETION PRESERVE DISABLE;