向数据库添加数据，一定时间后自动删除? [英] Add data to a database, automatically remove after a certain time?

问题描述

所以，我在这个话题上做了很多谷歌搜索，但我找不到答案.所以，基本上，我正在寻找一个小型网站，它将从 HTML 表单中提取信息，将其发送到数据库，然后在两个小时后，它会自动删除自己.我有一个关于如何工作的基本理论，但我不知道如何去做:我可以拉出当前时间和日期，加上两个小时，然后将时间放入过期"列中数据库.一旦时间是在 expires 列中的时间，数据将从数据库中删除.对不起，如果这是一个非常noobish"的问题，我对使用 PHP 的数据库仍然有点陌生.

So, I've done quite a bit of googling on this topic, and I just can't find an answer. So, basically, I'm looking to make a small website, that will pull information from a HTML form, send it to a database, then after two hours, it will automatically delete itself. I have a basic theory on how this could work, but I can't figure out how to do it: I could pull the current time and date, add two hours to that, then put the time into an "expires" column in the database. Once the time is the one that is in the expires column, the data will be removed from the database. Sorry if this is a very "noobish" question, I'm still a bit new to databases with PHP.

无论如何，任何帮助将不胜感激！谢谢！

Anyway, any help would be much appreciated! Thanks!

推荐答案

您可以在表中添加一个新的时间戳列，它会像这样自动添加创建行的时间戳

You could add a new timestamp column to your table which will automatically add the timestamp of when the row was created like so

CREATE TABLE t1 (
   #your existing columns defined as before + this new column
   ts_created TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

现在每次在这个表上创建一行，MySQL 都会在创建时完成所有记录工作.

Now every time you create a row on this table, MySQL does all the work of recording when it was created.

假设您可能无法在主机上创建 cron 作业，您可以在现有站点代码中最明显的位置添加删除代码以进行删除.

Assuming you may not be able to create a cron job on your host you could then add the deletion code in the most obvious place in your existing site code to do the removal.

// remove old stale data
$sql = "DELETE FROM user
        WHERE ts_created < DATE_ADD(NOW(),INTERVAL -2 HOUR)";

if ( ! $mysqli->query($sql) ) {
    // log $mysqli->error somewhere
}

虽然 cron 作业乍一看似乎是个好主意，但为了确保此表上的内容始终准确，您必须每 30 秒或更频繁地运行一次.这会妨碍该表上的其他活动，如果站点很忙，这可能是一个问题，如果它不忙，您将在大多数时间不必要地运行 cron.

ALthough a cron job seems a good idea at first sight, in order to make sure things are always accurate on this table you would have to run it every 30 seconds or maybe even more often. That would get in the way of other activities on this table, if the site was busy that could be a problem, if it was not busy you would just be running the cron unnecessarily most of the time.

如果您在向用户提供此信息之前添加删除代码，至少它只会在需要时运行，并且您还可以确保在向用户提供数据时表格始终准确.

If you add the deletion code just before you present this information to the user at least it would only be run when required and you would also ensure that the table was always accurate at the time the data was presented to the user.

这篇关于向数据库添加数据，一定时间后自动删除?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！