时间戳

首页 > 解决方案 > 使用 TIME 列类型使记录过期的 DATE_ADD()

mysql - 使用 TIME 列类型使记录过期的 DATE_ADD()

问题描述

我有两个表，KEY_TYPE和USER_KEY：

CREATE TABLE `KEY_TYPE` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `internal_name` varchar(16) NOT NULL,
  `expiration` time NOT NULL DEFAULT '00:00:00',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_application_internal_name` (`internal_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE `USER_KEY` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `uuid` char(36) NOT NULL,
  `key_type_id` int(11) NOT NULL,
  `CREATE_DATE` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

我想要DELETE一个USER_KEY一旦它是CREATE_DATE加上KEY_TYPE.expiration大于NOW(). 我将设置一个事件来完成此操作（假设这是最佳实践？）。但是，我无法弄清楚如何将 Time 数据类型过期列正确添加到CREATE_DATE列。我想我已经接近了，但是例如在CREATE_DATEDATE_ADD中增加了超过 24 小时。到目前为止，我的查询如下：

SELECT USER_KEY.create_date, KEY_TYPE.expiration,
DATE_ADD(USER_KEY.create_date, INTERVAL KEY_TYPE.expiration SECOND) CREATE_PLUS_EXPIRATION
FROM USER_KEY
JOIN KEY_TYPE ON USER_KEY.key_type_id = KEY_TYPE.id;

我一直在用下面的例子在 DB Fiddle 上玩耍。

标签： mysql

解决方案

谢谢@P.Salmon 为我指明了正确的方向。我最终改变了INTERVAL SECOND似乎INTERVAL HOUR_SECOND可以解决问题的方法。

这是我结束的事件：

SET GLOBAL event_scheduler = ON;
CREATE EVENT EVENT_EXPIRE_USER_KEYS
    ON SCHEDULE EVERY 1 HOUR STARTS CURRENT_TIMESTAMP
    DO
        DELETE USER_KEY
        FROM USER_KEY
        JOIN KEY_TYPE ON USER_KEY.key_type_id = KEY_TYPE.id
        WHERE  DATE_ADD(USER_KEY.create_date, INTERVAL KEY_TYPE.expiration HOUR_SECOND)  < NOW();

但这也适用：

SET GLOBAL event_scheduler = ON;
CREATE EVENT EVENT_EXPIRE_USER_KEYS
    ON SCHEDULE EVERY 1 HOUR STARTS CURRENT_TIMESTAMP
    DO
        DELETE USER_KEY
        FROM USER_KEY
        JOIN KEY_TYPE ON USER_KEY.key_type_id = KEY_TYPE.id
        WHERE  DATE_ADD(USER_KEY.create_date, INTERVAL TIME_TO_SEC(KEY_TYPE.expiration) SECOND)  < NOW();

推荐阅读