在MySQL中使用JSON

最新推荐文章于 2024-05-15 10:46:25 发布

culiu9261

最新推荐文章于 2024-05-15 10:46:25 发布

阅读量437

点赞数

文章标签：数据库 python java php mysql

原文链接：https://scotch.io/tutorials/working-with-json-in-mysql

版权

SQL databases tend to be rigid.

SQL数据库趋于僵化。

If you have worked with them, you would agree that database design though it seems easier, is a lot trickier in practice. SQL databases believe in structure, that is why it's called structured query language.

如果您与他们合作过，您将同意数据库设计虽然看起来更容易，但是在实践中却要复杂得多。 SQL数据库相信结构，这就是为什么它被称为结构化查询语言。

On the other side of the horizon, we have the NoSQL databases, also called schema-less databases that encourage flexibility. In schema-less databases, there is no imposed structural restriction, only data to be saved.

另一方面，我们拥有NoSQL数据库，也称为无模式数据库，可鼓励灵活性。在无模式数据库中，没有施加结构限制，只有要保存的数据。

Though every tool has it's use case, sometimes things call for a hybrid approach.

尽管每种工具都有用例，但有时还是需要一种混合方法。

What if you could structure some parts of your database and leave others to be flexible?

如果您可以结构化数据库的某些部分而让其他部分保持灵活性怎么办？

MySQL version 5.7.8 introduces a JSON data type that allows you to accomplish that.

MySQL 5.7.8版引入了JSON数据类型，您可以完成此操作。

In this tutorial, you are going to learn.

在本教程中，您将学习。

How to design your database tables using JSON fields.
如何使用JSON字段设计数据库表。
The various JSON based functions available in MYSQL to create, read, update, and delete rows.
MYSQL提供了各种基于JSON的功能来创建，读取，更新和删除行。
How to work with JSON fields using the Eloquent ORM in Laravel.
如何使用Laravel中的Eloquent ORM处理JSON字段。

为什么使用JSON ( Why Use JSON )

At this moment, you are probably asking yourself why would you want to use JSON when MySQL has been catering to a wide variety of database needs even before it introduced a JSON data type.

目前，您可能会问自己：当MySQL在引入JSON数据类型之前就已经满足了各种各样的数据库需求时，为什么要使用JSON。

The answer lies in the use-cases where you would probably use a make-shift approach.

答案在于用例中，您可能会使用权宜之计。

Let me explain with an example.

让我举例说明。

Suppose you are building a web application where you have to save a user's configuration/preferences in the database.

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

Generally, you can create a separate database table with the id, user_id, key, and value fields or save it as a formatted string that you can parse at runtime.

通常，您可以使用id ， user_id ， key和value字段创建一个单独的数据库表，或将其另存为可在运行时解析的格式化字符串。

However, this works well for a small number of users. If you have about a thousand users and five configuration keys, you are looking at a table with five thousand records that addresses a very small feature of your application.

但是，这对于少数用户来说效果很好。如果您有大约一千个用户和五个配置键，则您正在查看一个包含五千条记录的表，该表解决了应用程序的一个很小的功能。

Or if you are taking the formatted string route, extraneous code that only compounds your server load.

或者，如果您采用格式化的字符串路由，则多余的代码只会增加服务器的负载。

Using a JSON data type field to save a user's configuration in such a scenario can spare you a database table's space and bring down the number of records, which were being saved separately, to be the same as the number of users.

在这种情况下，使用JSON数据类型字段保存用户的配置可以节省数据库表的空间，并减少单独保存的记录数，使其与用户数相同。

And you get the added benefit of not having to write any JSON parsing code, the ORM or the language runtime takes care of it.

而且您获得了不必编写任何JSON解析代码的额外好处，ORM或语言运行时都可以处理它。

模式 ( The Schema )

Before we dive into using all the cool JSON stuff in MySQL, we are going to need a sample database to play with.

在深入研究MySQL中所有酷炫的JSON内容之前，我们将需要一个示例数据库来使用。

So, let's get our database schema out of the way first.

因此，让我们首先解决数据库架构问题。

We are going to consider the use case of an online store that houses multiple brands and a variety of electronics.

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

Since different electronics have different attributes(compare a Macbook with a Vacuumn Cleaner) that buyers are interested in, typically the Entity–attribute–value model (EAV) pattern is used.

