MySQL的JSON指北（2）：CURD之JSON

上篇《MySQL的JSON指北（1）：什么时候选择JSON存储？ 》讲什么时候选择JSON数据类型。本篇mysql的JSON操作

MySQL JSON数据写入

MySQL 对 JSON 的存储本质上还是字符串的存储操作。只是当定义为 JSON 类型之后内部会对数据再进行一些索引的创建方便后续的操作而已。所以添加 JSON 数据的时候需要使用字符串包装。

INSERT INTO user (`name`, `info`) VALUES('lilei', '{"sex": "male", "age": 18, "hobby": ["basketball", "football"], "score": [85, 90, 100]}');

或使用

INSERT INTO user (`name`, `info`) VALUES('hanmeimei', JSON_OBJECT(
    ->   'sex', 'female', 
    ->   'age', 18, 
    ->   'hobby', JSON_ARRAY('badminton', 'sing'), 
    ->   'score', JSON_ARRAY(90, 95, 100)
    -> ));

JSON_OBJECT用于创建JSON对象，JSON_ARRAY用于创建JSON数组。

查询数据

为了更好的支持 JSON 数据的操作，MySQL 提供了一些 JSON 数据操作类的方法。和查询操作相关的方法主要如下：

• JSON_EXTRACT()：根据 Path 获取部分 JSON 数据，使用方法 JSON_EXTRACT(json_doc, path[, path] ...)

• ->：JSON_EXTRACT() 的等价写法

• ->>：JSON_EXTRACT() 和 JSON_UNQUOTE() 的等价写法

• JSON_CONTAINS()：查询 JSON 数据是否在指定 Path 包含指定的数据，包含则返回1，否则返回0。使用方法 JSON_CONTAINS(json_doc, val[, path])

• JSON_CONTAINS_PATH()：查询是否存在指定路径，存在则返回1，否则返回0。one_or_all 只能取值 "one" 或 "all"，one 表示只要有一个存在即可，all 表示所有的都存在才行。使用方法 JSON_CONTAINS_PATH(json_doc, one_or_all, path[, path] ...)

• JSON_KEYS()：获取 JSON 数据在指定路径下的所有键值。使用方法 JSON_KEYS(json_doc[, path])，类似 JavaScript 中的 Object.keys() 方法。

• JSON_SEARCH()：查询包含指定字符串的 Paths，并作为一个 JSON Array 返回。查询的字符串可以用 LIKE 里的 '%' 或 '_' 匹配。使用方法 JSON_SEARCH(json_doc, one_or_all, search_str[, escape_char[, path] ...])，类似 JavaScript 中的 findIndex() 操作。

比如用户信息查询：

SELECT `name`, JSON_EXTRACT(`info`, '$.age') as `age`, `info`->'$.sex' as sex FROM `user`;

SELECT `name` FROM `user` WHERE JSON_CONTAINS(`info`, '"male"', '$.sex') AND JSON_SEARCH(`info`, 'one', 'basketball', null, '$.hobby');

SELECT `name` FROM `user` WHERE JSON_VALUE(`info`, '$.sex') = 'male' AND 'basketball' MEMBER OF(JSON_VALUE(`info`, '$.hobby'));

JSON_TYPE

JSON_TYPE(value)用于查询JSON值类型的内置函数。该函数返回一个表示给定JSON值基本类型的字符串。

1. "NULL"：如果路径指向的值是JSON null。

2. "OBJECT"：如果路径指向的是一个JSON对象（键值对集合）。

3. "ARRAY"：如果路径指向的是一个JSON数组。

4. "STRING"：如果路径指向的是一个JSON字符串。

5. "NUMBER"：如果路径指向的是一个JSON数字（包括整数和浮点数）。

6. "TRUE" 或 "FALSE"：如果路径指向的是布尔值 true 或 false。

修改数据

MySQL 提供的 JSON 操作函数中，和修改操作相关的方法主要如下：

• JSON_APPEND/JSON_ARRAY_APPEND：这两个名字是同一个功能的两种叫法，MySQL 5.7 的时候为 JSON_APPEND，MySQL 8 更新为 JSON_ARRAY_APPEND，并且之前的名字被废弃。该方法如同字面意思，给数组添加值。使用方法 JSON_ARRAY_APPEND(json_doc, path, val[, path, val] ...)

• JSON_ARRAY_INSERT：给数组添加值，区别于 JSON_ARRAY_APPEND() 它可以在指定位置插值。使用方法 JSON_ARRAY_INSERT(json_doc, path, val[, path, val] ...)

• JSON_INSERT/JSON_REPLACE/JSON_SET：以上三个方法都是对 JSON 插入数据的，他们的使用方法都为 JSON_[INSERT|REPLACE|SET](json_doc, path, val[, path, val] ...)，不过在插入原则上存在一些差别。

• JSON_INSERT：当路径不存在才插入

• JSON_REPLACE：当路径存在才替换

• JSON_SET：不管路径是否存在

• JSON_REMOVE：移除指定路径的数据。使用方法 JSON_REMOVE(json_doc, path[, path] ...)

比如

UPDATE `user` SET `info` = JSON_REPLACE(`info`, '$.age', 20) WHERE `name` = 'lilei';

DATE `user` SET `info` = JSON_ARRAY_APPEND(`info`, '$.hobby', 'badminton') WHERE `name` = 'lilei'

转载本站文章《MySQL的JSON指北（2）：CURD之JSON》,
请注明出处：https://www.zhoulujun.cn/html/DB/mysql/2024_1126_9350.html