database 如何使用 Laravel 架构构建器更改列类型？

How do I change a column type with Laravel schema builder?

提问by mike

I need to change two fields from integer to foreign key. How do I build my migration to do so?

我需要将两个字段从整数更改为外键。我如何构建我的迁移来做到这一点？

Schema::create('messages', function($table)
{
    $table->increments('id');
    $table->integer('sender');
    $table->integer('recipient');
    $table->string('title');
    $table->longtext('body');
    $table->timestamps();
    $table->softDeletes();
    $table->integer('regarding');
});

I'll change senderto sender_id, recipientto recipient_idand regardingto regarding_id.

我会改sender到sender_id，recipient到recipient_id和regarding到regarding_id。

回答by Kajan Thadsanamoorthy

Don't think too much about it, I'm using following code. Here I change the data type from varchar to text.

不要想太多，我正在使用以下代码。在这里，我将数据类型从 varchar 更改为 text。

public function up(){
    DB::statement('ALTER TABLE items MODIFY COLUMN item_description TEXT');
    DB::statement('ALTER TABLE items MODIFY COLUMN delivery_description TEXT');
}

public function down(){
    DB::statement('ALTER TABLE items MODIFY COLUMN item_description VARCHAR(255)');
    DB::statement('ALTER TABLE items MODIFY COLUMN delivery_description VARCHAR(255)');
} 

回答by Ben

I came across a similar problem needing to change a column type from string to integer. I managed this using two separate migrations (with a RAW sql statement in each) to get the solution.

我遇到了一个类似的问题，需要将列类型从字符串更改为整数。我使用两个单独的迁移（每个迁移都有一个 RAW sql 语句）来管理这个以获得解决方案。

In addition this works with Postgres and MySQL because we were in the middle of a transfer.

此外，这适用于 Postgres 和 MySQL，因为我们正处于传输过程中。

The first migration:

第一次迁移：

/**
 * Run the migrations.
 *
 * @return void
 */
public function up()
{
    Schema::table('plans', function(Blueprint $table)
    {
        //
        $table->mediumInteger('duration_change_type')->default(0)->after('duration');
    });

    if (Config::get('database')['default'] === 'mysql'){
        // Mysql 
        DB::statement('update plans set duration_change_type=duration');

    } else if (Config::get('database')['default'] === 'pgsql'){
        // PostgreSQL
        DB::statement('update plans set duration_change_type=duration::integer');
    }
}

/**
 * Reverse the migrations.
 *
 * @return void
 */
public function down()
{
    Schema::table('plans', function(Blueprint $table)
    {
        //
        $table->dropColumn('duration_change_type');
    });
}

The second migration:

第二次迁移：

/**
 * Run the migrations.
 *
 * @return void
 */
public function up()
{
    Schema::table('plans', function(Blueprint $table)
    {
        //
        $table->dropColumn('duration');
        $table->renameColumn('duration_change_type', 'duration');
    });
}

/**
 * Reverse the migrations.
 *
 * @return void
 */
public function down()
{
    Schema::table('plans', function(Blueprint $table)
    {
        // Rollback to string
        $table->string('duration_change_type')->default(0)->after('duration');
    });
    if (Config::get('database')['default'] === 'mysql'){
        // Mysql 
        DB::statement('update plans set duration_change_type=duration');

    } else if (Config::get('database')['default'] === 'pgsql'){
        // PostgreSQL
        DB::statement('update plans set duration_change_type=duration::text');
    }
}

Thinking on this now it could be simplified to a single migration.

现在考虑这个可以简化为单个迁移。

回答by Chaudhry Waqas

If you want to change the column type or modifying length etc, just call the change()method

如果要更改列类型或修改长度等，只需调用该change()方法

Schema::table('users', function ($table) {
    $table->string('id')->change();
});

and to rename the column

并重命名列

Schema::table('users', function ($table) {
    $table->renameColumn('from', 'to');
});

回答by AndyC

I've just come across the same issue and the quickest solution is to simply change the column structure on the database and then also alter the original migration to reflect the change made on the DB.

我刚刚遇到了同样的问题，最快的解决方案是简单地更改数据库上的列结构，然后更改原始迁移以反映对数据库所做的更改。

This way if the migration is ever rolled back and then up again the correct fields will be created. I can't see any downside to this as long as you make sure to accurately alter the migration.

这样，如果迁移被回滚然后再次向上，将创建正确的字段。只要您确保准确更改迁移，我看不出有任何不利之处。

回答by Mohamed Bouallegue

I had a similar issue trying to change column type from text to binary. I was getting an error when trying to run the migrate command. I ended up solving it this way:

我在尝试将列类型从文本更改为二进制时遇到了类似的问题。尝试运行 migrate 命令时出现错误。我最终以这种方式解决了它：

public function up()
{
    Schema::table('user', function (Blueprint $table) {
        $table->dropColumn('address');
    });
    Schema::table('user', function (Blueprint $table) {
        $table->binary('address')->nullable();
    });
}

回答by suraz

you can change column name by using change() function.here i have mentioned one schema table as a example

您可以使用 change() 函数更改列名。这里我以一个模式表为例

     Schema::table('person', function (Blueprint $table) {
        $table->increments('id');
        $table->text('name')->nullable()->change();
        $table->integer('number')->nullable()->change();
        $table->integer('billing_day')->nullable()->change();
        $table->timestamps()->nullable()->change();
    });

回答by brack11

You can try to rename column to something temporary with $table->renameColumn('from', 'to');then create new column with standard creation line for foreign key, and finally copy data from temporary column to the new one with controller function. Should work, but I didnt try it, didnt have a need.

您可以尝试将列重命名为临时列，$table->renameColumn('from', 'to');然后使用外键的标准创建行创建新列，最后将数据从临时列复制到具有控制器功能的新列。应该工作，但我没有尝试，没有必要。

回答by iMezied

I would just drop it and then re-add it as a different type.

我会放弃它，然后将其重新添加为不同的类型。

回答by lauri108

In Laravel 5, you can use a closure such as below. That should do it in a database-agnostic way, so no need to have if-statements checking for database types.

在 Laravel 5 中，您可以使用如下所示的闭包。这应该以与数据库无关的方式进行，因此无需检查数据库类型的 if 语句。

(Note that you may need to composer require doctrine/dbalto do this.)

（请注意，您可能需要Composer 要求学说/ dbal来执行此操作。）

public function up() {
    Schema::table('plans', function($t) {
        $t->renameColumn('duration_change_type', 'duration');
    });
}

public function down() {
    Schema::table('plans', function($t) {
        $t->renameColumn('duration', 'duration_change_type');
    });
}