MySQL从表中删除数据

I have timestamp value in a column of my table.I need to keep all the data of the last week and delete rest data in the table(which is not belong to last 7 days).How can I do it?

表的一列中有时间戳值。我需要保留上周的所有数据，并删除表中的rest数据(不属于最近7天)。我怎么做呢?

The query that I tried is given below.

下面给出了我尝试的查询。

DELETE * FROM EmailMainTable WHERE DATE_FORMAT(timestamp, '%Y-%m-%d %H:%i:%s') > 
DATE_SUB(DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s'), INTERVAL 8 DAY);

NOTE: My Filed Name is timestamp and i converted it to bigint

注意:我的存档名是timestamp，我将它转换为bigint

My table's structure: MySQL从表中删除数据

我的表的结构:

2 个解决方案

#1

Since you're converting the timestamps to varchars (using date_format), they will be compares lexicographically, which isn't the behavior you want. Just drop the formatting:

由于您正在将时间戳转换为varchars(使用date_format)，所以它们将会比较词法，而不是您想要的行为。只是把格式:

DELETE
FROM   EmailMainTable 
WHERE  `timestamp` > DATE_SUB(NOW(), INTERVAL 8 DAY);

#2

Finally I found the answer for my own question. This is the answer that worked for me.

最后我找到了我自己问题的答案。这是对我有用的答案。

DELETE 
FROM EmailMainTable
WHERE FROM_UNIXTIME(timestamp/1000,"%Y-%m-%d") < DATE_SUB(NOW(), INTERVAL 8 DAY);

#1