实时更改MySQL中不同时间间隔的行“状态”

Question

我将尽可能详细地解释这个问题，而不是解释应用程序本身。

我有一个MySQL表，记录发送消息的时间，以及消息是已读还是未读。现在，我需要在该表中创建一列，根据消息未读的时间间隔为消息提供状态。

For example, if message was unread for:
1h - it gets status of "1";
1day - status of "2";
2days - status of "3";
3days - status of "4";
7days and more - status of "5".

假设我有一条13:20发送的消息，它是未读的，它的状态是"NULL“。我需要它在14:20的状态为"1“，第二天13:20的状态为"2”。

现在我可以想到几个选项，但它们涉及到每分钟的Crone作业，和/或大量的计算。我的网站正在快速增长，我想找到一个最优化和可伸缩的选择。也许有一些内部的MySQL选项，比如触发器之类的。

感谢任何帮助<3

Answer 1

这似乎更适合在检索消息的查询中处理，而不是在不断更新表的查询中处理。

您可以在检索消息后在应用程序中计算状态，也可以从查询本身返回计算的状态字段：

SELECT
  user,
  from,
  subject,
  time,
  created,
  read,
  IF(NOW() - INTERVAL 7 DAY > created, 5,
    IF(NOW() - INTERVAL 3 DAY > created, 4,
      IF(NOW() - INTERVAL 2 DAY > created, 3
        IF(NOW() - INTERVAL 1 DAY > created, 2
          IF(NOW() - INTERVAL 1 HOUR > created, 1
            0
          )
        )
      )
    )
  )
FROM messages
WHERE user = 'foo'

Answer 2

我建议创建一个created_at时间戳列作为消息模式的一部分，这将允许您运行类似以下内容的查询：

 select *, datediff(now(), created_at) as date_diff from your_table;

然后，当您准备要演示的数据时，您可以这样做(仅限演示)：

class Message
{
    public static function convertToStatusCode($diff)
    {
        switch ($diff) {

            case 0:
                return 1;

            case 1:
                return 2;

            case 2:
                return 3;

            case 3:
                return 4;

            default: 
                return 5;
        }
    }
}

// Construct and execute your query
while ($row = $dbo->fetch()) {

     // Add status while hydrating
     $message = new Message();

     $message->setStatus(Message:convertToStatusCode($row['date_diff']));
}