php 删除超过 30 天的 mysql 记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9734901/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Delete mysql record older than 30 days
提问by fish man
How to delete mysql record older than 30 days? my code will delete all the records even which are inserted yesterday.
如何删除超过 30 天的 mysql 记录？我的代码将删除所有记录，即使是昨天插入的记录。
require('../conn_db.php');
mysql_select_db("my_news",$conn);
mysql_query("SET NAMES utf8");
mysql_query("DELETE FROM my_news WHERE date < DATE_SUB(NOW(), INTERVAL 1 MONTH)");
mysql_close("my_news");
And mysql table
和mysql表
date int(10)
1321095600
1322107200
...
1328288400
1328290440
采纳答案by clops
Your MySQL Table does not store a date, but rather a unix timestamp (judging from the data you have provided). To delete do the following:
您的 MySQL 表不存储日期，而是存储 unix 时间戳（根据您提供的数据判断）。要删除，请执行以下操作：
mysql_query("DELETE FROM my_news WHERE date < ".strtotime('-1 month'));
回答by professorsloth
First off, if you really want to delete records older than 30 days, use INTERVAL 30 DAYinstead, when you use INTERVAL 1 MONTHyou will delete records added on Mars 31st, when it's April 1st.
首先，如果您真的想删除超过 30 天的记录，请INTERVAL 30 DAY改用，当您使用时，INTERVAL 1 MONTH您将删除在火星 31 日（即 4 月 1 日）添加的记录。
Also, your date-column is of type int, and DATE_SUB() will return a date in this format YYYY-MM-DD HH:MM:SS, so they are not comparable. You could do this to work around that problem:
此外，您的日期列的类型为int，而 DATE_SUB() 将以这种格式返回日期YYYY-MM-DD HH:MM:SS，因此它们不可比较。你可以这样做来解决这个问题：
DELETE FROM my_news WHERE date < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 30 DAY))
回答by Java
Try this .
尝试这个 。
mysql_query("DELETE FROM my_news WHERE date < DATE_SUB(NOW(), INTERVAL 30 DAY)");
回答by Brendan Bullen
Your SQL is fine but you have confused your datatypes. Just make a call to UNIX_TIMESTAMP(date): http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_unix-timestamp
您的 SQL 很好，但您混淆了数据类型。只需致电UNIX_TIMESTAMP(date)：http: //dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_unix-timestamp
require('../conn_db.php');
mysql_select_db("my_news",$conn);
mysql_query("SET NAMES utf8");
mysql_query("DELETE FROM my_news WHERE date < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 1 MONTH))");
mysql_close("my_news");
回答by mithunsatheesh
do it like
做喜欢
$time_ago = strtotime("-30 day");
mysql_query("DELETE FROM my_news WHERE date < $time_ago");
回答by Christofer Eliasson
Maybe not the prettiest, but since you seems to store your time as an int. How about just subtracting 30 days from now, and compare to that value:
也许不是最漂亮的，但是因为您似乎将时间存储为 int。从现在开始减去 30 天，然后与该值进行比较如何：
DELETE
FROM my_news
WHERE `date` < (UNIX_TIMESTAMP() - (60 * 60 * 24 * 30))
回答by MakuraYami
$expiretime = time() - 2592000; //1 * 60 * 60 * 24 * 30
mysql_query("DELETE FROM my_news WHERE date < ".$expiretime);
回答by heyanshukla
Try to use date_interval_create_from_date_string('1 MONTH')instead of INTERVAL 1 MONTH in the second parameter of DATE_SUB().
尝试date_interval_create_from_date_string('1 MONTH')在 的第二个参数中使用INTERVAL 1 MONTH 代替DATE_SUB()。
回答by Somnath Muluk
Try this sql query:
试试这个 sql 查询：
DELETE FROM my_news WHERE DATEDIFF(NOW() ,date )>30;
回答by Sarath Tomy
mysql_query("DELETE FROM my_news WHERE date < DATE_SUB(CURDATE(), INTERVAL 1 MONTH)");
