使用Laravel Migration，如何在不使用原始SQL查询的情况下更改列的数据类型并更新其现有数据以适应新的数据类型？ [英] With Laravel Migration, How to change data type of a column and update its existing data to fit the new data type, without using raw SQL queries?

问题描述

假设我有一个 users 表，该表是通过Migration and Schema Builder创建的，如下所示：

Suppose I have a users table which has been created through Migration and Schema Builder, like this:

public function up()
{
    Schema::create('users', function(Blueprint $table)
    {
        $table->increments('id')->unsigned();
        $table->string('name', 50);
        $table->string('email', 50)->unique();
        $table->string('password', 100);
        $table->enum('is_suspended', ['yes', 'no'])->default('no');
        $table->timestamps();
    });
}

public function down()
{
    Schema::drop('users');
}

现在假设我想更改列的数据类型 is_suspended 从 enum 到 boolean ，而无需使用原始SQL查询，并填充新的带有 0 的布尔列，而不是枚举值 no 和 1 而不是枚举vlaue 是，如何在Laravel框架中进行迁移？

Now suppose I want to change data type of the column is_suspended from enum to boolean without using raw SQL queries, and also fill the new boolean column with 0 instead of the enum value no and 1 instead of enum vlaue yes, How can I do this with migration in Laravel framework?

推荐答案

这是我的执行方式：

...
use App\User;

/**
 * Run the migrations.
 *
 * @return void
 */
public function up()
{
    // do the following steps in order:
    // 1- add a new column with the desired data type to the table
    // 2- fill the new column with the appropriate data
    // 3- delete the old column
    // 4- rename the new column to match name of the deleted column

    Schema::table('users', function(Blueprint $table)
    {
        // 1- add a new column with the desired data type to the table
        // note that after() method is used to order the column and works only with MySQL
        $table->boolean('is_suspended_new')->default(0)->after('is_suspended');
    });

    // 2- fill the new column with the appropriate data 
    // note that you may need to use data in the old column as a guide (like in this example)
    $users = User::all();
    if ($users) {
        foreach ($users as $user) {
            $u = User::find($user->id);

            if ($u->is_suspended === 'yes') {
                $u->is_suspended_new = 1;
            } elseif ($u->is_suspended === 'no') {
                $u->is_suspended_new = 0;
            }

            $u->save();
        }
    }

    Schema::table('users', function(Blueprint $table)
    {
        // 3- delete the old column
        $table->dropColumn('is_suspended');
    });

    Schema::table('users', function(Blueprint $table)
    {
        // 4- rename the new column to match name of the deleted column
        $table->renameColumn('is_suspended_new', 'is_suspended');
    });
}

down（）方法：

The down() method:

/**
 * Reverse the migrations.
 *
 * @return void
 */
public function down()
{
    // IMPORTANT NOTE:
    // because renaming (enum) column types is not supported in laravel 4.2 (as mentioned here https://laravel.com/docs/4.2/schema#renaming-columns)
    // so we will make some changes in the order of the steps as follows:
    // 
    // do the following steps in order:
    // 1- rename the exiting column
    // 2- add a new column with the desired data type and give it a name matches name of the existing column before renaming
    // 3- fill the new column with the appropriate data
    // 4- delete the old column

    Schema::table('users', function(Blueprint $table)
    {
        // 1- rename the existing column
        $table->renameColumn('is_suspended', 'is_suspended_old');
    });

    Schema::table('users', function(Blueprint $table)
    {
        // 2- add a new column with the desired data type to the table
        // and give it a name matches name of the existing column before renaming
        // note that after() method is used to order the column and works only with MySQL
        $table->enum('is_suspended', ['yes', 'no'])->default('no')->after('is_suspended_old');
    });

    // 3- fill the new column with the approprite data 
    // note that you may need to use data in the old column as a guide (like in this example)
    $users = User::all();
    if ($users) {
        foreach ($users as $user) {
            $u = User::find($user->id);

            if ($u->is_suspended_old == 1) {
                $u->is_suspended = 'yes';
            } elseif ($u->is_suspended_old == 0) {
                $u->is_suspended = 'no';
            }

            $u->save();
        }
    }

    Schema::table('users', function(Blueprint $table)
    {
        // 4- delete the old column
        $table->dropColumn('is_suspended_old');
    });
}

这篇关于使用Laravel Migration，如何在不使用原始SQL查询的情况下更改列的数据类型并更新其现有数据以适应新的数据类型？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！