在MySQL中使用JSON

sql数据库在灵活性方面往往有些欠缺,他们的设计远比看起来的复杂,sql数据库强调结构,这就是它被称为结构化查询语言的原因。

与之相反的,我们有NoSQL数据库,也称为schema-less数据库,鼓励灵活性。在schema-less数据库中,没有强制的结构限制,只有需要保存的数据。

尽管每个工具都有它适用的场景,但有的时候需要将他们结合起来使用。

如果要使数据库一部分结构化,而让其他部分变得灵活,那该怎么办?
MySQL version 5.7.8 引入了一个JSON数据类型,恰好能实现这个需求。

在本教程中,您将学习:

  1. 如何使用JSON字段设计数据库表。
  2. 使用 MYSQL 中各种基于 JSON 的可用的方法对 json 数据进行增、删、改、查。
  3. 如何使用 Laravel 框架的 Eloquent ORM 处理 json 类型的字段。

为什么使用 JSON

您可能会有这样的疑惑,在引入 json 数据类型之前,mysql 不是工作的好好的吗?为什么还要使用 json ?

您可以从下面的例子中找到答案:

相关课程: Getting Started with JavaScript for Web Development

假设您正在构建一个web应用程序,您必须在数据库中保存用户的配置/首选项。

通常,您可以使用iduser_idkeyvalue fields创建一个单独的数据库表,或者把它保存为格式化的字符串,您可以在运行时进行解析。

然而,这对于用户量小的时候来说是很有效的。如果您有上千个用户并且每个用户会有 5 个配置项,那么您将看到一个包含 5 千条记录的表,这些记录处理了应用程序的一个非常小的特性。

或者,如果您使用的是保存格式化的字符串的方法,那么只会使服务器负载额外的代码。

在这样的场景中使用JSON数据类型字段来保存用户的配置,可以节省数据库表的空间,并减少记录的数量,这些记录被单独保存,与用户的数量相同。

还有一个好处就是,您不必编写任何JSON解析代码,ORM 或 sql 运行时会负责处理它。

数据库设计

在我们开始使用 MySQL 中炫酷的 JSON 之前,我们需要一个示例数据库。

所以,让我们先来设计一下数据库。

我们将考虑一个在线商店的用例,它拥有多个品牌和多种电子产品。

由于不同的电子产品有不同的属性(比较Macbook和真空吸尘器),买家感兴趣的是,通常使用实体——属性——价值模型(EAV)模式。

然而,由于我们现在可以选择使用JSON数据类型,所以我们将放弃EAV。

首先,我们的数据库将被命名为e_store,并且有三个表,分别命名为 brandscategoriesproducts

我们的brandscategories表将非常相似,每个表都有一个id和一个name字段。

CREATE DATABASE IF NOT EXISTS `e_store`
DEFAULT CHARACTER SET utf8
DEFAULT COLLATE utf8_general_ci;

SET default_storage_engine = INNODB;

CREATE TABLE `e_store`.`brands`(
    `id` INT UNSIGNED NOT NULL auto_increment ,
    `name` VARCHAR(250) NOT NULL ,
    PRIMARY KEY(`id`)
);

CREATE TABLE `e_store`.`categories`(
    `id` INT UNSIGNED NOT NULL auto_increment ,
    `name` VARCHAR(250) NOT NULL ,
    PRIMARY KEY(`id`)
);

这两个表的目标是存放产品类别和提供这些产品的品牌。

当我们在做这件事的时候,让我们继续,将一些数据放入这些表中,以便以后使用。

/* Brands */
INSERT INTO `e_store`.`brands`(`name`)
VALUES
    ('Samsung');

INSERT INTO `e_store`.`brands`(`name`)
VALUES
    ('Nokia');

INSERT INTO `e_store`.`brands`(`name`)
VALUES
    ('Canon');

/* Types of electronic device */
INSERT INTO `e_store`.`categories`(`name`)
VALUES
    ('Television');

INSERT INTO `e_store`.`categories`(`name`)
VALUES
    ('Mobilephone');

