LoginSignup
4
4

More than 5 years have passed since last update.

LaravelのマイグレーションとMySQLの実行文を見比べてみた

Posted at

LaravelのマイグレーションとMySQLの実行文を見比べてみた。

Laravel 5.6 データベース:マイグレーションのカラム作成にあるメソッド実行してみた。
ただそれだけ。

環境

  • Laravel Framework 5.6.14
  • MySQL 5.7.21

結果

Laravel MySQL
bigIncrements('hoge_bigIncrements'); 'hoge_bigIncrements' bigint unsigned not null auto_increment primary key,
bigInteger('hoge_bigInteger'); 'hoge_bigInteger' bigint not null,
binary('hoge_binary'); 'hoge_binary' blob not null,
boolean('hoge_boolean'); 'hoge_boolean' tinyint(1) not null,
char('hoge_char', 100); 'hoge_char' char(100) not null,
date('hoge_date'); 'hoge_date' date not null,
dateTime('hoge_dateTime'); 'hoge_dateTime' datetime not null,
dateTimeTz('hoge_dateTimeTz'); 'hoge_dateTimeTz' datetime not null,
decimal('hoge_decimal', 8, 2); 'hoge_decimal' decimal(8, 2) not null,
double('hoge_double', 8, 2); 'hoge_double' double(8, 2) not null,
enum('hoge_enum', ['man', 'woman']); 'hoge_enum' enum('man', 'woman') not null,
float('hoge_float', 8, 2); 'hoge_float' double(8, 2) not null,
geometry('hoge_geometry'); 'hoge_geometry' geometry not null,
geometryCollection('hoge_geometryCollection'); 'hoge_geometryCollection' geometrycollection not null,
increments('hoge_increments'); 'hoge_increments' int unsigned not null auto_increment primary key,
integer('hoge_integer'); 'hoge_integer' int not null,
ipAddress('hoge_ipAddress'); 'hoge_ipAddress' varchar(45) not null,
json('hoge_json'); 'hoge_json' json not null,
jsonb('hoge_jsonb'); 'hoge_jsonb' json not null,
lineString('hoge_lineString'); 'hoge_lineString' linestring not null,
longText('hoge_longText'); 'hoge_longText' longtext not null,
macAddress('hoge_macAddress'); 'hoge_macAddress' varchar(17) not null,
mediumIncrements('hoge_mediumIncrements'); 'hoge_mediumIncrements' mediumint unsigned not null auto_increment primary key,
mediumInteger('hoge_mediumInteger'); 'hoge_mediumInteger' mediumint not null,
mediumText('hoge_mediumText'); 'hoge_mediumText' mediumtext not null,
morphs('hoge_morphs'); 'hoge_morphs_type' varchar(255) not null,
'hoge_morphs_id' bigint unsigned not null,
multiLineString('hoge_multiLineString'); 'hoge_multiLineString' multilinestring not null,
multiPoint('hoge_multiPoint'); 'hoge_multiPoint' multipoint not null,
multiPolygon('hoge_multiPolygon'); 'hoge_multiPolygon' multipolygon not null,
nullableMorphs('hoge_nullableMorphs'); 'hoge_nullableMorphs_type' varchar(255) null,
'hoge_nullableMorphs_id' bigint unsigned null,
nullableTimestamps(); 'created_at' timestamp null,
'updated_at' timestamp null,
point('hoge_point'); 'hoge_point' point not null,
polygon('hoge_polygon'); 'hoge_polygon' polygon not null,
rememberToken(); 'remember_token' varchar(100) null,
smallIncrements('hoge_smallIncrements'); 'hoge_smallIncrements' smallint unsigned not null auto_increment primary key,
smallInteger('hoge_smallInteger'); 'hoge_smallInteger' smallint not null,
softDeletes(); 'deleted_at' timestamp null,
softDeletesTz(); 'deleted_at' timestamp null,
string('hoge_string', 100); 'hoge_string' varchar(100) not null,
text('hoge_text'); 'hoge_text' text not null,
time('hoge_time'); 'hoge_time' time not null,
timeTz('hoge_timeTz'); 'hoge_timeTz' time not null,
timestamp('hoge_timestamp'); 'hoge_timestamp' timestamp not null,
timestampTz('hoge_timestampTz'); 'hoge_timestampTz' timestamp not null,
timestamps(); 'created_at' timestamp null,
'updated_at' timestamp null,
timestampsTz(); 'created_at' timestamp null,
'updated_at' timestamp null,
tinyIncrements('hoge_tinyIncrements'); 'hoge_tinyIncrements' tinyint unsigned not null auto_increment primary key,
tinyInteger('hoge_tinyInteger'); 'hoge_tinyInteger' tinyint not null,
unsignedBigInteger('hoge_unsignedBigInteger'); 'hoge_unsignedBigInteger' bigint unsigned not null,
unsignedDecimal('hoge_unsignedDecimal', 8, 2); 'hoge_unsignedDecimal' decimal(8, 2) unsigned not null,
unsignedInteger('hoge_unsignedInteger'); 'hoge_unsignedInteger' int unsigned not null,
unsignedMediumInteger('hoge_unsignedMediumInteger'); 'hoge_unsignedMediumInteger' mediumint unsigned not null,
unsignedSmallInteger('hoge_unsignedSmallInteger'); 'hoge_unsignedSmallInteger' smallint unsigned not null,
unsignedTinyInteger('hoge_unsignedTinyInteger'); 'hoge_unsignedTinyInteger' tinyint unsigned not null,
uuid('hoge_uuid'); 'hoge_uuid' char(36) not null,
year('hoge_year'); 'hoge_year' year not null

