如何使用 Laravel 迁移将时间戳列的默认值设置为当前时间戳? [英] How Can I Set the Default Value of a Timestamp Column to the Current Timestamp with Laravel Migrations?

问题描述

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

I would like to make a timestamp column with a default value of CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP using 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.

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

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

推荐答案

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

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

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

这适用于每个数据库驱动程序.

This works flawlessly on every database driver.

从 Laravel 5.1.25 开始(参见 PR 10962 和 commit 15c487fe) 现在可以使用新的 useCurrent() 列修饰符方法来实现列的相同默认值:

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

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

回到问题，在 MySQL 上你也可以通过 DB::raw() 使用 ON UPDATE 子句:

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

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

同样，从 Laravel 8.36.0 开始(参见 PR 36817)，您现在可以使用新的 useCurrentOnUpdate() 列修饰符方法和 useCurrent() 修饰符来为列实现相同的默认值:

Again, as of Laravel 8.36.0 (see PR 36817) you can now use the new useCurrentOnUpdate() column modifier method together with the useCurrent() modifier to achieve the same default value for a column:

$table->timestamp('updated_at')->useCurrent()->useCurrentOnUpdate();

问题

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

  Gotchas

  • MySQL
    
    Starting with MySQL 5.7, 0000-00-00 00:00:00 is 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
    
    在 Laravel 4.x 版本中，PostgreSQL 驱动程序使用默认的数据库精度来存储时间戳值.在具有默认精度的列上使用 CURRENT_TIMESTAMP 函数时，PostgreSQL 生成具有更高可用精度的时间戳，从而生成带有小数秒部分的时间戳 - 请参阅此 SQL 小提琴.

    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_TIMESTAMP function 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.

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

    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_TIMESTAMP function as below:

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

    自 Laravel 5.0 起，timestamp() 列已更改为使用默认精度为零以避免这种情况.

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

    感谢 @andrewhl 在评论中指出 Laravel 4.x 问题.

    Thanks to @andrewhl for pointing out the Laravel 4.x issue in the comments.

    感谢 @ChanakaKarunarathne 推出新的 useCurrentOnUpdate() 评论中的快捷方式.

    Thanks to @ChanakaKarunarathne for bringing out the new useCurrentOnUpdate() shortcut in the comments.

    这篇关于如何使用 Laravel 迁移将时间戳列的默认值设置为当前时间戳?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！