猫窝私语 — Makumo's Blog

玛酷猫的温馨小窝,记录生活点点滴滴。

@玛酷猫7年前

03/12
14:12
数据库

mysql定时任务以及乱码处理

前几天接到一个工作需求,在OA中增加一个功能,每天给当天过生日的同事发送一封生日快乐的邮件,并发送站内短信给所有人,提醒今天某某过生日。这个需求不错,很能体现人文关怀,如是乎构思如何实现。

先说下背景情况,OA是完全自行开发的,CI框架,WIMP架构(最初建设的时候由于各种原因,等接手的时候已经是这种别扭的架构)。最一开始准备用windows的任务计划。制作一个单页实现功能,在定时任务里面设置执行一个vbs文件,访问下该文件。内容类似如下

DIM IE
SET IE = CREATEOBJECT("INTERNETEXPLORER.APPLICATION")
IE.NAVIGATE("网址")
IE.VISIBLE=0
SET IE = NOTHING

后来考虑这一系列都是数据路操作,没必要绕一大圈,又写php程序又在windows下面建计划任务,mysql已经支持计划任务。于是乎就准备在mysql里面建一个存储过程一个计划事件搞定它。定时任务需要mysql版本在5.1以上,版本不够的话可以用上面的方式。

MYSQL的存储过程还是第一次写,以前只在MSSQL里面写过存储过程和触发器。不停地查找写法资料才完成如下代码(初次写难免存在问题,欢迎指正),

#这个是最原始版本,实际应用版本做了修改优化#
DROP PROCEDURE IF EXISTS `pro_birth`;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `pro_birth`()
BEGIN
	DECLARE t_uid INT;
	DECLARE a_uid INT;
	DECLARE t_username VARCHAR(255);
	DECLARE t_depname VARCHAR(255);
	DECLARE done INT DEFAULT 0;

	DECLARE cur1 CURSOR FOR 当日过生日人员名单SQL;
	DECLARE cur2 CURSOR FOR 全员名单SQL;
	DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

	OPEN cur1;
	REPEAT
	FETCH cur1 INTO t_uid,t_username,t_depname;
	IF NOT done THEN

		#生日邮件
		SET @TEMP_STRING = CONCAT("'<div style=font-size:14px><p>亲爱的",t_username,":</p><p>  祝您生日快乐!</p><p>  感谢您为公司发展付出的辛勤劳动和努力。在您生日到来之际,公司代表全体员工送上衷心的祝福:生日快乐!愿所有的快乐、所有的幸福、所有的温馨、所有的好运都永远围绕在你的身边。</p><p style=text-align:right>",DATE_FORMAT(CURDATE(),'%Y年%m月%d日'),"</p></div>' ");
		SET @MAIL_STRING = "插入邮件表SQL";
		PREPARE mail_stmt FROM @MAIL_STRING;
		EXECUTE mail_stmt;

		#全员站内短信提醒
		OPEN cur2;
		REPEAT
		FETCH cur2 INTO a_uid;
		IF NOT done THEN
			SET @TEMP_STRING = CONCAT("亲爱的伙伴:今天(",DATE_FORMAT(CURDATE(),'%m月%d日'),")是",t_depname," ",t_username,"的生日,快去给TA送祝福吧!");
			SET @PM_STRING = "插入短信表SQL"
			PREPARE pm_stmt FROM @PM_STRING;
			EXECUTE pm_stmt;
		END IF;
		UNTIL done END REPEAT;
		CLOSE cur2;

	END IF;
	UNTIL done END REPEAT;
	CLOSE cur1;
END
;;
DELIMITER ;

本机测试正常,在服务器上创建存储过程的时候却报错。“#1271 – Illegal mix of collations for operation ‘concat’”,谷歌了一下,都说是编码问题导致。检查了下服务器上牵扯到得几个表,用户表、邮件表、短信息表都是utf8的编码。心想是不是存储过程中间的中文导致报错,于是重新改写了下代码,新建一个临时表,将存储过程中的文字存放在库中,读取出来替换掉中间的关键字。代码类似,就不在重复贴了。服务器重新创建存储过程的时候执行成功,但是运行存储过程后,插入邮件表和短信息表的内容还是乱码,中文部分全都是用?代替。看来还是没有解决中文乱码的问题。

多番网上查找后,有位同仁也遇到此类问题,他的表和库都是utf8的编码,同样存储过程运行后中文都是??,这时候我才发现我这服务器上库的编码居然是latin1_swedish_ci,存储过程肯定也是这个编码,虽然表的数据都是utf8的,经过存储过程一弄,就全变??了。解决办法其实也很简单,变量声明的时候定一下编码格式。

DECLARE t_username VARCHAR(255) CHARACTER SET utf8;
DECLARE t_depname VARCHAR(255) CHARACTER SET utf8;

这个也不是彻底的解决办法,最终需要统一库的编码格式。

定时事件就简单多了,新建一个事件,每天凌晨6点运行下就好了。要核对下mysql的系统时间,像我的是+8区就不用调整了,如果默认的话需要对应做个加减。如下

DROP EVENT IF EXISTS `EVENT_BIRTH`;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` EVENT `EVENT_BIRTH` ON SCHEDULE EVERY 1 DAY STARTS '2012-03-08 6:00:00' ON COMPLETION NOT PRESERVE ENABLE DO CALL pro_birth()
;;
DELIMITER ;

最后,最关键一点,开启mysql的定时事件。我就是忘开启了,结果等了一天没动静。。。今天的手动执行了下存储过程。

set global event_scheduler = 1

mysql定时任务以及乱码处理