MySQL之存储过程按月创建表的方法步骤
作者:wjw555 时间:2024-01-25 02:40:40
具体不多说,直接上代码。欢迎一起交流和学习。
创建一个按月创建表的存储过程,SQL语句如下:
DELIMITER //
DROP PROCEDURE IF EXISTS create_table_by_month //
CREATE PROCEDURE `create_table_by_month`()
BEGIN
#--提前申明变量,后面会用到
DECLARE nextMonth varchar(20);
DECLARE nextTABLE varchar(20);
DECLARE csql varchar(5210);
DECLARE outputParam int;
DECLARE tableName_1 varchar(20);
DECLARE tableName_2 varchar(20);
DECLARE table_prefix varchar(20);
#--获取下个月
SELECT SUBSTR(replace(DATE_ADD(CURDATE(), INTERVAL 1 MONTH), '-', ''), 1, 6) INTO @nextMonth;
#--对上面的SQL语句拆分分析:
#MySQL时间函数date_add()为日期增加一个时间间隔,此sql的意思就是当前日期增加一个月后的日期
#select DATE_ADD(CURDATE(), INTERVAL 1 MONTH);
##返回20201006
#select replace('2020-10-06', '-', '');
##返回20201006
#select replace(DATE_ADD(CURDATE(), INTERVAL 1 MONTH), '-', '');
##返回202010
#SELECT SUBSTR(20201006,1,6);
##赋值到变量@nextMonth 中
#SELECT SUBSTR(20201006,1,6) INTO @nextMonth;
# 表1
#设置表前缀变量值为td_user_banks_log_
set @table_prefix = 'td_user_banks_log_';
#定义表1的名称
SET @tableName_1 = CONCAT(@table_prefix, @nextMonth);
##定义创建表的SQL语句
set @csql=concat("create table if not exists ",@tableName_1,"(
`id` int(11) NOT NULL AUTO_INCREMENT,
`app_id` varchar(100) NOT NULL,
`user_id` int(11) NOT NULL DEFAULT '0' COMMENT '用户ID',
`type` tinyint(2) NOT NULL DEFAULT '1' COMMENT '1 签到奖励 2 活动奖励 3 兑换',
`gold_coin` int(11) NOT NULL,
`remarks` varchar(200) NOT NULL DEFAULT '' COMMENT '备注',
`create_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `app_id` (`app_id`),
KEY `user_id` (`user_id`),
KEY `type` (`type`),
KEY `create_at` (`create_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;");
#PREPARE介绍:
##语句用于预备一个语句,并指定名称statement_name,以后引用该语句。语句名称对大小写不敏感。
##preparable_stmt可以是一个文字字符串,也可以是一个包含了语句文本的用户变量。该文本必须表现为一个单一的SQL语句,而不是多个语句。
PREPARE create_stmt from @csql;
EXECUTE create_stmt;
DEALLOCATE PREPARE create_stmt;
# 表2 和表1是同样的SQL,此处不做介绍:
set @table_prefix = 'td_sign_log_';
SET @tableName_2 = CONCAT(@table_prefix, @nextMonth);
set @csql=concat("create table if not exists ",@tableName_2,"(
`id` int(11) NOT NULL AUTO_INCREMENT,
`app_id` varchar(100) NOT NULL,
`user_id` int(11) NOT NULL,
`day` int(11) NOT NULL DEFAULT '0' COMMENT '天',
`sign_type` tinyint(2) NOT NULL DEFAULT '1' COMMENT '1 签到 2 补签 3 累计奖励',
`type` tinyint(2) NOT NULL DEFAULT '1' COMMENT '1 金币',
`num` int(11) NOT NULL DEFAULT '0',
`data_id` int(11) NOT NULL DEFAULT '0',
`create_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `app_id` (`app_id`),
KEY `user_id` (`user_id`),
KEY `type` (`type`),
KEY `data_id` (`data_id`),
KEY `create_at` (`create_at`),
KEY `sign_type` (`sign_type`),
KEY `day` (`day`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;");
PREPARE create_stmt from @csql;
EXECUTE create_stmt;
DEALLOCATE PREPARE create_stmt;
SELECT COUNT(1) INTO @outputParam FROM information_schema.`TABLES` WHERE TABLE_NAME in (@tableName_1, @tableName_2);
SELECT @outputParam;
END //
delimiter ;
#--每月创建一个当月表的定时任务:
create EVENT `create_table_by_month` ON SCHEDULE EVERY 1 MONTH STARTS '2020-09-06 12:40:00' ON COMPLETION NOT PRESERVE ENABLE DO call create_table_by_month();
常识提示:在计划任务中,某个具体的时间来调用存储过程SQL1语句执行。这个指定的具体时间必须是将来的时间才可以的
#查看创建存储过程的SQL语句:
show create PROCEDURE create_table_by_month\G
#删除存储过程:
DROP PROCEDURE IF EXISTS create_table_by_month;
#删除计划任务
drop event create_table_by_month1;
以上存储过程的SQL语句和定时器的SQL语句都是生产环境使用过的
#排查报错:
发现mysql的日志报错,原来的存储过程的SQL语句里面增加的索引键字段正好是表中不存在的字段,所以包下面的错误。于是去掉存储过程中的索引键字段SQL就OK了。
2020-09-06T12:40:00.244879+08:00 604392 [ERROR] Event Scheduler: [root@localhost][test0001.create_table_by_month1] Key column 'sign_date' doesn't exist in table
2020-09-06T12:40:00.244926+08:00 604392 [Note] Event Scheduler: [root@localhost].[test0001.create_table_by_month1] event execution failed.
上述的存储过程只是本博主线上业务环境的SQL,切勿直接套用,造成损失与本博文无关。鉴于学习交流初心发表此处,仅供大家学习交流参考用。
来源:https://blog.51cto.com/wujianwei/2529168
标签:MySQL,存储过程,按月创建表
0
投稿
猜你喜欢
解决python中的幂函数、指数函数问题
2021-12-03 01:26:00
Python实现学生信息管理系统的示例代码
2022-12-06 22:17:56
详解使用vue-router进行页面切换时滚动条位置与滚动监听事件
2024-05-08 09:34:39
Python logging模块原理解析及应用
2022-04-08 11:14:32
ASP实现语音分时问候
2007-10-02 12:12:00
网页中插入视频播放代码全集
2007-10-22 17:48:00
ASP读取日期单日期自动补零函数代码
2011-02-20 10:39:00
MySQL延时复制库方法详解
2024-01-26 14:21:51
python实现随机梯度下降法
2023-11-02 16:55:37
史上最全Python文件类型读写库大盘点
2021-01-28 22:08:26
CSS 的优先规则
2009-01-08 12:40:00
解决bootstrap中使用modal加载kindeditor时弹出层文本框不能输入的问题
2024-04-16 09:26:21
在Python中测试访问同一数据的竞争条件的方法
2023-03-29 15:04:06
查看vue-cli脚手架的版本号和vue真实版本号及详细操作命令
2023-07-02 17:04:20
DIV+CSS高度自适应网页代码实例
2008-09-20 08:00:00
常见的jQuery选择器汇总
2024-04-22 22:21:35
asp如何遍历目录及目录下的文件?
2009-11-11 17:55:00
使用Python完成15位18位身份证的互转功能
2021-11-30 03:04:30
浅析python 中__name__ = '__main__' 的作用
2022-07-21 16:32:28
ASP链接Mysql数据库 非DSN连接的方法
2009-03-09 18:24:00