删除一个月/30天以上的记录 [英] Deleting records older than a month/30 days

问题描述

我将数据存储在mysql数据库中.我正在尝试删除所有超过30天的记录.所有记录都有一个指定记录created_date的字段. created_date的格式为Mon, 08 Dec 2014 00:33:13 -0500.运行删除查询时，将删除所有记录.甚至不超过30天的记录.我不确定这是否是由于created_date字段中日期的格式所致.如何删除30天以上的记录?

I have data stored in mysql database. I am trying to delete all records that are greater than 30 days. All records have a field that specify a created_date of the record. The format of the created_date is Mon, 08 Dec 2014 00:33:13 -0500. When running the delete query, all record are deleted. Even records that are not older than 30 days. I am not sure if it is due to how the date is formatted in the created_date field. How can I delete records older than 30 days?

try {
     $days = strtotime('1 month');
     $delete = $pdo->prepare("DELETE FROM archive WHERE created_date > :days");
            return $delete->execute(array('days' => $days));
        }catch (Exception $e) {
                      echo "Failed: " . $e->getMessage();
        }

表架构

+------------+-----------+-----+---------------+---------------------------------+
| First Name | Last Name | Age | Date of Birth |           created_date          |
+------------+-----------+-----+---------------+---------------------------------+
| Lionel     | Messi     |  27 | 6/24/1987     | Mon, 08 Dec 2014 00:33:13 -0500 |
| Michael    | Jordan    |  51 | 2/17/1963     | Tue, 15 Nov 2014 00:33:13 -0500 |
| Lebron     | James     |  30 | 12/30/1984    | Fri, 02 Oct 2014 00:33:13 -0500 |
+------------+-----------+-----+---------------+---------------------------------+

推荐答案

DELETE FROM archive
WHERE STR_TO_DATE(SUBSTR(created_date, 0, 25), '%a, %d %b %Y %H:%i:%S') <
      DATE_SUB(NOW(), INTERVAL 30 DAY);

然后您可以删除用于计算日期范围的PHP代码，然后让MySQL为您处理.

You can then remove the PHP code for calculating the date range and let MySQL handle it for you.

如果您可以控制数据库结构，则将日期存储在DATETIME字段中将比在每个数据库行上调用STR_TO_DATE效率更高.

If you have control over the database structure, it will be much more efficient to store the date in a DATETIME field, rather than calling STR_TO_DATE on every database row.

这篇关于删除一个月/30天以上的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！