MySQL定时调度器的使用（先定义一个函数（函数内含多条更新语句），再定义一个event事件调用该函数）

推荐相关博文：mysql的定时调度器（内含多条与定时调度器相关的常用查询语句以及使用例子）

一、想实现的功能：

1. 我想每隔5分钟执行一次event事件，事件的功能是每隔5分钟更新某张表的状态字段。（比如：系统执行一次event事件，当检查到距离开始时间这个字段还有半小时，会自动更新状态字段，把“未开始”替换成“即将开始”状态）
2. 查了百度，比较好的方法是创建一个函数，再创一个event事件。
3. 函数里含多条我们写的update语句，且该函数名称记为update_state()。
4. event事件就专门用call update_state()运行该函数即可。

二、使用步骤：

1. 先在my.ini配置文件中设置永久开启时间控制器event_scheduler = ON并重启mysql。（配置步骤详见：MySQL正确配置my.ini的event_scheduler = ON）
2. 创建一个函数，名为update_state，创建步骤如下：

↑↑↑如果上面设置了要传入的参数，但是等下要写的函数体没有出现该参数，保存时会报1064错误，错误信息说第一行出错，但我们的代码并没有写错，它在提醒你注意补充之前你设置的参数而已，错误提示信息如下：

1064 - You have an error in your SQLsyntax;check the manual that corresponds to your MySQL server version for the right syntax to use near ')

下一步紧接着：
点击完成后，需要我们定义函数体，我们把三条update语句依次写入：

BEGIN
	#将距离开始时间还有半个小时（1800s）的记录状态从0改为1
	UPDATE `schedule` SET s_state='1' WHERE timestampdiff(SECOND,current_timestamp,s_starttime) BETWEEN 0 AND 1800 AND s_state='0';
	#将位于开始时间和结束时间之间且状态为1的记录改变其状态为2
	UPDATE `schedule` SET s_state='2' WHERE timestampdiff(SECOND,current_timestamp,s_starttime) <0 AND timestampdiff(SECOND,current_timestamp,s_endtime) >0 AND s_state='1';
	#将已经过了结束时间的记录从状态2改为3
	UPDATE `schedule` SET s_state='3' WHERE timestampdiff(SECOND,current_timestamp,s_endtime) <0 AND s_state='2';
	RETURN 0;
END

可以看到↓下方参数：里没有要传进来的参数，默认不设参，对应之前设参那一步向导。

直接ctrl+s保存，此时弹出一个框，我们把该函数的名称写进去即可（虽然它叫过程名，但其实就是该函数的函数名称而已）：

试着手动运行一次该函数↓，正确运行，表中的状态字段随条件正确更新了：

• 你还可以用select关键字select 某函数()来调用一个函数，不可以用call关键字调用一个函数，会报错，call是用来调用存储过程的。
• 以后我们会配合event定时事件，每5分钟运行一遍该函数，让系统自动帮我们实时更改表上的状态字段，以此完成我们最终的目标。

接下来开始创建事件：

ctrl+s保存，输入事件名↓：

执行SELECT * FROM mysql.event;查看最近一次事件发生是在什么时候，发现最近一次发生在凌晨6点（其实当前是下午2点，差了8小时，我们心算+8即可，无大碍）↓：

过了5分钟后查看，最近一次发生在6点05分（即现实中14点05分），正确！！！

你可以设置PRESERVE保存每个执行完毕的事件，不要让他废除后自动删掉，保留来看，毕竟辛辛苦苦写的娃子：

数据库表中需要自动更改的状态字段也更新了，教程结束！！！

三、拓展：

问： 如果我们想让上述创建的事件只在白天开启，晚上就关闭，减少频繁的执行次数，如何做？

答： 再创个新事件，利用下面↓两句开启/关闭事件的语句，每到白天7点多就执行第一句，到晚上19点多就执行第二句即可。

alter event update_state_event on completion preserve enable;
alter event update_state_event on completion preserve disable;

• 为了方便直接在定时事件的事件体上写代码，如下：

这是个名为on_off_event的事件，是专门用来定时开启和关闭update_state_event事件的事件，其事件体写法如下↓：

# 事件体：
BEGIN
IF MONTH(current_timestamp) NOT IN (1,2,7,8) THEN
	IF HOUR(current_timestamp)BETWEEN 7 and 18 THEN
		alter event update_state_event on completion preserve enable; 
	ELSE
    alter event update_state_event on completion preserve disable; 
	END IF;
END IF;
END

下面↓图中的代码没写完，完整版已经贴在上上面↑了。

更多mysql时间函数见这篇博文：mysql中时间比较的实现

四、补充：

可以配合这句↓查询语句来查看每条记录的开始、结束时间与当前时间的关系：

