'Create table and set default value to NOW() + 24 hours

I'm trying to create a table and set the default value to now() + 24 hours. I'm getting syntax errors when trying to do this.

This doesn't work

CREATE TABLE IF NOT EXISTS `my_table` (
    `my_table_id` CHAR(36) BINARY NOT NULL , 
    `expiration_time` DATETIME NOT NULL DEFAULT (NOW() + INTERVAL 24 HOUR),
    PRIMARY KEY (`my_table_id`)
) ENGINE=InnoDB;

Although this does work SELECT NOW() + INTERVAL 24 HOUR; so i'm not sure why it doesn't work when trying to create a table.



Solution 1:[1]

Expressions for defaults are not supported in MySQL 5.7.

You can implement a "default" expression in a trigger such as the following:

CREATE TRIGGER my_awesome_trigger BEFORE INSERT ON my_table
FOR EACH ROW
  SET NEW.expiration_time = COALESCE(NEW.expiration_time, NOW() + INTERVAL 24 HOUR));

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1