search
LoginSignup
19
Help us understand the problem. What are the problem?

More than 1 year has passed since last update.

posted at

updated at

Organization

Laravel マイグレーションでカラム追加後の列順序が気持ち悪い

※mysqlだとafterを使ったら解決するみたい。

普通にマイグレーションでカラムaddするとuser_idが一番下に入って気持ち悪い。みんなどうしてるんだろうか。
こうなる。

mysql> SHOW FULL COLUMNS FROM `users`;
+------------+------------------+-----------------+------+-----+---------+----------------+---------------------------------+---------+
| Field      | Type             | Collation       | Null | Key | Default | Extra          | Privileges                      | Comment |
+------------+------------------+-----------------+------+-----+---------+----------------+---------------------------------+---------+
| id         | int(10) unsigned | NULL            | NO   | PRI | NULL    | auto_increment | select,insert,update,references |         |
| email      | varchar(191)     | utf8_general_ci | NO   |     | NULL    |                | select,insert,update,references |         |
| password   | varchar(191)     | utf8_general_ci | NO   |     | NULL    |                | select,insert,update,references |         |
| created_at | timestamp        | NULL            | YES  |     | NULL    |                | select,insert,update,references |         |
| updated_at | timestamp        | NULL            | YES  |     | NULL    |                | select,insert,update,references |         |
| user_id ★技術的特異点   | int(11)          | NULL            | NO   | MUL | NULL    |                | select,insert,update,references |         |
+------------+------------------+-----------------+------+-----+---------+----------------+---------------------------------+---------+
6 rows in set (0.00 sec)

結論1

afterを使う。
idの次にemailを追加。

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

結論2

一時テーブルに退避してテーブル作成し直す時のサンプル。

php artisan make:migration ModifyUsers
<?php

use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class ModifyUsers extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('users_temp', function (Blueprint $table) {
            $table->increments('id');
            $table->string('email')->unique;
            $table->string('password');
            $table->timestamps();
        });

        DB::insert('insert into users_temp select * from users');

        Schema::dropIfExists('users');

        Schema::create('users', function (Blueprint $table) {
            $table->increments('id');
            $table->integer('user_id')->nullable(false);
            $table->index('user_id'); //インデックス
            $table->string('email')->unique;
            $table->string('password');
            $table->timestamps();
        });

        DB::insert('insert into users (id, user_id, email, password) select id, 1, email, password from users_temp');

        Schema::dropIfExists('users_temp');
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('users_temp');
    }
}

参考情報

元のマイグレートファイル。

<?php

use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class CreateUsersTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('users', function (Blueprint $table) {
            $table->increments('id');
            $table->string('email')->unique;
            $table->string('password');
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('users');
    }
}

シーダ。

<?php

use Illuminate\Database\Seeder;
use Illuminate\Support\Facades\DB;
use Carbon\Carbon;

class UsersSeeder extends Seeder
{
    /**
     * Run the database seeds.
     *
     * @return void
     */
    public function run()
    {
        $now = Carbon::now();

        $password = 'password';

        DB::table('users')
            ->insert([
                [
                    'id' => 1,
                    'email' => '1',
                    'password' => bcrypt($password),
                ],
                [
                    'id' => 2,
                    'email' => '2',
                    'password' => bcrypt($password),
                ],
            ]);


    }
}

結果

順番よろしい。

mysql> select * from `users`;
+----+---------+-----------------+--------------------------------------------------------------+------------+------------+
| id | user_id | email           | password                                                     | created_at | updated_at |
+----+---------+-----------------+--------------------------------------------------------------+------------+------------+
|  1 |       1 | 1 | $2y$10$g1FmUBd300LyUGApPjyC... | NULL       | NULL       |
|  2 |       1 | 2 | $2y$10$8i6RPMfr..fI7... | NULL       | NULL       |
+----+---------+-----------------+--------------------------------------------------------------+------------+------------+
2 rows in set (0.00 sec)

mysql> SHOW FULL COLUMNS FROM `users`;
+------------+------------------+-----------------+------+-----+---------+----------------+---------------------------------+---------+
| Field      | Type             | Collation       | Null | Key | Default | Extra          | Privileges                      | Comment |
+------------+------------------+-----------------+------+-----+---------+----------------+---------------------------------+---------+
| id         | int(10) unsigned | NULL            | NO   | PRI | NULL    | auto_increment | select,insert,update,references |         |
| user_id    | int(11)          | NULL            | NO   | MUL | NULL    |                | select,insert,update,references |         |
| email      | varchar(191)     | utf8_general_ci | NO   |     | NULL    |                | select,insert,update,references |         |
| password   | varchar(191)     | utf8_general_ci | NO   |     | NULL    |                | select,insert,update,references |         |
| created_at | timestamp        | NULL            | YES  |     | NULL    |                | select,insert,update,references |         |
| updated_at | timestamp        | NULL            | YES  |     | NULL    |                | select,insert,update,references |         |
+------------+------------------+-----------------+------+-----+---------+----------------+---------------------------------+---------+
6 rows in set (0.00 sec)

参考

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
What you can do with signing up
19
Help us understand the problem. What are the problem?