INSERT INTO `e_store`.`categories`(`name`)
VALUES
    ('Camera');

接下来,是本教程的业务领域。

我们将创建一个带有idnamebrand_idcategory_idattributes字段的产品表products

CREATE TABLE `e_store`.`products`(
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
    `name` VARCHAR(250) NOT NULL ,
    `brand_id` INT UNSIGNED NOT NULL ,
    `category_id` INT UNSIGNED NOT NULL ,
    `attributes` JSON NOT NULL ,
    PRIMARY KEY(`id`) ,
    INDEX `CATEGORY_ID`(`category_id` ASC) ,
    INDEX `BRAND_ID`(`brand_id` ASC) ,
    CONSTRAINT `brand_id` FOREIGN KEY(`brand_id`) REFERENCES `e_store`.`brands`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE ,
    CONSTRAINT `category_id` FOREIGN KEY(`category_id`) REFERENCES `e_store`.`categories`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE
);

我们的表定义指定了brand_idcategory_id字段的外键约束,指定它们分别引用brandscategories表。我们还规定,不应该允许引用的行删除,如果更新,更改也应该反映在参考字段中。

attributes字段的列类型被声明为 JSON,这是 MySQL 中支持的原生数据类型。这允许我们在attributes字段中使用 MySQL 中的各种 JSON 相关结构。

这是我们创建的数据库的实体关系图。

我们的数据库设计在效率和准确性方面并不是最好的。在产品表中没有价格列,我们可以将一个产品放到多个类别中。然而,本教程的目的不是教授数据库设计,而是如何使用 MySQL 的 JSON 特性在单个表中对不同性质的对象进行建模。

增删改查操作

让我们看看如何在 JSON 字段中创建、读取、更新和删除数据。

创建

用 JSON 字段在数据库中创建记录非常简单。

您所要做的就是在 insert 语句中添加有效的 JSON 作为字段值。

/* Let's sell some televisions */
INSERT INTO `e_store`.`products`(
    `name` ,
    `brand_id` ,
    `category_id` ,
    `attributes`
)
VALUES(
    'Prime' ,
    '1' ,
    '1' ,
    '{"screen": "50 inch", "resolution": "2048 x 1152 pixels", "ports": {"hdmi": 1, "usb": 3}, "speakers": {"left": "10 watt", "right": "10 watt"}}'
);

INSERT INTO `e_store`.`products`(
    `name` ,
    `brand_id` ,
    `category_id` ,
    `attributes`
)
VALUES(
    'Octoview' ,
    '1' ,
    '1' ,
    '{"screen": "40 inch", "resolution": "1920 x 1080 pixels", "ports": {"hdmi": 1, "usb": 2}, "speakers": {"left": "10 watt", "right": "10 watt"}}'
);

INSERT INTO `e_store`.`products`(
    `name` ,
    `brand_id` ,
    `category_id` ,
    `attributes`
)
VALUES(
    'Dreamer' ,
    '1' ,
    '1' ,
    '{"screen": "30 inch", "resolution": "1600 x 900 pixles", "ports": {"hdmi": 1, "usb": 1}, "speakers": {"left": "10 watt", "right": "10 watt"}}'
);

INSERT INTO `e_store`.`products`(
    `name` ,
    `brand_id` ,
    `category_id` ,
    `attributes`
)
VALUES(
    'Bravia' ,
    '1' ,
    '1' ,
    '{"screen": "25 inch", "resolution": "1366 x 768 pixels", "ports": {"hdmi": 1, "usb": 0}, "speakers": {"left": "5 watt", "right": "5 watt"}}'
);

INSERT INTO `e_store`.`products`(
    `name` ,
    `brand_id` ,
    `category_id` ,
    `attributes`
)
VALUES(
    'Proton' ,
    '1' ,
    '1' ,
    '{"screen": "20 inch", "resolution": "1280 x 720 pixels", "ports": {"hdmi": 0, "usb": 0}, "speakers": {"left": "5 watt", "right": "5 watt"}}'
);

您也可以使用内置的 JSON_OBJECT 函数,而不是自己处理 JSON 对象。