由于不同的电子产品具有购买者感兴趣的不同属性（将Macbook与Vacuumn Cleaner进行比较），因此通常使用实体-属性-价值模型（EAV）模式。

However, since we now have the option to use a JSON data type, we are going to drop EAV.

但是，由于我们现在可以选择使用JSON数据类型，因此我们将删除EAV。

For a start, our database will be named e_store and has three tables only named, brands, categories, and products respectively.

一开始，我们的数据库将被命名为e_store ，有三个表只命名， brands ， categories和products分别。

Our brands and categories tables will be pretty similar, each having an id and a name field.

我们的brands和categories表将非常相似，每个表都有一个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`)
);

The objective of these two tables will be to house the product categories and the brands that provide these products.

这两个表的目的是容纳产品类别和提供这些产品的品牌。

While we are at it, let us go ahead and seed some data into these tables to use later.

在此过程中，让我们继续并将一些数据播种到这些表中，以备后用。

/* 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');

Next, is the business area of this tutorial.

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

We are going to create a products table with the id, name, brand_id, category_id, and attributes fields.

我们将使用id ， name ， brand_id ， category_id和attributes字段创建一个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
);

Our table definition specifies foreign key constraints for the brand_id and category_id fields, specifying that they reference the brands and categories table respectively. We have also specified that the referenced rows should not be allowed to delete and if updated, the changes should reflect in the references as well.

我们的表定义指定外键约束brand_id和category_id领域，指明所引用的brands和categories分别表。我们还指定了不允许删除引用的行，并且如果更新了，则更改也应反映在引用中。

The attributes field's column type has been declared to be JSON which is the native data type now available in MySQL. This allows us to use the various JSON related constructs in MySQL on our attributes field.

attributes字段的列类型已声明为JSON，这是MySQL现在可用的本机数据类型。这允许我们在attributes字段上使用MySQL中与JSON相关的各种构造。

Here is an entity relationship diagram of our created database.

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

Our database design is not the best in terms of efficiency and accuracy. There is no price column in the products table and we could do with putting a product into multiple categories. However, the purpose of this tutorial is not to teach database design but rather how to model objects of different nature in a single table using MySQL's JSON features.

就效率和准确性而言，我们的数据库设计并非最佳。 products表中没有价格列，我们可以将产品分为多个类别。但是，本教程的目的不是讲授数据库设计，而是讲授如何使用MySQL的JSON功能在单个表中对不同性质的对象进行建模。

CRUD操作 ( The CRUD Operations )

Let us look at how to create, read, update, and delete data in a JSON field.

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

创造 (Create)

Creating a record in the database with a JSON field is pretty simple.

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

All you need to do is add valid JSON as the field value in your insert statement.

您需要做的就是在插入语句中添加有效的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"}}'
);

The products table after adding televisions

Instead of laying out the JSON object yourself, you can also use the built-in JSON_OBJECT function.

除了自己布置JSON对象，您还可以使用内置的JSON_OBJECT函数。

The JSON_OBJECT function accepts a list of key/value pairs in the form JSON_OBJECT(key1, value1, key2, value2, ... key(n), value(n)) and returns a JSON object.

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"
    )
);

The products table after adding mobilephones

Notice the JSON_ARRAY function which returns a JSON array when passed a set of values.

请注意， JSON_ARRAY函数在传递一组值时会返回JSON数组。

If you specify a single key multiple times, only the first key/value pair will be retained. This is called normalizing the JSON in MySQL's terms. Also, as part of normalization, the object keys are sorted and the extra white-space between key/value pairs is removed.

如果您多次指定单个键，则仅保留第一个键/值对。这被称为用MySQL术语规范化JSON。另外，作为规范化的一部分，对对象键进行排序，并删除键/值对之间的多余空白。

Another function that we can use to create JSON objects is the JSON_MERGE function.

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

The JSON_MERGE function takes multiple JSON objects and produces a single, aggregate object.

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"}'
    )
);

There is a lot happening in these insert statements and it can get a bit confusing. However, it is pretty simple.

这些插入语句中发生了很多事情，可能会造成一些混乱。但是，这很简单。

We are only passing objects to the JSON_MERGE function. Some of them have been constructed using the JSON_OBJECT function we saw previously whereas others have been passed as valid JSON strings.

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

In case of the JSON_MERGE function, if a key is repeated multiple times, it's value is retained as an array in the output.

对于JSON_MERGE函数，如果键重复多次，则其值将作为数组保留在输出中。

A proof of concept is in order I suppose.

我认为是概念验证。

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

We can confirm all our queries were run successfully using the JSON_TYPE function which gives us the field value type.

我们可以使用提供字段值类型的JSON_TYPE函数来确认所有查询均已成功运行。

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

读 (Read)

Right, we have a few products in our database to work with.

正确，我们的数据库中有一些产品可以使用。

For typical MySQL values that are not of type JSON, a where clause is pretty straight-forward. Just specify the column, an operator, and the values you need to work with.

对于不是JSON类型的典型MySQL值，where子句非常简单。只需指定列，运算符和您需要使用的值即可。

Heuristically, when working with JSON columns, this does not work.

试探性地，当使用JSON列时，这不起作用。

/* It's not that simple */
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"}';

When you wish to narrow down rows using a JSON field, you should be familiar with the concept of a path expression.

当您希望使用JSON字段缩小行范围时，应熟悉路径表达式的概念。

The most simplest definition of a path expression(think JQuery selectors) is it's used to specify which parts of the JSON document to work with.

路径表达式最简单的定义（例如JQuery选择器）是用于指定要使用的JSON文档部分。

The second piece of the puzzle is the JSON_EXTRACT function which accepts a path expression to navigate through JSON.

难题的第二部分是JSON_EXTRACT函数，该函数接受路径表达式以浏览JSON。

Let us say we are interested in the range of televisions that have atleast a single USB and HDMI port.

假设我们对至少具有单个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;

The first argument to the JSON_EXTRACT function is the JSON to apply the path expression to which is the attributes column. The $ symbol tokenizes the object to work with. The $.ports.usb and $.ports.hdmi path expressions translate to "take the usb key under ports" and "take the hdmi key under ports" respectively.

JSON_EXTRACT函数的第一个参数是JSON，用于将路径表达式应用于attributes列。 $符号标记要使用的对象。 $.ports.usb和$.ports.hdmi路径表达式分别转换为“在端口下获取usb密钥”和“在端口下获取hdmi密钥”。

Once we have extracted the keys we are interested in, it is pretty simple to use the MySQL operators such as > on them.

一旦我们提取了感兴趣的键，就可以在它们上使用MySQL操作符，例如> 。

Also, the JSON_EXTRACT function has the alias -> that you can use to make your queries more readable.

另外， JSON_EXTRACT函数具有别名-> ，您可以使用该别名使查询更具可读性。

Revising our previous query.

修改我们之前的查询。

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

更新资料 (Update)

In order to update JSON values, we are going to use the JSON_INSERT, JSON_REPLACE, and JSON_SET functions. These functions also require a path expression to specify which parts of the JSON object to modify.

为了更新JSON值，我们将使用JSON_INSERT ， JSON_REPLACE和JSON_SET函数。这些函数还需要一个路径表达式来指定要修改的JSON对象的哪些部分。

The output of these functions is a valid JSON object with the changes applied.

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

Let us modify all mobilephones to have a chipset property as well.

让我们将所有手机都修改为具有芯片组属性。

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

The $.chipset path expression identifies the position of the chipset property to be at the root of the object.

$.chipset路径表达式将chipset属性的位置标识为对象的根。

Let us update the chipset property to be more descriptive using the JSON_REPLACE function.

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

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

Easy peasy!

十分简单！

Lastly, we have the JSON_SET function which we will use to specify our televisions are pretty colorful.

最后，我们具有JSON_SET函数，该函数将用于指定电视色彩鲜艳。

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

All of these functions seem identical but there is a difference in the way they behave.

所有这些功能看起来都是相同的，但是它们的行为方式有所不同。

The JSON_INSERT function will only add the property to the object if it does not exists already.

JSON_INSERT函数只会将该属性添加到该对象（如果尚不存在）。

The JSON_REPLACE function substitutes the property only if it is found.

JSON_REPLACE函数仅在找到属性后才替换该属性。

The JSON_SET function will add the property if it is not found else replace it.

如果JSON_SET该属性， JSON_SET函数将添加该属性，否则将其替换。

删除 (Delete)

There are two parts to deleting that we will look at.

删除将分为两部分。

The first is to delete a certain key/value from your JSON columns whereas the second is to delete rows using a JSON column.

第一种是从JSON列中删除某个键/值，而第二种是使用JSON列删除行。

Let us say we are no longer providing the mount_type information for cameras and wish to remove it for all cameras.

假设我们不再为摄像机提供mount_type信息， mount_type希望为所有摄像机删除它。

We will do it using the JSON_REMOVE function which returns the updated JSON after removing the specified key based on the path expression.

我们将使用JSON_REMOVE函数来完成此操作，该函数将根据路径表达式删除指定的键后返回更新的JSON。

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

Cameras after removing mount_type property

For the second case, we also do not provide mobilephones anymore that have the Jellybean version of the Android OS.

对于第二种情况，我们也不再提供具有Jellybean版本的Android OS的手机。

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

As stated previously, working with a specific attribute requires the use of the JSON_EXTRACT function so in order to apply the LIKE operator, we have first extracted the os property of mobilephones(with the help of category_id) and deleted all records that contain the string Jellybean.

如前所述，使用特定属性需要使用JSON_EXTRACT函数，因此为了应用LIKE运算符，我们首先（在category_id的帮助下）提取了手机的os属性，并删除了所有包含字符串Jellybean记录。

Web应用入门 ( A Primer for Web Applications )

The old days of directly working with a database are way behind us.

直接使用数据库的时代已经过去。

These days, frameworks insulate developers from lower-level operations and it almost feels alien for a framework fanatic not to be able to translate his/her database knowledge into an object relational mapper.

如今，框架将开发人员与低级操作隔离开来，并且对于狂热的框架无法将其数据库知识转换为对象关系映射器几乎感到陌生。

For the purpose of not leaving such developers heartbroken and wondering about their existence and purpose in the universe, we are going to look at how to go about the business of JSON columns in the Laravel framework.

为了不让这样的开发人员伤心欲绝，并想知道他们在宇宙中的存在和目的，我们将研究如何在Laravel框架中进行JSON列的业务。

We will only be focusing on the parts that overlap with our subject matter which deals with JSON columns. An in-depth tutorial on the Laravel framework is beyond the scope of this piece.

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

创建迁移 ( Creating the Migrations )

Make sure to configure your Laravel application to use a MySQL database.

确保将您的Laravel应用程序配置为使用MySQL数据库。

We are going to create three migrations for brands, categories, and products respectively.

我们将分别为brands ， categories和products创建三个迁移。

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

The create_brands and create_categories migrations are pretty similar and and a regulation for Laravel developers.

create_brands和create_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');
    }
}

The create_products migration will also have the directives for indexes and foreign keys.

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->json('attributes'); statement in the migration.

注意$table->json('attributes'); 迁移中的声明。

Just like creating any other table field using the appropriate data type named method, we have created a JSON column using the json method with the name attributes.

就像使用适当的数据类型named方法创建任何其他表字段一样，我们使用具有name attributes的json方法创建了JSON列。

Also, this only works for database engines that support the JSON data type.

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

Engines, such as older versions of MySQL will not be able to carry out these migrations.

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

创建模型 ( Creating the Models )

Other than associations, there is not much needed to set up our models so let's run through them quickly.

除了关联，建立模型的需求不大，因此让我们快速地进行研究。

/* 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');
    }
}

Again, our Product model needs a special mention.

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

The $casts array which has the key attributes set to array makes sure whenever a product is fetched from the database, it's attributes JSON is converted to an associated array.

$casts数组的关键attributes设置为array可确保每当从数据库中获取产品时，其attributes JSON都会转换为关联的数组。

We will see later in the tutorial how this facilitates us to update records from our controller actions.

我们将在本教程的后面部分看到这如何帮助我们从控制器操作中更新记录。

资源运作 ( Resource Operations )

创建产品 (Creating a Product)

Speaking of the admin panel, the parameters to create a product maybe coming in through different routes since we have a number of product categories. You may also have different views to create, edit, show, and delete a product.

说到管理面板，由于我们有许多产品类别，因此创建产品的参数可能会通过不同的途径出现。您可能还具有用于创建，编辑，显示和删除产品的不同视图。

For example, a form to add a camera requires different input fields than a form to add a mobilephone so they warrant separate views.

例如，添加相机的表单与添加手机的表单需要不同的输入字段，因此它们需要单独的视图。

Moreover, once you have the user input data, you will most probabaly run it through a request validator, separate for the camera, and the mobilephone each.

此外，一旦获得用户输入的数据，您最有可能通过一个请求验证器来运行它，该请求验证器与相机和手机分开。

The final step would be to create the product through Eloquent.

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

We will be focusing on the camera resource for the rest of this tutorial. Other products can be addressed using the code produced in a similar manner.

在本教程的其余部分中，我们将重点介绍相机资源。可以使用以类似方式生成的代码来寻址其他产品。

Assuming we are saving a camera and the form fields are named as the respective camera attributes, here is the controller action.

假设我们要保存一个摄像机，并且将表单字段命名为相应的摄像机属性，这是控制器操作。

// creates product in database
// using form fields
public function store(Request $request){
    // create object and set properties
    $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,
    ]);
    // save to database
    $camera->save();
    // show the created camera
    return view('product.camera.show', ['camera' => $camera]);
}

提取产品 (Fetching Products)

Recall the $casts array we declared earlier in the Product model. It will help us read and edit a product by treating attributes as an associative array.

回想一下我们在Product模型中之前声明的$casts数组。通过将属性视为关联数组，它将帮助我们阅读和编辑产品。

// fetches a single product
// from database
public function show($id){
    $camera = \App\Product::find($id);
    return view('product.camera.show', ['camera' => $camera]);
}

Your view would use the $camera variable in the following manner.

您的视图将以以下方式使用$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>

编辑产品 (Editing a Product)

As shown in the previous section, you can easily fetch a product and pass it to the view, which in this case would be the edit view.

如上一节中所示，您可以轻松获取产品并将其传递给视图，在这种情况下，该视图就是编辑视图。

You can use the product variable to pre-populate form fields on the edit page.

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

Updating the product based on the user input will be pretty similar to the store action we saw earlier, only that instead of creating a new product, you will fetch it first from the database before updating it.

根据用户输入来更新产品与我们之前看到的store操作非常相似，只是您无需创建新产品，而是先从数据库中获取产品，然后再进行更新。

基于JSON属性进行搜索 (Searching Based on JSON Attributes)

The last piece of the puzzle that remains to discuss is querying JSON columns using the Eloquent ORM.

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

If you have a search page that allows cameras to be searched based on their specifications provided by the user, you can do so with the following code.

如果您有一个搜索页面，可以根据用户提供的规格搜索摄像机，则可以使用以下代码进行搜索。

// 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]);
}

The retrived records will now be available to the product.camera.search view as a $cameras collection.

现在，检索到的记录将作为$cameras集合提供给product.camera.search视图。

删除产品 (Deleting a Product)

Using a non-JSON column attribute, you can delete products by specifying a where clause and then calling the delete method.

使用非JSON列属性，可以通过指定where子句然后调用delete方法来删除产品。

For example, in case of an ID.

例如，在ID的情况下。

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

For JSON columns, specify a where clause using a single or multiple attributes and then call the delete method.

对于JSON列，使用单个或多个属性指定where子句，然后调用delete方法。

// deletes all cameras with the sensor_type attribute as CMOS
\App\Product::where('attributes->sensor_type', 'CMOS')->delete();
}

幕布 ( Curtains )

We have barely scratched the surface when it comes to using JSON columns in MySQL.

在MySQL中使用JSON列时，我们几乎还没有摸索过。

Whenever you need to save data as key/value pairs in a separate table or work with flexible attributes for an entity, you should consider using a JSON data type field instead as it can heavily contribute to compressing your database design.

每当需要将数据作为键/值对保存在单独的表中或使用实体的灵活属性时，都应考虑使用JSON数据类型字段来代替，因为它可以极大地有助于压缩数据库设计。

If you are interested in diving deeper, the MySQL documentation is a great resource to explore JSON concepts futher.

如果您想更深入地学习， MySQL文档是进一步探索JSON概念的好资源。

I hope you found this tutorial interesting and knowledgeable. Until my next piece, happy coding!

我希望您发现本教程有趣且知识丰富。直到我的下一篇文章，祝您编程愉快！