MySQL的JSON类型

果冻味的椰子 2021-04-20

1272

为什么使用JSON

MySQL5.7增加了JSON数据类型的支持，当 MySQL 引入 JSON 数据类型前已经满足了多样的数据库需求时，为什么还需要使用 JSON 。

让我们举个例子说明

通常，我们可能会创建一个单独的数据库表，包含 id ， user_id ， key ，以及 value 字段，或者保存为一个格式化的字符串，在程序执行时再进行解析。

然而，这对于少量用户是很好的。如果我们有 1000 个用户和 5 个配置项，则需要查看一个具有 5000 条记录的表，但这只是我们应用非常小的一个功能。或者我们正在使用格式化的字符串，多余的解析代码只会增加服务器的负载。这种情况下，使用 JSON 数据类型来保存用户的配置可以节省数据库表空间，并将单独保存的记录数减少到与用户数相同。而且，我们还能够获得不用编写任何 JSON 的解析代码的好处，因为 ORM 或者语言运行库会进行相应的处理。

CRUD操作

首先我们创建一张表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) 
);

attributes 字段的列类型已经被声明为 JSON，这是现在 MySQL 中一种可用的本地化数据类型。它允许我们在 MySQL 里给 attributes 字段使用各种与 JSON 相关的数据结构。

插入

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

我们也可以使用内置的 JSON_OBJECT 函数来创建 JSON 对象。

JSON_OBJECT 函数接受 JSON_OBJECT(key1, value1, key2, value2, ... key(n), value(n)) 形式的键值对列表来返回一个 JSON 对象。

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

JSON_ARRAY
函数接受到一组值会返回一个 JSON 数组。如果多次指定相同的键，只会保留第一个键值对。这是 MySQL 内部对 JSON 的规范。此外，作为规范的一部分，当对象被排序时，键值对之间的留白会被移除。

我们用来创建 JSON 对象的另一个函数是 JSON_MERGE
。JSON_MERGE
函数接受多个 JSON 对象，并生成一个单独的聚合对象。

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"}' ,
        JSON_OBJECT("mount_type" , "PL") ,
        '{"monitor_type": "LCD"}'
    )
);

在 JSON_MERGE
函数的情形下，如果一个键被重复添加多次，它的值会作为一个数组保留在输出中。

SELECT JSON_MERGE(
    '{"network": "GSM"}' ,
    '{"network": "CDMA"}' ,
    '{"network": "HSPA"}' ,
    '{"network": "EVDO"}'
);

读取

对于非 JSON 类型的典型 MySQL 值，where
子句是非常直接的。只要指定列，运算符，和需要处理的值就行了。然而当处理 JSON 列时，这样的 where
子句不起作用：

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字段作为查询条件时，应该使用路径表达式。

JSON_EXTRACT
函数，它接收一个路径表达式来做为读取 JSON 数据的参数。

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 函数的第一个参数是应用路径表达式为 attributes 对应的列的 JSON。$ 符号表示要处理的对象。 $.ports.usb 和 $.ports.hdmi 路径表达式可以分别翻译为 “获取 ports 下的 usb 的值” 和 “获取 ports 下的 hdmi 的值”。

JSON_EXTRACT
函数还有一个别名 ->
，我们可以用这个别名来使你的查询语句可读性更好。

修改我们之前的查询语句。

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

更新

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

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_INSERT 函数只有当属性不存在的时候，它才会将这个属性添加到对象中。

JSON_REPLACE 函数只有在对象中找到该属性才会替换该属性。

JSON_SET 函数，如果在对象中没有找到这个属性，就会添加这个属性到对象中，如果对象中有这个属性了，就会替换掉原来的属性。

删除

关于删除操作我们将会关注两个点。

第一个点是从 JSON 列中删除某个键 / 值（JSON_REMOVE），第二个点是从 JSON 列中删除某些行（JSON_EXTRACT）。

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

删除了属性中mount_type的键值对

mysql

文章转载自果冻味的椰子，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

MySQL的JSON类型

为什么使用JSON

CRUD操作

插入

读取

更新

删除

评论