猫窝私语 — Makumo's Blog

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

@玛酷猫13 年前

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定时任务以及乱码处理