JSON_OBJECT函数接键/值对列表 JSON_OBJECT(key1, value1, key2, value2, ... key(n), value(n)) 并返回一个 JSON 对象。

/* Let's sell some mobilephones */
INSERT INTO `e_store`.`products`(
    `name` ,
    `brand_id` ,
    `category_id` ,
    `attributes`
)
VALUES(
    'Desire' ,
    '2' ,
    '2' ,
    JSON_OBJECT(
        "network" ,
        JSON_ARRAY("GSM" , "CDMA" , "HSPA" , "EVDO") ,
        "body" ,
        "5.11 x 2.59 x 0.46 inches" ,
        "weight" ,
        "143 grams" ,
        "sim" ,
        "Micro-SIM" ,
        "display" ,
        "4.5 inches" ,
        "resolution" ,
        "720 x 1280 pixels" ,
        "os" ,
        "Android Jellybean v4.3"
    )
);

INSERT INTO `e_store`.`products`(
    `name` ,
    `brand_id` ,
    `category_id` ,
    `attributes`
)
VALUES(
    'Passion' ,
    '2' ,
    '2' ,
    JSON_OBJECT(
        "network" ,
        JSON_ARRAY("GSM" , "CDMA" , "HSPA") ,
        "body" ,
        "6.11 x 3.59 x 0.46 inches" ,
        "weight" ,
        "145 grams" ,
        "sim" ,
        "Micro-SIM" ,
        "display" ,
        "4.5 inches" ,
        "resolution" ,
        "720 x 1280 pixels" ,
        "os" ,
        "Android Jellybean v4.3"
    )
);

INSERT INTO `e_store`.`products`(
    `name` ,
    `brand_id` ,
    `category_id` ,
    `attributes`
)
VALUES(
    'Emotion' ,
    '2' ,
    '2' ,
    JSON_OBJECT(
        "network" ,
        JSON_ARRAY("GSM" , "CDMA" , "EVDO") ,
        "body" ,
        "5.50 x 2.50 x 0.50 inches" ,
        "weight" ,
        "125 grams" ,
        "sim" ,
        "Micro-SIM" ,
        "display" ,
        "5.00 inches" ,
        "resolution" ,
        "720 x 1280 pixels" ,
        "os" ,
        "Android KitKat v4.3"
    )
);

INSERT INTO `e_store`.`products`(
    `name` ,
    `brand_id` ,
    `category_id` ,
    `attributes`
)
VALUES(
    'Sensation' ,
    '2' ,
    '2' ,
    JSON_OBJECT(
        "network" ,
        JSON_ARRAY("GSM" , "HSPA" , "EVDO") ,
        "body" ,
        "4.00 x 2.00 x 0.75 inches" ,
        "weight" ,
        "150 grams" ,
        "sim" ,
        "Micro-SIM" ,
        "display" ,
        "3.5 inches" ,
        "resolution" ,
        "720 x 1280 pixels" ,
        "os" ,
        "Android Lollypop v4.3"
    )
);

INSERT INTO `e_store`.`products`(
    `name` ,
    `brand_id` ,
    `category_id` ,
    `attributes`
)
VALUES(
    'Joy' ,
    '2' ,
    '2' ,
    JSON_OBJECT(
        "network" ,
        JSON_ARRAY("CDMA" , "HSPA" , "EVDO") ,
        "body" ,
        "7.00 x 3.50 x 0.25 inches" ,
        "weight" ,
        "250 grams" ,
        "sim" ,
        "Micro-SIM" ,
        "display" ,
        "6.5 inches" ,
        "resolution" ,
        "1920 x 1080 pixels" ,
        "os" ,
        "Android Marshmallow v4.3"
    )
);

请注意JSON_ARRAY函数,它在传递一组值时返回一个 JSON 数组。

如果你多次指定一个键,那么只保留第一个键/值对。这就是 MySQL 术语中的 JSON 规范化。另外,作为标准化的一部分,对象键被排序,键/值对之间的额外空白被删除。

我们可以用来创建 JSON 对象的另一个函数是JSON_MERGE函数。

