MySQL学习(一):存储过程以及定时任务

---

存储过程

首先在navcat事件中创建存储过程,然后再开始代码编写

# IN表示传入参数,多个参数逗号隔开 格式: IN <name> <type>
CREATE DEFINER=`mysql`@`127.0.0.1` PROCEDURE `UPDATE_ONLINE_SUMMARY_BY_MONTH`(IN tableName varchar(100))
BEGIN

# DATE_FORMAT(date,format)函数 date:传入日期 format:输出格式
# 输出格式: %Y 年,4位,y小写俩位 %m 月,m大写是英文名 %d 日,d大写有英文后缀,例22nd
# NOW() 返回当前日期
SET @myYear = DATE_FORMAT(NOW(), '%Y');
SET @myMonth = DATE_FORMAT(NOW(), '%m');
# CONCAT() 字符串拼接函数,多个字符串用逗号隔开
SET @saveDate = CONCAT(@myYear,'_' , @myMonth - 1);
IF @myMonth > 6 THEN
	SET @delDate = CONCAT(@myYear, '_', @myMonth - 6);
ELSE
	SET @delDate = CONCAT(@myYear - 1, '_', @myMonth + 6);
END IF;

#删除六个月之前的表格
SET @deleteTableName = CONCAT('DROP TABLE IF EXISTS `' , tableName , '_', @delDate , '`;');
# PREPARE基本用法
# PREPARE stmt from '你的sql语句';
# EXECUTE stmt (如果sql有参数的话, USING xxx,xxx); // 这里USING的只能是会话变量
# 会话变量: 例: SET @变量=值
# DEALLOCATE PREPARE stmt;  // 释放连接
PREPARE stmt FROM @deleteTableName;
EXECUTE stmt;
 
#重命名上个月的表
SET @renameTableName = CONCAT('rename table ' , tableName , ' to ' , tableName, '_' , @saveDate , ';');
PREPARE stmt FROM @renameTableName;
EXECUTE stmt;
# sql的if elseif 语句格式
IF tableName = 'adm_chn_online_summary' THEN
	#创建新表
	SET @createStr = CONCAT("CREATE TABLE `adm_chn_online_summary` (
		`ID` bigint(20) NOT NULL AUTO_INCREMENT,
		`DEVICE_CODE` varchar(50) NOT NULL COMMENT '设备code',
		`CHANNEL_SEQ` int(10) NOT NULL COMMENT '通道号',
		`ONLINE_SECOND` bigint(20) NOT NULL COMMENT '在线时长,单位秒',
		`ONLINE_RATE` double(20,2) NOT NULL COMMENT '每日在线率，小数点保留二位',
		`DATE` datetime NOT NULL COMMENT '统计日期，用于分组',
		`SAVE_DATE` datetime NOT NULL COMMENT '数据插入时间，保留数据生成的时间点',
		`DESCRIBE_LIST` mediumtext COMMENT '用于前端展示数据，展示具体的时间点在线和离线状态',
		PRIMARY KEY (`ID`),
		KEY `INDEX_DEVICE_CODE` (`DEVICE_CODE`) USING BTREE,
		KEY `INDEX_DATE` (`DATE`) USING BTREE
	) ENGINE=InnoDB AUTO_INCREMENT=222357 DEFAULT CHARSET=utf8;");
	PREPARE stmt FROM @createStr;
	EXECUTE stmt;
ELSEIF tableName = 'adm_dev_online_summary' THEN
	#创建新表
	SET @createStr = CONCAT("CREATE TABLE `adm_dev_online_summary` (
		`ID` bigint(20) NOT NULL AUTO_INCREMENT,
		`DEVICE_CODE` varchar(50) CHARACTER SET utf8 NOT NULL COMMENT '设备编号',
		`ONLINE_SECOND` bigint(20) NOT NULL COMMENT '在线时长,单位秒',
		`ONLINE_RATE` double(20,2) NOT NULL COMMENT '每日在线率，小数点保留二位',
		`DATE` datetime NOT NULL COMMENT '统计日期，用于分组',
		`SAVE_DATE` datetime NOT NULL COMMENT '数据插入时间，保留数据生成的时间点',
		`DESCRIBE_LIST` mediumtext CHARACTER SET utf8 COMMENT '用于前端展示数据，展示具体的时间点在线和离线状态',
		PRIMARY KEY (`ID`)
	) ENGINE=InnoDB AUTO_INCREMENT=24382 DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='设备在线时长统计表';");
	PREPARE stmt FROM @createStr;
	EXECUTE stmt;
END IF;

DEALLOCATE PREPARE stmt;
END

定时任务

begin

call UPDATE_ONLINE_SUMMARY_BY_MONTH('adm_chn_online_summary');
call UPDATE_ONLINE_SUMMARY_BY_MONTH('adm_dev_online_summary');

end

然后在计划中添加执行时间