表结构:
id status Createtime
----------------------------
1 1 2009-11-11 11:10:00
....
createtime是timestamp类型判断createtime距离当前时间是不是超过一小时,如果超过一小时,则把status值改为2,这个作业应该怎么写?谢谢!!!!
id status Createtime
----------------------------
1 1 2009-11-11 11:10:00
....
createtime是timestamp类型判断createtime距离当前时间是不是超过一小时,如果超过一小时,则把status值改为2,这个作业应该怎么写?谢谢!!!!
mysql> show variables like '%sche%';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| event_scheduler | OFF |
+-----------------+-------+
1 row in set (0.01 sec)启动event功能:
mysql> set global event_scheduler =1;
Query OK, 0 rows affected (0.00 sec)确定起用event功能后,执行下面的建立event:
delimiter //
create EVENT my_stat_event
ON SCHEDULE
EVERY 1 HOUR
STARTS CURRENT_TIMESTAMP
on completion preserve ENABLE
do
begin
update tb_name set status=2 where Createtime<date_sub(CURRENT_TIMESTAMP(),interval 1 hour);
end;
//
delimiter ;执行上面的建立作业后,查看作业的信息:
mysql> select * from information_schema.events where event_name='my_stat_event';