MySQL中如何实现列ON UPDATE自动设为CURRENT_TIMESTAMP+5分钟？

问题描述 #

现有codes表包含三列：

• code：整数类型
• created_at：DATETIME类型，默认值CURRENT_TIMESTAMP，设置ON UPDATE CURRENT_TIMESTAMP可正常生效
• expires_at：DATETIME类型，默认值CURRENT_TIMESTAMP + INTERVAL 5 MINUTE可正常设置，但尝试为其添加ON UPDATE (CURRENT_TIMESTAMP + INTERVAL 5 MINUTE)时执行失败，报错：

  ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(CURRENT_TIMESTAMP + INTERVAL 5 MINUTE)' at line 1
  

  尝试用NOW()替代CURRENT_TIMESTAMP，或使用DATE_ADD()、ADDTIME()函数均无效。

原因分析 #

MySQL在8.0.19版本之前，对于TIMESTAMP和DATETIME列的ON UPDATE子句，仅支持CURRENT_TIMESTAMP（或其同义词如NOW()），不允许使用表达式计算值。而DEFAULT子句支持表达式的时间更早，这就是为什么默认值设置成功但ON UPDATE失败的核心原因。

解决方案 #

方案1：升级MySQL到8.0.19或更高版本 #

从MySQL 8.0.19开始，官方支持在ON UPDATE子句中使用表达式计算列的更新值，此时你最初的语句可直接生效：

ALTER TABLE codes ADD expires_at DATETIME DEFAULT (CURRENT_TIMESTAMP + INTERVAL 5 MINUTE) ON UPDATE (CURRENT_TIMESTAMP + INTERVAL 5 MINUTE);

如果是修改已存在的expires_at列：

ALTER TABLE codes MODIFY COLUMN expires_at DATETIME DEFAULT (CURRENT_TIMESTAMP + INTERVAL 5 MINUTE) ON UPDATE (CURRENT_TIMESTAMP + INTERVAL 5 MINUTE);

方案2：使用触发器（兼容低版本MySQL） #

如果无法升级版本，可通过创建BEFORE UPDATE触发器实现需求：

DELIMITER //
CREATE TRIGGER update_expires_at
BEFORE UPDATE ON codes
FOR EACH ROW
BEGIN
    SET NEW.expires_at = CURRENT_TIMESTAMP + INTERVAL 5 MINUTE;
END //
DELIMITER ;

该触发器会在每次更新codes表的行时，自动将expires_at设置为当前时间加5分钟，达到和ON UPDATE相同的效果。

验证触发器效果 #

执行更新操作后，检查expires_at是否自动更新：

UPDATE codes SET code = code WHERE id = 1; -- 替换为你的实际更新逻辑
SELECT expires_at FROM codes WHERE id = 1;

内容的提问来源于stack exchange，提问作者ollelloses