JSON_MERGE函数接受多个JSON对象并产生单个聚合对象。

/* Let's sell some cameras */
INSERT INTO `e_store`.`products`(
    `name` ,
    `brand_id` ,
    `category_id` ,
    `attributes`
)
VALUES(
    'Explorer' ,
    '3' ,
    '3' ,
    JSON_MERGE(
        '{"sensor_type": "CMOS"}' ,
        '{"processor": "Digic DV III"}' ,
        '{"scanning_system": "progressive"}' ,
        '{"mount_type": "PL"}' ,
        '{"monitor_type": "LCD"}'
    )
);

INSERT INTO `e_store`.`products`(
    `name` ,
    `brand_id` ,
    `category_id` ,
    `attributes`
)
VALUES(
    'Runner' ,
    '3' ,
    '3' ,
    JSON_MERGE(
        JSON_OBJECT("sensor_type" , "CMOS") ,
        JSON_OBJECT("processor" , "Digic DV II") ,
        JSON_OBJECT("scanning_system" , "progressive") ,
        JSON_OBJECT("mount_type" , "PL") ,
        JSON_OBJECT("monitor_type" , "LED")
    )
);

INSERT INTO `e_store`.`products`(
    `name` ,
    `brand_id` ,
    `category_id` ,
    `attributes`
)
VALUES(
    'Traveler' ,
    '3' ,
    '3' ,
    JSON_MERGE(
        JSON_OBJECT("sensor_type" , "CMOS") ,
        '{"processor": "Digic DV II"}' ,
        '{"scanning_system": "progressive"}' ,
        '{"mount_type": "PL"}' ,
        '{"monitor_type": "LCD"}'
    )
);

INSERT INTO `e_store`.`products`(
    `name` ,
    `brand_id` ,
    `category_id` ,
    `attributes`
)
VALUES(
    'Walker' ,
    '3' ,
    '3' ,
    JSON_MERGE(
        '{"sensor_type": "CMOS"}' ,
        '{"processor": "Digic DV I"}' ,
        '{"scanning_system": "progressive"}' ,
        '{"mount_type": "PL"}' ,
        '{"monitor_type": "LED"}'
    )
);

INSERT INTO `e_store`.`products`(
    `name` ,
    `brand_id` ,
    `category_id` ,
    `attributes`
)
VALUES(
    'Jumper' ,
    '3' ,
    '3' ,
    JSON_MERGE(
        '{"sensor_type": "CMOS"}' ,
        '{"processor": "Digic DV I"}' ,
        '{"scanning_system": "progressive"}' ,
        '{"mount_type": "PL"}' ,
        '{"monitor_type": "LCD"}'
    )
);

在这些插入语句中有很多事情发生,可能会让人感到困惑。然而,它非常简单。

我们只是将对象传递给JSON_MERGE函数。其中一些是使用我们前面看到的JSON_OBJECT函数构造的,而另一些则作为有效的 JSON 字符串传递。

JSON_MERGE函数下,如果一个键重复多次,那么它的值就会被保留为数组输出。

我想下面的例子可以证明这个概念:

/* output: {"network": ["GSM", "CDMA", "HSPA", "EVDO"]} */
SELECT JSON_MERGE(
    '{"network": "GSM"}' ,
    '{"network": "CDMA"}' ,
    '{"network": "HSPA"}' ,
    '{"network": "EVDO"}'
);

我们可以通过JSON_TYPE确认我们的查询都是执行成功的,它会提供给我们字段值的类型。

/* output: OBJECT */
SELECT JSON_TYPE(attributes) FROM `e_store`.`products`;

查询

我们的数据库中已经有一些产品可以使用了。

对于不属于 JSON 类型的典型 MySQL 值,where子句是非常直接的。只要指定列、运算符和您需要处理的值即可。

从设计上来说,当使用 JSON 列时,这就行不通了。

/* 没有这么简单 */
SELECT
    *
FROM
    `e_store`.`products`
WHERE
    attributes = '{"ports": {"usb": 3, "hdmi": 1}, "screen": "50 inch", "speakers": {"left": "10 watt", "right": "10 watt"}, "resolution": "2048 x 1152 pixels"}';

