为了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 --prunemigration文件例子
<?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 migratescheme详解
建表
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']);