说明:本文主要学习Schema Builder和Migration System的使用及相关原理。传统上在设计database时需要写大量的SQL语句,但Laravel提供了Schema Builder这个神器使得在设计database时使用面向对象方法来做,不需要写一行SQL,并且还提供了另一个神器Migration System,可以对database做版本控制,包括回滚上一次的迁移操作。本小系列主要分为上中下三篇。本篇主要学习使用Schema Builder来creating,dropping,updating tables;adding,removing,renaming columns;simple index,unique index,foreign keys
,同时也会学习相关源码来进一步了解Schema Builder。
开发环境: Laravel5.3 + PHP7
表的操作-tables
在设计database时需要创建、删除和更新表,Schema Builder类提供了一些methods来面向对象的执行这些操作,而不需要写一行SQL。在写Laravel程序时,也经常使用类似命令php artisan make:migration create_accounts_table --create=accounts
来做一个迁移类创建数据表,会在database/migrations
文件夹下得到类似如下的代码类:
use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;
class CreateAccountsTable extends Migration
{
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
Schema::create('accounts', function (Blueprint $table) {
$table->increments('id');
$table->timestamps();
});
}
/**
* Reverse the migrations.
*
* @return void
*/
public function down()
{
Schema::dropIfExists('accounts');
}
}
(1)creating tables
在执行php artisan migrate
命令时操作的是up()
方法中语句。在创建的迁移类CreateAccountsTable
中,Schema::create()
就是创建表的语句,并且第一个参数就是表的名字,第二个参数是个闭包,是操作columns的语句,并且参数是个Blueprint对象。为什么有这么奇怪的写法呢?
看下Schema Facade中getFacadeAccessor
的源码:
/**
* Get a schema builder instance for the default connection.
*
* @return \Illuminate\Database\Schema\Builder
*/
protected static function getFacadeAccessor()
{
// 这里'db'服务是在DatabaseServiceProvider中定义的,是DatabaseManager对象,且laravel默认connection是mysql
// 则返回的是MysqlBuilder,也就是\Illuminate\Database\Schema\Builder的子类
return static::$app['db']->connection()->getSchemaBuilder();
}
根据注释就知道Schema::create
就是等同于MysqlBuilder::create()
,看下源码:
// \Illuminate\Database\Schema\Builder
/**
* Create a new table on the schema.
*
* @param string $table
* @param \Closure $callback
* @return \Illuminate\Database\Schema\Blueprint
*/
public function create($table, Closure $callback)
{
/** @var \Illuminate\Database\Schema\Blueprint $blueprint */
$blueprint = $this->createBlueprint($table);
// 添加'create'命令
$blueprint->create();
// 执行闭包里的操作,也就是操作columns
$callback($blueprint);
$this->build($blueprint);
}
protected function createBlueprint($table, Closure $callback = null)
{
if (isset($this->resolver)) {
return call_user_func($this->resolver, $table, $callback);
}
return new Blueprint($table, $callback);
}
// Illuminate\Database\Schema\Blueprint
public function create()
{
return $this->addCommand('create');
}
protected function build(Blueprint $blueprint)
{
$blueprint->build($this->connection, $this->grammar);
}
create()
中的$callback($blueprint);
语句执行了闭包操作,并且闭包参数还是个Blueprint对象。最关键的方法时Blueprint对象的build()
方法,下文再聊具体细节。
当然,Schema Builder是可以在任意模块中使用的,如在路由中使用,执行https://localhost:8888/create_accounts
就可以创建一个accounts
表了:
Route::get('create_accounts', function () {
\Illuminate\Support\Facades\Schema::create('accounts', function(\Illuminate\Database\Schema\Blueprint $table)
{
$table->increments('id');
$table->string('name');
$table->string('number');
$table->tinyInteger('status');
$table->enum('source', ['bank account', 'credit card', 'investment account']);
$table->timestamps();
});
});
(2)dropping tables
Schema Builder提供了两个方法来删除表:drop(string $table)
和dropIfExists(string $table)
,参数是表名,dropIfExists()
表示只有在表存在才删除,所以dropIfExists()
比drop()
更优雅。看下两个方法的源码:
/**
* Drop a table from the schema.
*
* @param string $table
* @return \Illuminate\Database\Schema\Blueprint
*/
public function drop($table)
{
$blueprint = $this->createBlueprint($table);
$blueprint->drop();
$this->build($blueprint);
}
/**
* Drop a table from the schema if it exists.
*
* @param string $table
* @return \Illuminate\Database\Schema\Blueprint
*/
public function dropIfExists($table)
{
$blueprint = $this->createBlueprint($table);
$blueprint->dropIfExists();
$this->build($blueprint);
}
// Illuminate\Database\Schema\Blueprint
public function drop()
{
return $this->addCommand('drop');
}
public function dropIfExists()
{
return $this->addCommand('dropIfExists');
}
同样是使用了Blueprint对象来添加命令drop
和dropIfExists
。在路由中删除accounts
表:
Route::get('delete_accounts', function () {
\Illuminate\Support\Facades\Schema::dropIfExists('accounts');
// \Illuminate\Support\Facades\Schema::drop('accounts');
});
(3)updating tables
更新表的操作包括更新表名和更新表字段。
使用Schema::rename($from, $to)
方法来更新表名:
Route::get('rename_bank_accounts', function () {
\Illuminate\Support\Facades\Schema::rename('accounts', 'bank_accounts');
});
看下Schema的rename()
源码,同样是使用Blueprint对象添加rename
命令:
public function rename($from, $to)
{
$blueprint = $this->createBlueprint($from);
$blueprint->rename($to);
$this->build($blueprint);
}
// Illuminate\Database\Schema\Blueprint
public function rename($to)
{
return $this->addCommand('rename', compact('to'));
}
使用Schema::table()
方法来更新表字段值,如更新accounts
的number
字段,,不过如果该表中有字段类型为enum
就不支持修改:
Route::get('update_accounts', function () {
\Illuminate\Support\Facades\Schema::table('accounts', function(\Illuminate\Database\Schema\Blueprint $table)
{
$table->string('number', 50)->change();
});
});
同时,Schema还提供了几个有用的方法,如hasTable($table),hasColumn($table, $column),hasColumns($table, array $column),getColumnListing($table)
:
Route::get('get_column_listing', function () {
// if (\Illuminate\Support\Facades\Schema::hasTable('accounts'))
// if (\Illuminate\Support\Facades\Schema::hasColumn('accounts', 'name'))
if (\Illuminate\Support\Facades\Schema::hasColumns('accounts', ['name']))
{
// ['id', 'name', 'number', 'status', 'source', 'created_at', 'updated_at']
return \Illuminate\Support\Facades\Schema::getColumnListing('accounts');
}
});
字段的操作-column
(1)adding columns
设计database时需要添加columns,上文说过这段逻辑是在Schema::create(),Schema::table()
的闭包里执行的,利用Blueprint对象来依次添加每一个column字段属性和Mysql中数据类型对应,如:
\Illuminate\Support\Facades\Schema::create('accounts', function(\Illuminate\Database\Schema\Blueprint $table)
{
$table->string('name'); // => $this->addColumn('string', $column, compact('length'));
});
常用的Schema字段类型函数表如下:
Schema Column Type | MySQL Column Type |
---|---|
bigIncrements('id') |
id UNSIGNED BIGINT |
bigInteger('number') |
number BIGINT |
binary('data') |
data BLOB |
boolean('is_viewed') |
is_viewed BOOLEAN |
char('title', 50) |
title CHAR(50) |
date('created_at') |
created_at DATE |
dateTime('updated_at') |
updated_at DATETIME |
decimal('amount', 2, 2) |
amount DECIMAL(2,2) |
double('length', 10, 10) |
length DOUBLE(10, 10) |
enum('source', ['fund', 'equity']) |
source ENUM('fund', 'equity') |
float('width', 5, 5) |
width FLOAT(5, 5) |
json('options') |
options JSON |
string('content') |
content VARCHAR(255) |
text('description') |
description TEXT |
... |
... |
``
(2)removing columns
Schema提供了dropColumn()
方法来删除表中字段:
Route::get('drop_column', function () {
\Illuminate\Support\Facades\Schema::table('accounts', function(\Illuminate\Database\Schema\Blueprint $table)
{
$table->dropColumn(['number', 'status']);
});
});
(3)renaming columns
Schema提供了renameColumn()
来修改column名称,不过如果该表中有字段类型为enum
就不支持修改:
Route::get('rename_column', function () {
\Illuminate\Support\Facades\Schema::table('accounts', function(\Illuminate\Database\Schema\Blueprint $table)
{
$table->renameColumn('name', 'title');
});
});
索引操作-index
(1)simple index
Schema提供了index()
方法来给column加索引,且索引名称约定为table-name_column-name_index-type
:
Route::get('index_column', function () {
\Illuminate\Support\Facades\Schema::table('accounts', function(\Illuminate\Database\Schema\Blueprint $table)
{
$table->index('name');
});
});
同时,Schema提供了dropIndex('table-name_column-name_index')
来删除simple index。
(2)unique index
Schema提供了unique()
方法来给column加索引,且索引名称约定为table-name_column-name_index-type
:
Route::get('unique_column', function () {
\Illuminate\Support\Facades\Schema::table('accounts', function(\Illuminate\Database\Schema\Blueprint $table)
{
$table->unique(['title']);
});
});
同时,Schema提供了dropUnique('table-name_column-name_unique')
来删除unique index。
(3)foreign key
Schema提供了foreign()->reference()->on()
fluent api来设计foreign key,且索引名称约定为table-name_column-name_index-type
:
Route::get('foreign_key_column', function () {
\Illuminate\Support\Facades\Schema::create('bills', function(\Illuminate\Database\Schema\Blueprint $table)
{
$table->increments('id');
$table->unsignedInteger('account_id');
$table->float('amount', 5, 5);
$table->foreign('account_id')
->references('id')
->on('accounts')
->onUpdate('CASCADE')
->onDelete('CASCADE');
});
});
同时,Schema提供了dropForeign('table-name_column-name_foreign')
来删除foreign index。
总结:本篇主要学习下Laravel使用了Schema和Blueprint两个类来设计database,中篇将以Schema::create()为例仔细研究下源码是如何转换为SQL并执行SQL语句的,下篇聊下Migration System的使用及其原理。到时见。
**粗体** _斜体_ [链接](http://example.com) `代码` - 列表 > 引用
。你还可以使用@
来通知其他用户。