当您希望在使用了 JSON 字段的数据表中进行搜索时,您应该熟悉路径表达式的概念。

路径表达式最简单的定义(想想 JQuery 选择器)是用来指定要处理的 JSON 文档的哪一部分。

第二个难题是JSON_EXTRACT函数,它接受路径表达式来浏览 JSON。

让我们假设,我们需要查找的是至少有一个USB和HDMI端口的电视机:

SELECT
    *
FROM
    `e_store`.`products`
WHERE
    `category_id` = 1
AND JSON_EXTRACT(`attributes` , '$.ports.usb') > 0
AND JSON_EXTRACT(`attributes` , '$.ports.hdmi') > 0;

JSON_EXTRACT函数的第一个参数是 json 字段的名称,第二个参数是 json 字段值属性中值对应的路径,使用 $ 符号开头,$.ports.usb$.ports.hdmi 分表表示 attributesport 下对应的 usbhdmi

一旦我们指定了目标键,就可以很容易地使用 MySQL 操作符,就像 >

此外,JSON_EXTRACT 函数还有别名 ->,您可以使用别名来使查询更具可读性。

将上面的查询修改如下:


SELECT
    *
FROM
    `e_store`.`products`
WHERE
    `category_id` = 1
AND `attributes` -> '$.ports.usb' > 0
AND `attributes` -> '$.ports.hdmi' > 0;

更新

了更新 JSON 值,我们将使用JSON_INSERTJSON_REPLACEJSON_SET函数。这些函数还需要一个路径表达式来指定要修改的 JSON 部分。

这些函数的输出是一个有效的应用了更改的 JSON 对象。

让我们修改所有的移动电话,都加入一个chipset的属性。

UPDATE `e_store`.`products`
SET `attributes` = JSON_INSERT(
    `attributes` ,
    '$.chipset' ,
    'Qualcomm'
)
WHERE
    `category_id` = 2;

$.chipset 这个路径表达式标识了chipset这个属性位于对象的根部。

让我们使用JSON_REPLACE函数更新chipset属性,使其更具描述性。

UPDATE `e_store`.`products`
SET `attributes` = JSON_REPLACE(
    `attributes` ,
    '$.chipset' ,
    'Qualcomm Snapdragon'
)
WHERE
    `category_id` = 2;

易如反掌!

最后,我们用JSON_SET函数来指定我们的电视是非常丰富多彩的。

UPDATE `e_store`.`products`
SET `attributes` = JSON_SET(
    `attributes` ,
    '$.body_color' ,
    'red'
)
WHERE
    `category_id` = 1;

所有这些功能看起来都是一样的,但它们的行为方式是不同的:

  • JSON_INSERT函数只会把属性添加到对象中,前提是需要添加的属性在对象中还不存在。
  • JSON_REPLACE函数只会更新对象中已经存在的属性。
  • JSON_SET函数在属性存在时对属性进行更新,否则就将属性添加到对象中。

删除

我们将看到两种类型的删除。

第一种方法是从 JSON 列中删除特定的 键/值,而第二个是使用 JSON 列删除行。

我们这样设定,我们将不再为相机提供mount_type属性并且希望删除所有相机的mount_type属性。

我们将使用JSON_REMOVE函数来完成这项工作,该函数根据路径表达式删除指定的键后返回更新后的 JSON 对象。

UPDATE `e_store`.`products`
SET `attributes` = JSON_REMOVE(`attributes` , '$.mount_type')
WHERE
    `category_id` = 3;

对于第二种情况,我们也不再提供包含 Jellybean 版本的安卓手机。

DELETE FROM `e_store`.`products`
WHERE `category_id` = 2
AND JSON_EXTRACT(`attributes` , '$.os') LIKE '%Jellybean%';

正如前面所述,使用特定属性需要使用JSON_EXTRACT函数,以便应用 LIKE 操作符,我们首先提取了 mobilephone 的 os 属性(在category_id的帮助下),并删除了包含字符串 Jellybean的所有记录。

