之所以要实现 Schemaless,主要是因为在线 DDL 有很多痛点,关于这一点,我在以前已经写过文章,没看过的不妨看看「 史上最LOW的在线DDL解决方案 」,不过那篇文章主要以介绍为主,并没有涉及具体的实现,所以我写了一个 Laravel 的例子。
首先创建测试用的 users 表,并且添加虚拟字段 name、address、level:
mysql> CREATE TABLE users ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, created_at timestamp null, updated_at timestamp null, data JSON NOT NULL, PRIMARY KEY(id) ); mysql> ALTER TABLE users add name VARCHAR(100) AS (JSON_UNQUOTE(JSON_EXTRACT(data, '$.name'))) AFTER id; mysql> ALTER TABLE users add address VARCHAR(100) AS (JSON_UNQUOTE(JSON_EXTRACT(data, '$.address'))) AFTER name; mysql> ALTER TABLE users add level INT UNSIGNED AS (JSON_EXTRACT(data, '$.level')) AFTER name;
然后是核心代码 Schemaless.php,以 trait 的方式实现:
<?php namespace App; trait Schemaless { public function getDirty() { $dirty = collect(parent::getDirty()); $keys = $dirty->keys()->map(function($key) { if (in_array($key, $this->virtual)) { $key = $this->getDataColumn() . '->' . $key; } return $key; }); return $keys->combine($dirty)->all(); } public function save(array $options = []) { if (!$this->exists) { $this->reviseRawAttributes(); } return parent::save($options); } public function getDataColumn() { static $column; if ($column === null) { $column = defined('static::DATA') ? static::DATA : 'data'; } return $column; } private function reviseRawAttributes() { $attributes = collect($this->getAttributes()); $virtual = $attributes->only($this->virtual); $attributes = $attributes->diffKeys($virtual)->merge([ $this->getDataColumn() => json_encode($virtual->all()), ]); $this->setRawAttributes($attributes->all()); } }
接着是 Model 实现 User.php,里面激活了 schemaless,并设置了虚拟字段:
<?php namespace App; use Illuminate/Database/Eloquent/Model; class User extends Model { use Schemaless; protected $virtual = ['name', 'address', 'level']; }
最后是 Controller 实现 UsersController.php,里面演示了如何创建和修改:
<?php namespace App/Http/Controllers; use Illuminate/Http/Request; use App/User; class UsersController extends Controller { public function __construct(User $user) { $this->user = $user; } public function store() { $user = $this->user; $user->name = '老王'; $user->address = '东北'; $user->level = 1; $user->save(); } public function update() { $user = $this->user->find(1); $user->address = '北京'; $user->save(); } }
以后建表的时候,除了主键 id,时间 created_at、updated_at 等少数几个系统字段,其他的数据都可以划到虚拟字段里去,不管表多大,随时随地都可以增减字段。