自动删除MySQL数据库中的消息

Hi I have a MySQL database table where a user inputs messages (via a form) and I was wondering if there is a way to automatically delete a message after say 1 minute has passed? The code i'm using is PHP. Thanks very, very much for any replies :)

Use a cron job to purge old messages.

Well, you could always run a delete query before you do anything else. For example whenever you check for messages, first delete all messages older than 1 minute.

I think I would rather just not get the messages older than 1 minute though. It can be nice with a log :)

When a message is inserted to the DB, store a time_created timestamp. Then in your PHP, you only display messages whose timestamp falls within 1 minute of the current time.

I can think of two ways to achieve that.

  1. Make a script to run every X minutes and make any changes in your db.
  2. Delete all expired records before or after inserting new records in your script.
  • You could make a cron script to run every minute and delete all old messages
  • or make a valid_until column in your table, and set it to NOW()+60 - then only show rows which have valid_until >= NOW().

Building a cron service is the first thing that pops into my mind, although is probably an unnecessary complication.

You can call the delete in the same script that does the insert after a sleep of 1 minute.

 sleep ($seconds);
 // call the delete query

Another way is to pass the delay logic to a Mysql trigger that will do the delete for you.

SELECT SLEEP(<seconds>);

thanks for all the replies/help :) the first suggestion by Svish 'You could always run a delete query before you do anything else. For example whenever you check for messages, first delete all messages older than 1 minute.' will work...so obvious don't know why i didn't think of that doh! Thanks again every1.

Solution 1: user cron job which runs every minute to check and delete

Solution 2: use ajax do that job

I personally suggest the first solution but if u have no SSH access to the server u have to choose the second one :D