如何根据特定字段的值在一定时间后删除记录？
我有一个名为users的表。该表中的两个字段是“activation”和“regdate”。两者都是 VARCHAR。如果用户已激活帐户，则激活字段包含字符串“active”。如果用户尚未激活帐户，则该字段包含一个字符串，该字符串是随机的字母数字激活密钥。 regdate 字段包含用户注册帐户的日期和时间，由 date("mdy H:m:s"); 填充。
我需要做的是创建一个 PHP 脚本，它将删除激活字段中不等于“活动”且早于我指定时间的所有记录。我知道如何通过匹配条件来定位记录，例如选择激活等于“活动”的记录，但我需要它做相反的事情。我需要它来匹配激活不等于“活动”的所有记录（因为随机字符串不可能匹配，不是吗？）。
当然，我还必须根据这些记录的“regdate”字段将所选记录与当前时间进行比较。
所以，简而言之，我有一堆记录，我想删除所有早于一定时间并且有一个不等于“活动”字段的记录。
如果我没有很好地解释这一点，我深表歉意。我真的很累，这也是我在 stackoverflow 上的第一篇文章。
I've got a table called users. Two of the fields in that table are "activation" and "regdate". Both are VARCHAR. The activation field contains the string "active" if the user has activated the account. If the user has not activated the account, the field contains a string which is a randomized alphanumerical activation key. The regdate field contains the date and time that the user registered the account, populated by date("m.d.y H:m:s");.
What I need to do is create a PHP script which will delete all records that do NOT equal "active" in the activation field and are older than an amount of time that I specify. I know how to locate records by matching criteria such as selecting records where activation equals "active", but I would need it to do the opposite. I would need it to match all records where activation does not equal "active" (because the randomized string would be impossible to match, would it not?).
Of course, I would then have to also compare the selected records to the current time based on those records' "regdate" field.
So, in short, I've got a bunch of records and I want to delete all of the ones that are older than a certain amount of time and have a field that does NOT equal "active".
I apologize if I did not explain this well enough. I'm really tired, and this is also my first post on stackoverflow.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
如果您将
regdate字段转换为DATETIME类型，则查询可以非常简单：DELETE FROM users WHEREactivation <> '活动' AND regdate < '2011-01-01'否则，根据 regdate VARCHAR 的格式，您必须将其
CAST或CONVERT转换为DATETIME在您的WHERE子句中进行比较之前。这些信息足以让您正常工作吗？
If you convert your
regdatefield into aDATETIMEtype, the query can be as simple as:DELETE FROM users WHERE activation <> 'active' AND regdate < '2011-01-01'Otherwise, depending on the format of your regdate VARCHAR,you'llhave to
CASTorCONVERTit to aDATETIMEbefore comparing it in yourWHEREclause.Is this enough infoforyou togetthisworking?
使用 mysqladmin 将数据库中的字段更改为“datetime”应该是一件轻而易举的事（数据看起来像这样“2011-09-13 02:08:20”）然后你可以使用标准 mysql 命令，例如：
这是删除任何内容在我的“iplog”表中已存在超过 2 天且不活动。
Change your field in the database to "datetime" using mysqladmin should be a breeze ( the data would look something like this "2011-09-13 02:08:20") then you could use standard mysql commands like:
this is deleting anything older than 2 days in my 'iplog' table and not active.