※SQL文のバッククォートはシングルクォートに置換しています。

実際のコード


開く

- Laravel
マイグレーションファイル
  public function up() {
    Schema::create('hoges', function (Blueprint $table) {
      $table->bigIncrements('hoge_bigIncrements');
      $table->bigInteger('hoge_bigInteger');
      $table->binary('hoge_binary');
      $table->boolean('hoge_boolean');
      $table->char('hoge_char', 100);
      $table->date('hoge_date');
      $table->dateTime('hoge_dateTime');
      $table->dateTimeTz('hoge_dateTimeTz');
      $table->decimal('hoge_decimal', 8, 2);
      $table->double('hoge_double', 8, 2);
      $table->enum('hoge_enum', ['man', 'woman']);
      $table->float('hoge_float', 8, 2);
      $table->geometry('hoge_geometry');
      $table->geometryCollection('hoge_geometryCollection');
      $table->increments('hoge_increments');
      $table->integer('hoge_integer');
      $table->ipAddress('hoge_ipAddress');
      $table->json('hoge_json');
      $table->jsonb('hoge_jsonb');
      $table->lineString('hoge_lineString');
      $table->longText('hoge_longText');
      $table->macAddress('hoge_macAddress');
      $table->mediumIncrements('hoge_mediumIncrements');
      $table->mediumInteger('hoge_mediumInteger');
      $table->mediumText('hoge_mediumText');
      $table->morphs('hoge_morphs');
      $table->multiLineString('hoge_multiLineString');
      $table->multiPoint('hoge_multiPoint');
      $table->multiPolygon('hoge_multiPolygon');
      $table->nullableMorphs('hoge_nullableMorphs');
      $table->nullableTimestamps();
      $table->point('hoge_point');
      $table->polygon('hoge_polygon');
      $table->rememberToken();
      $table->smallIncrements('hoge_smallIncrements');
      $table->smallInteger('hoge_smallInteger');
      $table->softDeletes();
      $table->softDeletesTz();
      $table->string('hoge_string', 100);
      $table->text('hoge_text');
      $table->time('hoge_time');
      $table->timeTz('hoge_timeTz');
      $table->timestamp('hoge_timestamp');
      $table->timestampTz('hoge_timestampTz');
      $table->timestamps();
      $table->timestampsTz();
      $table->tinyIncrements('hoge_tinyIncrements');
      $table->tinyInteger('hoge_tinyInteger');
      $table->unsignedBigInteger('hoge_unsignedBigInteger');
      $table->unsignedDecimal('hoge_unsignedDecimal', 8, 2);
      $table->unsignedInteger('hoge_unsignedInteger');
      $table->unsignedMediumInteger('hoge_unsignedMediumInteger');
      $table->unsignedSmallInteger('hoge_unsignedSmallInteger');
      $table->unsignedTinyInteger('hoge_unsignedTinyInteger');
      $table->uuid('hoge_uuid');
      $table->year('hoge_year');
    });
  }
  • MySQL
