MySQL-闲置X分钟后更改字段值 [英] MySQL - Change field value after X minutes of inactivity

问题描述

我正在尝试找到一种cronjob的方法，该方法可以检查自上次更新行以来经过了多长时间，如果它大于X，则将字段"ACTIVE"更改为"FALSE".

I am trying to find a way to have a cronjob which checks how long it has passed since the last update of a row, if it is more than X, then change field 'ACTIVE' to 'FALSE'.

我有一个可以更新其当前IP并添加时间戳的机器人.

I have a robot which updates its current IP and add the timestamp.

从另一面看，可能会有一个用户愿意向机器人发送命令.我的想法是能够分辨出机器人在最近的X秒钟内是否处于活动状态，因此尝试向其发送命令很有意义.

From the other side, there might be a User willing to send commands to the robot. My idea is to be able to tell whether the robot has been active in the last X seconds and therefore it makes sense trying to send a command to it.

所以，我猜想某种脚本会检查current_time()-field_time> = X，然后changeValue('ACTIVE')<-否.

So, I guess some sort of script which checks if current_time() - field_time >= X then changeValue('ACTIVE')<- False.

是否可以每隔X秒直接在数据库上完成此操作?还是在PHP中使用脚本来解决此问题的更好方法? (无限循环的脚本)

Can this be done directly on the DB every X seconds? Or perhaps using a script in PHP a better way to handle this problem? (a script which loops indefinitely)

推荐答案

尝试使用MySQL

Try doing this with MySQL scheduling:

  DELIMITER $$
  CREATE EVENT deactivation
    ON SCHEDULE EVERY 10 MINUTE STARTS CURRENT_TIMESTAMP
    DO
      BEGIN
        UPDATE tbl SET tbl.active = FALSE
           WHERE tbl.active = TRUE AND 
           ( TIME_TO_SEC( TIMEDIFF (NOW(),tbl.updated) ) / 60 ) > 10;
      END;
  $$;

其中tbl.updated是您的时间戳(使用php生成).由于我的测试箱无法访问atm， 我不确定此查询是否正确，但通常应该执行此工作.

Where tbl.updated is your timestamp (generated with php). As my testbox is unreachable atm, Im not sure if this query is correct, but generally, it should do the job.

这篇关于MySQL-闲置X分钟后更改字段值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！