字段名 类型
eventTime datetime
eventStat smallint
数据例子如下
eventTime eventStat
------------------- -----------
2010-02-25 20:30:00 0
2010-02-25 21:00:00 0
2010-02-25 21:30:00 0
现在该如何写一个mySql的事件,这个事件要做到在每行记录的eventTime时刻前5分钟,将该记录的eventStat更新为1.
就是说在 2010-02-25 20:25:00 ,将第1条记录的eventStat值改为1.
目前我只会
CREATE EVENT test
ON SCHEDULE
EVERY 1 SECOND
DO
BEGIN
......
END
省略号里面的内容不会写,求MYSQL达人帮忙,谢谢!!!
3 个解决方案
#1
我自己好像已经解决了,给这个表tableName 增加一个字段field1,如下
CREATE EVENT test
ON SCHEDULE
EVERY 1 SECOND
DO
BEGIN
select @s := field1 from tableName where eventTime = (now() + INTERVAL 5 minute);
update tableName set eventStat = 1 where field1= @s;
END
CREATE EVENT test
ON SCHEDULE
EVERY 1 SECOND
DO
BEGIN
select @s := field1 from tableName where eventTime = (now() + INTERVAL 5 minute);
update tableName set eventStat = 1 where field1= @s;
END
#2
Congratulations~~ 谢谢分享!
#3
解决就好。 .
#1
我自己好像已经解决了,给这个表tableName 增加一个字段field1,如下
CREATE EVENT test
ON SCHEDULE
EVERY 1 SECOND
DO
BEGIN
select @s := field1 from tableName where eventTime = (now() + INTERVAL 5 minute);
update tableName set eventStat = 1 where field1= @s;
END
CREATE EVENT test
ON SCHEDULE
EVERY 1 SECOND
DO
BEGIN
select @s := field1 from tableName where eventTime = (now() + INTERVAL 5 minute);
update tableName set eventStat = 1 where field1= @s;
END
#2
Congratulations~~ 谢谢分享!
#3
解决就好。 .