SELECT current_timestamp,s_starttime,timestampdiff(SECOND,current_timestamp,s_starttime) as starttime_diff,timestampdiff(SECOND,current_timestamp,s_endtime) as endtime_diff from `schedule`;

执行下面这行语句能够查出与之执行的最近一条增/删/改的受影响行数。（不包括查，查有另外一句）

SELECT ROW_COUNT();

使用演示：

同时选中某条update语句和SELECT ROW_COUNT();语句，点击运行已选择的按钮，得出以下结果1，说明有一行记录受影响：

注意单独先执行一条update语句再单独执行SELECT ROW_COUNT();语句是不会返回上述正确结果1的，只能同时选中这两句同时执行才有用。

你还可以直接在存储过程或函数体中使用ROW_COUNT()函数，比如我把ROW_COUNT()放到函数体中配合if 和 end if判断最近一句update返回的受影响行数，然后同理获取第二句update返回的受影响行数，第三句同理，然后函数体返回一个返回值，该返回值的内容是：“第一句update返回了x行受影响的行数，第二句…，第三句…”。

//if语句在函数体中的用法
IF search_condition THEN   
    statement_list    
[ELSEIF search_condition THEN]    
    statement_list ...    
[ELSE   
    statement_list]    
END IF 

// 例子：利用if语句配合ROW_COUNT()>0进行判断
IF ROW_COUNT()>0 THEN   
    statement_list      
END IF 

推荐相关博文：SQL返回受影响行数
自己动手试试能不能实现该功能，博主偷懒就不实现了，下面给你们演示在函数体中使用ROW_COUNT()统计函数体执行后所有受影响的行数值n，并返回值n，补充update_state()函数体，如下↓（注意我把每个update的判断条件改成<>不等于了，这样比较完善）：

BEGIN
	#定义变量n，类型为INTEGER，默认值为0
	declare n INTEGER DEFAULT 0;
	#将距离开始时间还有半个小时（1800s）的记录状态改为1
	UPDATE `schedule` SET s_state='1' WHERE timestampdiff(SECOND,current_timestamp,s_starttime) BETWEEN 0 AND 1800 AND s_state <> '1';
	set n=ROW_COUNT()+n;
	#将位于开始时间和结束时间之间的记录改变其状态为2
	UPDATE `schedule` SET s_state='2' WHERE timestampdiff(SECOND,current_timestamp,s_starttime) <0 AND timestampdiff(SECOND,current_timestamp,s_endtime) >0 AND s_state <> '2';
	set n=ROW_COUNT()+n;
	#将已经过了结束时间的记录状态改为3
	UPDATE `schedule` SET s_state='3' WHERE timestampdiff(SECOND,current_timestamp,s_endtime) <0 AND s_state <> '3';
	set n=ROW_COUNT()+n;
	RETURN n;
END

运行前s_state状态字段有三条记录，三条记录的状态分别为0、1、2↓：

点击运行按钮，可以看到update_state()函数返回的结果值为3↓，说明有3条记录的状态值受影响（更新）了↓：

运行后s_state状态字段的三条记录均受到影响，状态值都改变了，分别为1、2、3↓：

五、常用代码备份：

使用下面查询语句可查看已经创建好的event事件：

// 查看mysql中所有已创建定时事件event
SELECT * FROM mysql.event; 
或
SHOW EVENTS;

// 开启event_scheduler sql指令：
SET GLOBAL event_scheduler = ON;

// 相反，关闭event_scheduler指令：
SET GLOBAL event_scheduler = OFF;

// 关闭事件任务: 
alter event 事件名称 ON COMPLETION PRESERVE DISABLE;
// 开户事件任务: 
alter event 事件名称 ON COMPLETION PRESERVE ENABLE;

// 查看mysql系统时区：
show variables like '%time_zone%';
// 查看当前mysql系统时间：
select now();

// 查看mysql一些基础配置信息，通常用来查看event_scheduler 是否开启
select @@global.sql_mode;
show variables like "sql_mode";
show variables like '%sche%';
//更多语句请百度

六、执行SELECT * FROM mysql.event; 查出的event字段中，last_executed、starts、ends字段为什么显示的时间与我们设定的少8个小时？

解答：

1. 用SHOW EVENTS查询出来的字段中时间又是正确的，用SELECT * FROM mysql.event;就不行，我以为我时区错了，想去my.ini中添加default-time-zone = '+8:00'来着，后来发现根本没必要，直接忽略就行了，又不影响执行结果，而且用select now();查当前系统时间都是正确的，并没出现少8小时情况。
2. 显示少8小时就少8小时，只是看起来少8小时，实际并没影响其它时间。