MySQL之存储过程按月创建表的方法步骤

具体不多说，直接上代码。欢迎一起交流和学习。

创建一个按月创建表的存储过程，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 ;

#查看创建存储过程的SQL语句:show create PROCEDURE create_table_by_month\G

#排查报错：

发现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,切勿直接套用,造成损失与本博文无关。鉴于学习交流初心发表此处，仅供大家学习交流参考用。

更多相关文章

随机推荐