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 ;
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
  • 12.
  • 13.
  • 14.
  • 15.
  • 16.
  • 17.
  • 18.
  • 19.
  • 20.
  • 21.
  • 22.
  • 23.
  • 24.
  • 25.
  • 26.
  • 27.
  • 28.
  • 29.
  • 30.
  • 31.
  • 32.
  • 33.
  • 34.
  • 35.
  • 36.
  • 37.
  • 38.
  • 39.
  • 40.
  • 41.
  • 42.
  • 43.
  • 44.
  • 45.
  • 46.
  • 47.
  • 48.
  • 49.
  • 50.
  • 51.
  • 52.
  • 53.
  • 54.
  • 55.
  • 56.
  • 57.
  • 58.
  • 59.
  • 60.
  • 61.
  • 62.
  • 63.
  • 64.
  • 65.
  • 66.
  • 67.
  • 68.
  • 69.
  • 70.
  • 71.
  • 72.
  • 73.
  • 74.
  • 75.
  • 76.
  • 77.
  • 78.
  • 79.
  • 80.
  • 81.
  • 82.
  • 83.
  • 84.
  • 85.
  • 86.
  • 87.
  • 88.
  • 89.
  • 90.
  • 91.
  • 92.
  • 93.

#–每月创建一个当月表的定时任务:

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();
  • 1.

**常识提示:**在计划任务中,某个具体的时间来调用存储过程SQL1语句执行。这个指定的具体时间必须是将来的时间才可以的

#查看创建存储过程的SQL语句:

show create  PROCEDURE create_table_by_month\G
  • 1.

#删除存储过程:

DROP PROCEDURE IF EXISTS create_table_by_month;
  • 1.

#删除计划任务

drop event create_table_by_month1;
  • 1.

以上存储过程的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.
  • 1.
  • 2.
  • 3.

上述的存储过程只是本博主线上业务环境的SQL,切勿直接套用,造成损失与本博文无关。鉴于学习交流初心发表此处,仅供大家学习交流参考用。

posted @ 2020-09-06 13:23  勤奋的蓝猫  阅读(3)  评论(0编辑  收藏  举报  来源