一个不工作的 mysql 删除查询。为什么？

发布于 2024-11-15 18:06:52 字数 376 浏览 4 评论 0原文

我有一个包含 3 行的表，其中一行包含唯一的时间代码（例如：1308162911）。这些记录有很多，但我想删除所有大于一天（又称 86400 秒）的记录。我有这个查询，但它不起作用（什么也没有发生）：

$db = mysql_connect($hostname, $db_user, $db_password);
mysql_select_db($database,$db)or die( "Unable to select database");

$now = time() - 86400;
$delete = ("DELETE FROM $tbl WHERE time > '$now'");

原文

I have a table with 3 rows and one of those contains a unique time code (ex: 1308162911). There are a lot of these records but I want to delete all records which are bigger than one day (AKA 86400 seconds). I have this query but it doesn't work (nothing happens):

$db = mysql_connect($hostname, $db_user, $db_password);
mysql_select_db($database,$db)or die( "Unable to select database");

$now = time() - 86400;
$delete = ("DELETE FROM $tbl WHERE time > '$now'");

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

情感失落者 2024-11-22 18:06:53

select * from table
where now() - interval 1 day > from_unixtime(unix_timestamp_field)

如果这是您要查找的内容，请将选择转换为删除查询

select * from table
where now() - interval 1 day > from_unixtime(unix_timestamp_field)

if this is what you're lookin for convert the select into a delete query

回复收藏 0 原文

茶花眉 2024-11-22 18:06:53

这应该有效：

DELETE FROM $tbl 
WHERE FROM_UNIXTIME(`time`) > DATE_SUB(NOW(), INTERVAL 1 DAY);

否则，在您的代码中，我认为您应该删除 $now 周围的单引号。然而，我认为最好将这一切作为 MySQL 查询的一部分来完成，以避免 PHP 和 MySQL 运行在不同时区时出现任何时间差异

This should work:

DELETE FROM $tbl 
WHERE FROM_UNIXTIME(`time`) > DATE_SUB(NOW(), INTERVAL 1 DAY);

Or otherwise, in your code, I think you should remove the single-quotes around $now. However, I think it is a good idea to do it all as part of a MySQL query to avoid any time differences between PHP and MySQL if they are both running in different time-zones

回复收藏 0 原文

岁月静好 2024-11-22 18:06:53

Unix 时间戳随着时间的推移而增加，因此您的查询将删除 24 小时前（不超过 24 小时前）的所有记录。

您也应该可以删除时间戳值周围的单引号。

如果您仍然遇到问题，请包含执行 mysql_query() 的代码行和数据库的格式（SHOW CREATE TABLE myTable 的输出）

回复收藏 0 原文

巾帼英雄 2024-11-22 18:06:52

我不确定 MySQL，但可能你需要这样的东西：

DELETE FROM $tbl WHERE DATEDIFF('$now', time) > INTERVAL 1 DAY

I'm not sure about MySQL, but probably you need something like that:

DELETE FROM $tbl WHERE DATEDIFF('$now', time) > INTERVAL 1 DAY

回复收藏 0 原文

瀟灑尐姊 2024-11-22 18:06:52

像这样怎么样：

$yesterday = strtotime('-1 day');

$delete = "DELETE FROM $tbl WHERE time > FROM_UNIXTIME($yesterday)";

上面的查询将删除“时间”值大于 24 小时前的所有行。这里假设“时间”字段是 TIMESTAMP、DATETIME 或 DATE 类型。如果您想删除早于一天的记录，请更改 >对于一个<.

How about something like this:

$yesterday = strtotime('-1 day');

$delete = "DELETE FROM $tbl WHERE time > FROM_UNIXTIME($yesterday)";

The above query will delete all rows where the "time" value is greater than exactly 24 hours ago. This assumes that the "time" field is a TIMESTAMP, DATETIME or DATE type. If you want to delete records that are older than a day, change the > for a <.

回复收藏 0 原文

~没有更多了~