mysql - 拉维尔 : set default timestamp to one year from now

Question

我看过一些关于如何使用 CURRENT_TIMESTAMP 将时间戳列的默认值设置为当前日期时间的主题。但是，我正在寻找如何将默认值设置为 CURRENT_TIMESTAMP + 1 年。

我试过了，但没有成功:

Schema::table('users', function (Blueprint $table) {
    $table->timestamp('expires')->default(DB::raw('NOW() + INTERVAL 1 YEAR'))->after('updated_at');
});

收到此错误:

SQLSTATE[42000]: Syntax error or access violation: 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 '+ INTERVAL 1 YEAR after updated_at' at line 1 (SQL: alter table users add expires timestamp not null default NOW() + INTERVAL 1 YEAR after updated_at)

SQLSTATE[42000]: Syntax error or access violation: 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 '+ INTERVAL 1 YEAR after updated_at' at line 1

关于如何将此列的默认值设置为从当前日期起一年的任何想法？

Answer 1

您可以通过创建触发器让数据库处理所有这些。

创建表/插入表/创建触发器

CREATE DATABASE IF NOT EXISTS stackoverflow;
USE stackoverflow;

DROP TABLE IF EXISTS users;

CREATE TABLE IF NOT EXISTS users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY
  , user_name VARCHAR(255)  
  , expires DATETIME 
); 


DROP TRIGGER IF EXISTS users_expires;

DELIMITER $$

CREATE
    TRIGGER users_expires BEFORE INSERT
    ON users
    FOR EACH ROW BEGIN
      IF NEW.expires IS NULL 
       THEN 
        SET NEW.expires = NOW() + INTERVAL 1 YEAR;   
      END IF;
    END$$

DELIMITER ;

查询

INSERT INTO users (user_name) VALUES ('raymond nijland')

结果

Query: INSERT INTO users (user_name) VALUES ('raymond nijland')

1 row(s) affected

查询

SELECT * FROM users;

结果

    id  user_name        expires              
------  ---------------  ---------------------
     1  raymond nijland  2018-11-06 18:50:16