'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 |