创建带有两个时间戳列的表时出现Laravel错误 [英] Laravel error in creating a table with two timestamp columns

问题描述

我在Laravel 6.6中创建了一个具有以下定义的表.

I created a table in Laravel 6.6 with the following definition.

public function up()
{
    Schema::create('quarters', function (Blueprint $table) {
        $table->integer('quarter_id')->unsigned();
        $table->integer('year')->unsigned();
        $table->integer('quarter_num')->unsigned();
        $table->timestamp('valid_from');
        $table->timestamp('valid_to'); // <------ error on this line
        $table->string('description')->nullable();
        $table->timestamps();
        $table->primary('quarter_id');
    });
} 

运行迁移命令时，出现以下错误.

When I run the migration command I get the following error.

Illuminate \ Database \ QueryException:SQLSTATE [42000]:语法错误或访问冲突:1067"valid_to"的默认值无效(SQL:创建表 quarters ( quarter_id int unsigned not null， year 无符号整数不为null， quarter_num int unsigned不为null， valid_from 时间戳记不为空， valid_to 时间戳记不为空， description varchar(255)null， created_at 时间戳记null， updated_at 时间戳记null)默认字符集utf8mb4整理为'utf8mb4_unicode_ci')

Illuminate\Database\QueryException : SQLSTATE[42000]: Syntax error or access violation: 1067 Invalid default value for 'valid_to' (SQL: create table quarters (quarter_id int unsigned not null, year int unsigned not null, quarter_num int unsigned not null, valid_from timestamp not null, valid_to timestamp not null, description varchar(255) null, created_at timestamp null, updated_at timestamp null) default character set utf8mb4 collate 'utf8mb4_unicode_ci')

这是Eloquent生成的SQL:

here is the SQL generated by Eloquent:

CREATE TABLE `quarters`(
    `quarter_id` INT UNSIGNED NOT NULL,
    `year` INT UNSIGNED NOT NULL,
    `quarter_num` INT UNSIGNED NOT NULL,
    `valid_from` TIMESTAMP NOT NULL,
    `valid_to` TIMESTAMP NOT NULL,
    `description` VARCHAR(255) NULL,
    `created_at` TIMESTAMP NULL,
    `updated_at` TIMESTAMP NULL
) DEFAULT CHARACTER SET utf8mb4 COLLATE 'utf8mb4_unicode_ci'

奇怪的是，如果我注释掉 valid_to 行，那么它将创建没有错误的表.但是 valid_to 的定义与 valid_from 相似，并且100％不会为 valid_from 列引发该错误.实际上，似乎数据库不允许两个 timestamp 列！

The strange thing is that if I comment out the valid_to line then it creates the table with no error. But the definition of valid_to is 100% similar to valid_from, and it does not throw that error for the valid_from column. Actually it seems the DB does not allow for twotimestamp columns!

根据注释中的要求，我运行了 php artisan migration --pretend ，结果如下:

As requested in the comments I ran the php artisan migrate --pretend and here is the result:

C:\xampp\htdocs\voiceit> php artisan migrate --pretend
CreateQuartersTable: create table `quarters` (`quarter_id` int unsigned not null, `year` int unsigned not null, `quarter_num` int unsigned not null, `valid_from` timestamp not null, `valid_to` timestamp not null, `description` varchar(255) null, `created_at` timestamp null, `updated_at` timestamp null) default character set utf8mb4 collate 'utf8mb4_unicode_ci'
CreateQuartersTable: alter table `quarters` add primary key `quarters_quarter_id_primary`(`quarter_id`)
CreatePeopleDatasTable: create table `people_datas` (`mt_id` bigint unsigned not null, `valid_for` int unsigned not null, `local_personal_id` bigint unsigned not null, `first_name` varchar(255) not null, `last_name` varchar(255) not null, `date_of_birth` date null, `date_of_join` date null, `gender` varchar(1) not null, `location_type` varchar(1) not null, `created_at` timestamp null, `updated_at` timestamp null, `deleted_at` timestamp null) default character set utf8mb4 collate 'utf8mb4_unicode_ci'
CreatePeopleDatasTable: alter table `people_datas` add primary key `people_datas_mt_id_valid_for_primary`(`mt_id`, `valid_for`)
CreatePeopleDatasTable: alter table `people_datas` add constraint `people_datas_valid_for_foreign` foreign key (`valid_for`) references `quarters` (`quarter_id`)
CreatePeopleDatasTable: alter table `people_datas` add constraint `people_datas_gender_foreign` foreign key (`gender`) references `genders` (`id`)
CreatePeopleDatasTable: alter table `people_datas` add constraint `people_datas_location_type_foreign` foreign key (`location_type`) references `location_types` (`id`)
CreatePeopleDatasTable: alter table `people_datas` add index `people_datas_last_name_index`(`last_name`)

推荐答案

我通过将列的类型从 timestamp 更改为 dateTime 来解决了我的问题.因此，按如下所示更改表定义解决了我的问题，因为我需要一个日期和时间:

I solved my issue by changing the type of the column from timestamp to dateTime. So changing the table definition as follow solved my issue as I need a date and a time:

Schema::create('quarters', function (Blueprint $table) {
            $table->integer('quarter_id')->unsigned();
            $table->integer('year')->unsigned();
            $table->integer('quarter_num')->unsigned();
            $table->dateTime('valid_from');
            $table->dateTime('valid_to');            
            $table->string('description')->nullable();
            $table->timestamps();
            $table->primary('quarter_id');
        });

但是，我仍然很想知道我们如何在一个表中有多个 not null 时间戳列.

However, I am still interested to know how can we have multiple not null timestamp columns in a table.

这篇关于创建带有两个时间戳列的表时出现Laravel错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！