php 如何使用 Laravel 迁移将时间戳列的默认值设置为当前时间戳？

How Can I Set the Default Value of a Timestamp Column to the Current Timestamp with Laravel Migrations?

提问by JoeyD473

I would like to make a timestamp column with a default value of CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMPusing the Laravel Schema Builder/Migrations. I have gone through the Laravel documentation several times, and I don't see how I can make that the default for a timestamp column.

我想CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP使用 Laravel Schema Builder/Migrations的默认值创建一个时间戳列。我已经多次阅读 Laravel 文档，但我不知道如何将其设为时间戳列的默认值。

The timestamps()function makes the defaults 0000-00-00 00:00for both columns that it makes.

该timestamps()函数0000-00-00 00:00为其生成的两列设置默认值。

回答by Paulo Freitas

Given it's a raw expression, you should use DB::raw()to set CURRENT_TIMESTAMPas a default value for a column:

鉴于它是一个原始表达式，您应该使用DB::raw()将其设置CURRENT_TIMESTAMP为列的默认值：

$table->timestamp('created_at')->default(DB::raw('CURRENT_TIMESTAMP'));

This works flawlessly on every database driver.

这在每个数据库驱动程序上都可以完美运行。

New shortcut

新建快捷方式

As of Laravel 5.1.25 (see PR 10962and commit 15c487fe) you can use the new useCurrent()column modifier method to set the CURRENT_TIMESTAMPas a default value for a column:

从 Laravel 5.1.25（参见PR 10962和commit 15c487fe）开始，您可以使用新的useCurrent()列修饰符方法将 设置CURRENT_TIMESTAMP为列的默认值：

$table->timestamp('created_at')->useCurrent();

Back to the question, on MySQL you could also use the ON UPDATEclause through DB::raw():

回到问题，在 MySQL 上，您还可以ON UPDATE通过DB::raw()以下方式使用子句：

$table->timestamp('updated_at')->default(DB::raw('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'));

Gotchas

陷阱

• MySQL
  
  Starting with MySQL 5.7, 0000-00-00 00:00:00is no longer considered a valid date. As documented at the Laravel 5.2 upgrade guide, all timestamp columns should receive a valid default value when you insert records into your database. You may use the useCurrent()column modifier (from Laravel 5.1.25 and above) in your migrations to default the timestamp columns to the current timestamps, or you may make the timestamps nullable()to allow null values.

• PostgreSQL & Laravel 4.x
  
  In Laravel 4.x versions, the PostgreSQL driver was using the default database precision to store timestamp values. When using the CURRENT_TIMESTAMPfunction on a column with a default precision, PostgreSQL generates a timestamp with the higher precision available, thus generating a timestamp with a fractional second part - see this SQL fiddle.

  This will led Carbon to fail parsing a timestamp since it won't be expecting microseconds being stored. To avoid this unexpected behavior breaking your application you have to explicitly give a zero precision to the CURRENT_TIMESTAMPfunction as below:

  $table->timestamp('created_at')->default(DB::raw('CURRENT_TIMESTAMP(0)'));
  

  Since Laravel 5.0, timestamp()columns has been changed to use a default precision of zero which avoids this.

  Thanks to @andrewhlfor pointing out this issue in the comments.

• MySQL
  
  从 MySQL 5.7 开始，0000-00-00 00:00:00不再被视为有效日期。如Laravel 5.2 升级指南中所述，当您将记录插入数据库时​​，所有时间戳列都应接收有效的默认值。您可以useCurrent()在迁移中使用列修饰符（来自 Laravel 5.1.25 及更高版本）将时间戳列默认为当前时间戳，或者您可以使时间戳nullable()允许空值。

• PostgreSQL 和 Laravel 4.x
  
  在 Laravel 4.x 版本中，PostgreSQL 驱动程序使用默认的数据库精度来存储时间戳值。在CURRENT_TIMESTAMP具有默认精度的列上使用该函数时，PostgreSQL 生成具有更高可用精度的时间戳，从而生成带有小数秒部分的时间戳 -请参阅此 SQL fiddle。

  这将导致 Carbon 无法解析时间戳，因为它不会期望存储微秒。为避免这种意外行为破坏您的应用程序，您必须明确地为CURRENT_TIMESTAMP函数提供零精度，如下所示：

  $table->timestamp('created_at')->default(DB::raw('CURRENT_TIMESTAMP(0)'));
  

  从 Laravel 5.0 开始，timestamp()列已更改为使用默认精度零，从而避免了这种情况。

  感谢@andrewhl在评论中指出了这个问题。