実行SQL
  create table `hoges` (
    `hoge_bigIncrements` bigint unsigned not null auto_increment primary key,
    `hoge_bigInteger` bigint not null,
    `hoge_binary` blob not null,
    `hoge_boolean` tinyint(1) not null,
    `hoge_char` char(100) not null,
    `hoge_date` date not null,
    `hoge_dateTime` datetime not null,
    `hoge_dateTimeTz` datetime not null,
    `hoge_decimal` decimal(8, 2) not null,
    `hoge_double` double(8, 2) not null,
    `hoge_enum` enum('man', 'woman') not null,
    `hoge_float` double(8, 2) not null,
    `hoge_geometry` geometry not null,
    `hoge_geometryCollection` geometrycollection not null,
    `hoge_increments` int unsigned not null auto_increment primary key,
    `hoge_integer` int not null,
    `hoge_ipAddress` varchar(45) not null,
    `hoge_json` json not null,
    `hoge_jsonb` json not null,
    `hoge_lineString` linestring not null,
    `hoge_longText` longtext not null,
    `hoge_macAddress` varchar(17) not null,
    `hoge_mediumIncrements` mediumint unsigned not null auto_increment primary key,
    `hoge_mediumInteger` mediumint not null,
    `hoge_mediumText` mediumtext not null,
    `hoge_morphs_type` varchar(255) not null,
    `hoge_morphs_id` bigint unsigned not null,
    `hoge_multiLineString` multilinestring not null,
    `hoge_multiPoint` multipoint not null,
    `hoge_multiPolygon` multipolygon not null,
    `hoge_nullableMorphs_type` varchar(255) null,
    `hoge_nullableMorphs_id` bigint unsigned null,
    `created_at` timestamp null,
    `updated_at` timestamp null,
    `hoge_point` point not null,
    `hoge_polygon` polygon not null,
    `remember_token` varchar(100) null,
    `hoge_smallIncrements` smallint unsigned not null auto_increment primary key,
    `hoge_smallInteger` smallint not null,
    `deleted_at` timestamp null,
    `deleted_at` timestamp null,
    `hoge_string` varchar(100) not null,
    `hoge_text` text not null,
    `hoge_time` time not null,
    `hoge_timeTz` time not null,
    `hoge_timestamp` timestamp not null,
    `hoge_timestampTz` timestamp not null,
    `created_at` timestamp null,
    `updated_at` timestamp null,
    `created_at` timestamp null,
    `updated_at` timestamp null,
    `hoge_tinyIncrements` tinyint unsigned not null auto_increment primary key,
    `hoge_tinyInteger` tinyint not null,
    `hoge_unsignedBigInteger` bigint unsigned not null,
    `hoge_unsignedDecimal` decimal(8, 2) unsigned not null,
    `hoge_unsignedInteger` int unsigned not null,
    `hoge_unsignedMediumInteger` mediumint unsigned not null,
    `hoge_unsignedSmallInteger` smallint unsigned not null,
    `hoge_unsignedTinyInteger` tinyint unsigned not null,
    `hoge_uuid` char(36) not null,
    `hoge_year` year not null
  ) default character set utf8mb4 collate utf8mb4_unicode_ci)


まとめ

で?って感じですが。
~TzとかbooleanとかはPostgreSQLとかでやったらまた違うんだろうなーとか思いました。
ちなみに上記のコードは通らないです。PKやcreated_atやupdated_atやdeleted_at何個あるのよって話です。
おしまい。

4
4
0

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
  3. You can use dark theme
What you can do with signing up
4
4