一个初级的 web 应用程序

直接使用数据库的日子已经过去了。

如今,框架将开发人员与底层操作隔离开来,对于一个框架狂热者来说,不能够将他/她的数据库知识转换成一个对象关系映射器,这几乎是一种陌生的感觉。

为了不让这些开发人员感到伤心,并对他们在宇宙中的存在和目的感到疑惑,我们将研究如何在Laravel框架中处理 JSON 列的业务。

我们将只关注与我们的主题相关的部分,这些部分涉及到 JSON 列。关于Laravel框架的深入教程超出了本文的范围。

创建迁移

请确保正确配置了您的Laravel应用程序来使用 MySQL 数据库。

我们将分别为brandscategoriesproducts创建三个迁移。

$ php artisan make:migration create_brands
$ php artisan make:migration create_categories
$ php artisan make:migration create_products

create_brandscreate_categories迁移非常相似,对Laravel 开发人员来说是一种约定。

/* database/migrations/create_brands.php */

<?php

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

class CreateBrands extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('brands', function(Blueprint $table){
            $table->engine = 'InnoDB';
            $table->increments('id');
            $table->string('name');
            $table->timestamps();
        });
    }

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

/* database/migrations/create_categories.php */

<?php

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

class CreateCategories extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('categories', function(Blueprint $table){
            $table->engine = 'InnoDB';
            $table->increments('id');
            $table->string('name');
            $table->timestamps();
        });
    }

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

create_products迁移还将具有索引和外键的指令。


/* database/migrations/create_products */

<?php

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

class CreateProducts extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('products', function(Blueprint $table){
            $table->engine = 'InnoDB';
            $table->increments('id');
            $table->string('name');
            $table->unsignedInteger('brand_id');
            $table->unsignedInteger('category_id');
            $table->json('attributes');
            $table->timestamps();
            // foreign key constraints
            $table->foreign('brand_id')->references('id')->on('brands')->onDelete('restrict')->onUpdate('cascade');
            $table->foreign('category_id')->references('id')->on('categories')->onDelete('restrict')->onUpdate('cascade');
            // indexes
            $table->index('brand_id');
            $table->index('category_id');
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::drop('products');
    }
}
Pay attention to the $table

关注$table->json('attributes');迁移中的这个语句。

就像使用适当的数据类型命名的函数创建任何其他字段一样,我们使用 json 法创建了一个 JSON 名为attributes的 json 列。

此外,这只适用于支持 JSON 数据类型的数据库引擎。

比如旧版本的 MySQL 的引擎将无法执行这些迁移。

创建模型

除了关联之外,我们没有太多的必要来建立我们的模型,所以让我们快速地运行它们。

/* app/Brand.php */

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class Brand extends Model
{
    // A brand has many products
    public function products(){
        return $this->hasMany('Product')
    }
}

/* app/Category.php */

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class Category extends Model
{
    // A category has many products
    public function products(){
        return $this->hasMany('Product')
    }
}

/* app/Product.php */

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class Product extends Model
{
    // Cast attributes JSON to array
    protected $casts = [
        'attributes' => 'array'
    ];

    // Each product has a brand
    public function brand(){
        return $this->belongsTo('Brand');
    }

    // Each product has a category
    public function category(){
        return $this->belongsTo('Category');
    }
}

同样,我们的Product模型需要特别的提及。

$cast指定attributes类型为array,确保每当从数据库获取产品时,attributes 会被转换为一个相关联的数组。

我们稍后将看到在控制器中,它是如何帮助我们更新数据库记录的。

资源操作

创建产品

说到管理面板,创建一个产品的参数可能通过不同的路线进入,因为我们有很多产品类别。您可能还会有不同的视图来创建、编辑、显示和删除产品。

例如,添加相机的表单和添加手机的表单的字段是不同的,因此它们需要单独的视图。

此外,一旦你拥有了用户输入数据,你将会通过一个请求验证器来运行它(区分摄像头和移动电话)。

最后一步是通过Eloquent模型创建产品。