回答by Brian Adams

To create both of the created_atand updated_atcolumns:

要同时创建created_at和updated_at列：

$t->timestamp('created_at')->default(DB::raw('CURRENT_TIMESTAMP'));
$t->timestamp('updated_at')->default(DB::raw('CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP'));

You will need MySQL version >= 5.6.5 to have multiple columns with CURRENT_TIMESTAMP

您将需要 MySQL 版本 >= 5.6.5 才能拥有多个列 CURRENT_TIMESTAMP

回答by Gras Double

Starting from Laravel 5.1.26, tagged on 2015-12-02, a useCurrent()modifier has been added:

从 Laravel 5.1.26 开始，标记于 2015-12-02，useCurrent()添加了一个修饰符：

Schema::table('users', function ($table) {
    $table->timestamp('created')->useCurrent();
});

PR 10962(followed by commit 15c487fe) leaded to this addition.

PR 10962（后跟提交 15c487fe）导致了这个添加。

You may also want to read issues 3602and 11518which are of interest.

您可能还想阅读感兴趣的问题3602和11518。

Basically, MySQL 5.7 (with default config) requires you to define either a default value or nullable for time fields.

基本上，MySQL 5.7（具有默认配置）要求您为时间字段定义默认值或可为空。

回答by Glenn Plas

This doesn't work for a fact:

这不适用于以下事实：

$table->timestamp('created_at')->default('CURRENT_TIMESTAMP');

It doesn't remove the 'default 0' that seems to come with selecting timestamp and it just appends the custom default. But we kind of need it without the quotes. Not everything that manipulates a DB is coming from Laravel4. That's his point. He wants custom defaults on certain columns like:

它不会删除似乎随选择时间戳而来的“默认 0”，它只是附加了自定义默认值。但是我们有点需要它没有引号。并非所有操作 DB 的东西都来自 Laravel4。这就是他的观点。他希望在某些列上使用自定义默认值，例如：

$table->timestamps()->default('CURRENT_TIMESTAMP');

I don't think it's possible with Laravel. I've been searching for an hour now to see whether it's possible.

我认为 Laravel 是不可能的。我已经搜索了一个小时，看看是否有可能。

---

---

Update:Paulos Freita's answershows that it is possible, but the syntax isn't straightforward.

更新：Paulos Freita 的回答表明这是可能的，但语法并不简单。

回答by Marwelln

Use Paulo Freitas suggestioninstead.

请改用 Paulo Freitas 的建议。

---

---

Until Laravel fixes this, you can run a standard database query after the Schema::createhave been run.

在 Laravel 修复此问题之前，您可以在运行后运行标准数据库查询Schema::create。

    Schema::create("users", function($table){
        $table->increments('id');
        $table->string('email', 255);
        $table->string('given_name', 100);
        $table->string('family_name', 100);
        $table->timestamp('joined');
        $table->enum('gender', ['male', 'female', 'unisex'])->default('unisex');
        $table->string('timezone', 30)->default('UTC');
        $table->text('about');
    });
    DB::statement("ALTER TABLE ".DB::getTablePrefix()."users CHANGE joined joined TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL");

It worked wonders for me.

它为我创造了奇迹。

回答by ndberg

As additional possibility for future googlers

作为未来谷歌员工的额外可能性

I find it more useful to have nullin the updated_at column when the record is been created but has never been modified. It reduces the db size (ok, just a little) and its possible to see it at the first sight that the data has never been modified.

我发现在创建记录但从未修改过的 update_at 列中设置null更有用。它减少了 db 大小（好吧，只是一点点），并且可以在第一眼看到数据从未被修改过。

As of this I use:

至此，我使用：

$table->timestamp('created_at')->useCurrent();
$table->timestamp('updated_at')->default(DB::raw('NULL ON UPDATE CURRENT_TIMESTAMP'))->nullable();

(In Laravel 7 with mysql 8).

（在带有 mysql 8 的 Laravel 7 中）。

回答by Jawad

This is how you do it, I have checked it and it works on my Laravel 4.2.

这就是你的做法，我已经检查过了，它适用于我的 Laravel 4.2。

$table->timestamp('created_at')->default(DB::raw('CURRENT_TIMESTAMP'));

Hope this helps.

希望这可以帮助。

回答by JoenMarz

In Laravel 5 simply:

在 Laravel 5 中：

$table->timestamps(); //Adds created_at and updated_at columns.

Documentation: http://laravel.com/docs/5.1/migrations#creating-columns

文档：http: //laravel.com/docs/5.1/migrations#creating-columns