MySQL / PHP - 经过一定时间后自动更新列

Question

I've recently came across an issue. I want to create a game-like system in MySQL that would add to current_energy COLUMN of users TABLE a defined value regularly over time, for example add 5 to value every 5 minutes have passed.

Is it possible to be done through MySQL itself, or should I look for a way of updating it on, let's say, player login with PHP queries based on time(); comparation with last_login COLUMN?

Answer 1

Don't do it. You would be scheduling an event or job, just adding load onto your server.

Instead, create a view with the logic such as this:

create view v_table as
    select t.*,
           current_energy + floor(timestampdiff(minute, last_login, now()) / 5)
    from t;