使用 Laravel 迁移，如何在不使用原始 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?

提问by Amr

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

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

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');
}

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

现在假设我想改变列的数据类型is_suspended从enum到boolean不使用原始的SQL查询，并与也填补了新的布尔列0，而不是枚举值的no和1，而不是枚举vlaue yes，我怎么能做到这一点，在Laravel框架迁移？

回答by Amr

Here is how I performed this:

这是我如何执行此操作：

The up() method:

up() 方法：

...
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');
    });
}

The down() method:

down() 方法：

/**
 * 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');
    });
}