Tag: migration

为了laravel 数据库迁移 migration使用

按照某个model构建 mirgration

php artisan make:migration create_flights_table --create=flights

按照现有的数据库 dump一下

php artisan schema:dump

// Dump the current database schema and prune all existing migrations...
php artisan schema:dump --prune

migration文件例子

<?php

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

class CreateFlightsTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('flights', function (Blueprint $table) {
            $table->id();
            $table->string('name');
            $table->string('airline');
            $table->timestamps();
        });
    }

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

执行migration

php artisan migrate

scheme详解

建表

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

Schema::create('users', function (Blueprint $table) {
    $table->id();
    $table->string('name');
    $table->string('email');
    $table->timestamps();
});

检查

if (Schema::hasTable('users')) {
    // The "users" table exists...
}

if (Schema::hasColumn('users', 'email')) {
    // The "users" table exists and has an "email" column...
}

指定连接

Schema::connection('sqlite')->create('users', function (Blueprint $table) {
    $table->id();
});

指定表储存引擎

Schema::create('users', function (Blueprint $table) {
    $table->engine = 'InnoDB';

    // ...
});

指定字符集

Schema::create('users', function (Blueprint $table) {
    $table->charset = 'utf8mb4';
    $table->collation = 'utf8mb4_unicode_ci';

    // ...
});

更改字段（和创建字段一样）

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

Schema::table('users', function (Blueprint $table) {
    $table->integer('votes');
});

表改名

Schema::rename($from, $to);

删除表

Schema::drop('users');

Schema::dropIfExists('users');

所有的字段类型

bigIncrements bigInteger binary boolean char dateTimeTz dateTime date decimal double enumfloat foreignId geometryCollection geometry id increments integer ipAddress json jsonb lineString longText macAddress mediumIncrements mediumInteger mediumText morphs multiLineString multiPoint multiPolygon nullableMorphs nullableTimestamps nullableUuidMorphs point polygon rememberToken set smallIncrements smallInteger softDeletesTz softDeletes stringtext timeTz time timestampTz timestamp timestampsTz timestampstinyIncrements tinyInteger unsignedBigInteger unsignedDecimal unsignedInteger unsignedMediumInteger unsignedSmallInteger unsignedTinyInteger uuidMorphs uuid year

更改字段

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

字段改名

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

舍弃字段

Schema::table('users', function (Blueprint $table) {
    $table->dropColumn('votes');
});

Schema::table('users', function (Blueprint $table) {
    $table->dropColumn(['votes', 'avatar', 'location']);
});

Command	Description
$table->dropMorphs('morphable');	Drop the morphable_id and morphable_type columns.
$table->dropRememberToken();	Drop the remember_token column.
$table->dropSoftDeletes();	Drop the deleted_at column.
$table->dropSoftDeletesTz();	Alias of dropSoftDeletes() method.
$table->dropTimestamps();	Drop the created_at and updated_at columns.
$table->dropTimestampsTz();	Alias of dropTimestamps() method.

索引约束

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

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

$table->unique('email');

$table->index(['account_id', 'created_at']);

$table->unique('email', 'unique_email');

Command	Description
$table->primary('id');	Adds a primary key.
$table->primary(['id', 'parent_id']);	Adds composite keys.
$table->unique('email');	Adds a unique index.
$table->index('state');	Adds an index.
$table->spatialIndex('location');	Adds a spatial index (except SQLite).

更改索引

$table->renameIndex('from', 'to')

删除索引（约束）

Command	Description
$table->dropPrimary('users_id_primary');	Drop a primary key from the “users” table.
$table->dropUnique('users_email_unique');	Drop a unique index from the “users” table.
$table->dropIndex('geo_state_index');	Drop a basic index from the “geo” table.
$table->dropSpatialIndex('geo_location_spatialindex');	Drop a spatial index from the “geo” table (except SQLite).

外键约束

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

Schema::table('posts', function (Blueprint $table) {
    $table->unsignedBigInteger('user_id');

    $table->foreign('user_id')->references('id')->on('users');
});

//自动推测
Schema::table('posts', function (Blueprint $table) {
    $table->foreignId('user_id')->constrained();
});

Schema::table('posts', function (Blueprint $table) {
    $table->foreignId('user_id')->constrained('users');
});

外键级联

$table->foreignId('user_id')
      ->constrained()
      ->onUpdate('cascade')
      ->onDelete('cascade');

删除外键

$table->dropForeign(['user_id']);