mysql - 时间戳默认值不同于 current_timestamp

Question

我正在尝试向该表中添加一个时间戳列:

CREATE TABLE `task` (
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `name` varchar(45) NOT NULL,
   `timecreated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
   `session` varchar(45) NOT NULL,
   PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

然后我尝试执行此代码以添加另一个具有不同默认值的时间戳列:

ADD COLUMN `timeexpiration` TIMESTAMP NOT NULL DEFAULT TIMESTAMPADD(MINUTE, 15, CURRENT_TIMESTAMP)
ERROR 1064: 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 'TIMESTAMPADD(MINUTE, 15, CURRENT_TIMESTAMP) AFTER `timecreated`,'

ADD COLUMN `timeexpiration` TIMESTAMP NOT NULL DEFAULT (CURRENT_TIMESTAMP + INTERVAL 15 MINUTE) AFTER `timecreated`'
ERROR 1064: 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 15 MINUTE) AFTER `timecreated`,

但这些都不起作用。

Answer 1

您不能有两个使用 CURRENT_TIMESTAMP 的默认值的时间戳列，但是您可以在插入之前使用触发器:

ALTER TABLE task
ADD COLUMN `timeexpiration` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00';

DELIMITER //
CREATE TRIGGER task_timeexpiration_default BEFORE INSERT ON task FOR EACH ROW
BEGIN
    SET NEW.`timeexpiration` = TIMESTAMPADD(MINUTE, 15, CURRENT_TIMESTAMP);
END;//