在本教程的其余部分中,我们将重点关注相机资源。其他产品可以使用类似的代码来处理。

假设我们正在使用表单提交的数据创建一个相机,下面是控制器中的方法:

// 在数据库中创建产品
// 使用表单数据
public function store(Request $request){
    // 创建对象并且设置属性
    $camera = new \App\Product();
    $camera->name = $request->name;
    $camera->brand_id = $request->brand_id;
    $camera->category_id = $request->category_id;
    $camera->attributes = json_encode([
        'processor' => $request->processor,
        'sensor_type' => $request->sensor_type,
        'monitor_type' => $request->monitor_type,
        'scanning_system' => $request->scanning_system,
    ]);
    // 保存到数据库
    $camera->save();
    // 查看成功创建的产品
    return view('product.camera.show', ['camera' => $camera]);
}

获取产品

回想一下我们在产品模型中声明的$cast数组。它将帮助我们转化属性为关联数组来读取和编辑产品。

// 从数据库获取单个产品
public function show($id){
    $camera = \App\Product::find($id);
    return view('product.camera.show', ['camera' => $camera]);
}

您的视图将以下列方式使用$camera变量:

<table>
    <tr>
        <td>Name</td>
        <td>{{ $camera->name }}</td>
    </tr>
    <tr>
        <td>Brand ID</td>
        <td>{{ $camera->brand_id }}</td>
    </tr>
    <tr>
        <td>Category ID</td>
        <td>{{ $camera->category_id }}</td>
    </tr>
    <tr>
        <td>Processor</td>
        <td>{{ $camera->attributes['processor'] }}</td>
    </tr>
    <tr>
        <td>Sensor Type</td>
        <td>{{ $camera->attributes['sensor_type'] }}</td>
    </tr>
    <tr>
        <td>Monitor Type</td>
        <td>{{ $camera->attributes['monitor_type'] }}</td>
    </tr>
    <tr>
        <td>Scanning System</td>
        <td>{{ $camera->attributes['scanning_system'] }}</td>
    </tr>
</table>

编辑产品

如前一节所示,您可以轻松地获取产品并将其传递给视图,在本例中是 edit 视图。

您可以使用产品变量在编辑页面上预先填充表单字段。

根据用户输入更新产品将非常类似于我们之前(新增产品时)看到的操作,只是不创建新产品,而是首先从数据库中获取它,然后再更新它。

基于 JSON 属性进行搜索

剩下要讨论的最后一个问题是使用Eloquent ORM查询 JSON 列。

如果您有一个搜索页面,它允许根据用户提供的规格对相机进行搜索,那么您可以使用以下代码来进行搜索:


// searches cameras by user provided specifications
public function search(Request $request){
    $cameras = \App\Product::where([
        ['attributes->processor', 'like', $request->processor],
        ['attributes->sensor_type', 'like', $request->sensor_type],
        ['attributes->monitor_type', 'like', $request->monitor_type],
        ['attributes->scanning_system', 'like', $request->scanning_system]
    ])->get();
    return view('product.camera.search', ['cameras' => $cameras]);
}

查询结果会以$cameras集合的形式传递给视图product.camera.search

删除一个产品

使用非 json 列属性,您可以通过指定 where 子句并调用 delete 方法来删除产品。

例如,使用 ID 删除产品:

\App\Product::where('id', $id)->delete();

对于 json 列,请使用单个或多个属性指定 where 子句,然后调用 delete 方法:

\App\Product::where('attributes->sensor_type', 'CMOS')->delete();

结束语

在 MySQL 中使用JSON列时,我们仅仅是接触了皮毛。

无论何时,当您需要将数据作为 键/值 对保存在单独的表中,或者为实体使用灵活的属性时,您应该考虑使用 JSON 数据类型字段,因为它可以极大地促进压缩数据库设计。

如果您有兴趣深入挖掘,MySQL文档是探索 JSON 概念的重要资源。

我希望你觉得这个教程很有趣,也很有见识。happy coding!


文章参考 Working with JSON in MySQL

Add a Comment

电子邮件地址不会被公开。 必填项已用*标注