使用phpMyAdmin自动删除超过5秒的SQL记录

Question

So I'm using WampServer with the default phpMyAdmin to store this SQL table called Typing.

Table: Typing

Now I want to set the typing column to 0 for any row that has set the typing column to 1 more than five seconds ago.

For ex. I just set the typing column to 1 for the first row and my database detects the time since this 1 has been written, then it sets a 5 second timer to revert that 1 back to a 0. If 1 is overwritten with another 1 during that time, that timer should rest.

How should I go about this? Should I have a column for a 'timestamp' of each record? How do I make my database constantly check for entries older than 5 seconds without user input? Do I need an always on PHP script or a database trigger and how would I go about that?

Answer 1

As @JimL suggested, it might be a bit too ambitious to purge the records after only five seconds.

It might be helpful to have more information about what you're trying to accomplish, but I'll answer in a generic way that should answer your question

How I would handle this is that any queries should check for records that are less than five seconds old (I assume you're querying the data and only want records that are less than five seconds, otherwise I'm not really following the point of your question).

Once a day, or hourly if you have that much data, you can run a scheduled job (scheduled through MySQL itself, not through cron/Windows Scheduled Tasks) to purge the old records. You can use phpMyAdmin to set that up (the "Events" tab), although it's actually a MySQL feature that doesn't require phpMyAdmin.