MySQL之JSON数据类型

七⑧

已于 2024-10-22 10:04:22 修改

阅读量1.6k

点赞数 33

文章标签： java 数据库 mysql

于 2024-08-02 13:47:51 首次发布

本文链接：https://blog.csdn.net/s12345665432/article/details/140870153

版权

概述

mysql自5.7.8版本开始，就支持了json结构的数据存储和查询，这表明了mysql也在不断的学习和增加nosql数据库的有点。但mysql毕竟是关系型数据库，在处理json这种非结构化的数据时，还是比较别扭的。

创建一个JSON字段的表

首先先创建一个表，这个表包含一个json格式的字段：

CREATE TABLE table_name (
    id INT NOT NULL AUTO_INCREMENT, 
    json_col JSON,
    PRIMARY KEY(id)
);

上面的语句，主要注意json_col这个字段，指定的数据类型是JSON。

插入一条简单的JSON数据

INSERT INTO table_name (json_col) VALUES ('{"City": "Galle", "Description": "Best damn city in the world"}');

上面这个SQL语句，主要注意VALUES后面的部分，由于json格式的数据里，需要有双引号来标识字符串，所以，VALUES后面的内容需要用单引号包裹。

插入一条复杂的JSON数据

INSERT INTO table(col) VALUES('{"opening":"Sicilian","variations":["pelikan","dragon","najdorf"]}');

这地方，我们插入了一个json数组。主要还是注意单引号和双引号的问题。

修改JSON数据

之前的例子中，我们插入了几条JSON数据，但是如果我们想修改JSON数据里的某个内容，怎么实现了？比如我们向 variations 数组里增加一个元素，可以这样：

UPDATE myjson SET dict=JSON_ARRAY_APPEND(dict,'$.variations','scheveningen') WHERE id = 2;

这个SQL语句中，$符合代表JSON字段，通过.号索引到variations字段，然后通过JSON_ARRAY_APPEND函数增加一个元素。现在我们执行查询语句：

SELECT * FROM myjson

得到的结果是：

+----+-----------------------------------------------------------------------------------------------------------+

| id | dict

| +---+----------------------------------------------------------------------------------------------------------+

| 2 | {"opening": "Sicilian", "variations": ["pelikan", "dragon", "najdorf", "scheveningen"]} |

+----+-----------------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

关于MySQL中，JSON数据的获取方法，参照官方链接JSON Path Syntax

创建索引

MySQL的JSON格式数据不能直接创建索引，但是可以变通一下，把要搜索的数据单独拎出来，单独一个数据列，然后在这个字段上键一个索引。下面是官方的例子：

mysql> CREATE TABLE jemp (
-> c JSON,
-> g INT GENERATED ALWAYS AS (c->"$.id"),
-> INDEX i (g)
-> );

Query OK, 0 rows affected (0.28 sec)

mysql> INSERT INTO jemp (c) VALUES
> ('{"id": "1", "name": "Fred"}'), ('{"id": "2", "name": "Wilma"}'),
> ('{"id": "3", "name": "Barney"}'), ('{"id": "4", "name": "Betty"}');

Query OK, 4 rows affected (0.04 sec)
Records: 4 Duplicates: 0 Warnings: 0

mysql> SELECT c->>"$.name" AS name
> FROM jemp WHERE g > 2;

+--------+
| name |
+--------+
| Barney |
| Betty |
+--------+
2 rows in set (0.00 sec)

mysql> EXPLAIN SELECT c->>"$.name" AS name
> FROM jemp WHERE g > 2\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: jemp
partitions: NULL
type: range
possible_keys: i
key: i
key_len: 5
ref: NULL
rows: 2
filtered: 100.00
Extra: Using where
1 row in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS\G
*************************** 1. row ***************************

Level: Note
Code: 1003
Message: /* select#1 */ select json_unquote(json_extract(`test`.`jemp`.`c`,'$.name'))
AS `name` from `test`.`jemp` where (`test`.`jemp`.`g` > 2)
1 row in set (0.00 sec)

这个例子很简单，就是把JSON字段里的id字段，单独拎出来成字段g，然后在字段g上做索引，查询条件也是在字段g上。

字符串转JSON格式

把json格式的字符串转换成MySQL的JSON类型:

SELECT CAST('[1,2,3]' as JSON) ; SELECT CAST('{"opening":"Sicilian","variations":["pelikan","dragon","najdorf"]}' as JSON);

所有MYSQL JSON函数

Name	Description
JSON_APPEND()	Append data to JSON document
JSON_ARRAY()	Create JSON array
JSON_ARRAY_APPEND()	Append data to JSON document
JSON_ARRAY_INSERT()	Insert into JSON array-> Return value from JSON column after evaluating path; equivalent to JSON_EXTRACT().
JSON_CONTAINS()	Whether JSON document contains specific object at path
JSON_CONTAINS_PATH()	Whether JSON document contains any data at path
JSON_DEPTH()	Maximum depth of JSON document
JSON_EXTRACT()	Return data from JSON document->> Return value from JSON column after evaluating path and unquoting the result; equivalent to JSON_UNQUOTE(JSON_EXTRACT()).
JSON_INSERT()	Insert data into JSON document
JSON_KEYS()	Array of keys from JSON document
JSON_LENGTH()	Number of elements in JSON document
JSON_MERGE()	Merge JSON documents, preserving duplicate keys. Deprecated synonym for JSON_MERGE_PRESERVE()
JSON_MERGE_PRESERVE()	Merge JSON documents, preserving duplicate keys
JSON_OBJECT()	Create JSON object
JSON_QUOTE()	Quote JSON document
JSON_REMOVE()	Remove data from JSON document
JSON_REPLACE()	Replace values in JSON document
JSON_SEARCH()	Path to value within JSON document
JSON_SET()	Insert data into JSON document
JSON_TYPE()	Type of JSON value
JSON_UNQUOTE()	Unquote JSON value
JSON_VALID()	Whether JSON value is valid

MySQL之JSON函数解释：

1. JSON_APPEND()：
“JSON_APPEND()” 一个用于在 JSON 数据中添加内容的函数。它通常用于数据库操作中，例如在 MySQL 中，可以使用这个函数向已有的 JSON 数据添加新的键值对或者向 JSON 数组添加新的元素。具体的使用方式会根据不同的数据库和具体的需求而有所不同。

2. JSON_ARRAY()：
“_ARRAY()”通常是用于创建一个 JSON 数组的函数。在数据库操作中，比如在 MySQL 里，它可以接受一系列的值作为参数，并将这些值组合成一个 JSON 格式的数组。例如：JSON_ARRAY(1, 2, 3) 会生成一个包含 1、2、3 的 JSON 数组。

3. JSON_ARRAY_APPEND()：
“JSON_ARRAY_APPEND()” 是一个用于在 JSON 数组中添加元素的函数。
在数据库操作中，比如在 MySQL 里，它接受三个参数。第一个参数是要操作的 JSON 数组，第二个参数是指定添加位置的路径表达式，第三个参数是要添加的元素。通过这个函数，可以灵活地向指定位置的 JSON 数组添加新的内容。

4. JSON_ARRAY_INSERT()：
“JSON_ARRAY_INSERT()” 是一个在数据库操作中（如 MySQL ）使用的函数，用于向 JSON 数组中插入元素。

它通常接受多个参数，第一个参数是要操作的 JSON 数组，后面的参数依次指定插入的位置和要插入的元素。通过指定位置，可以将元素准确地插入到期望的位置，从而灵活地调整 JSON 数组的结构和内容。

5. JSON_CONTAINS()：
“JSON_CONTAINS()” 是一个在数据库操作中（比如在 MySQL 里）使用的函数。它主要用于检查一个 JSON 文档是否包含特定的元素或子文档。
例如，如果有一个 JSON 字段 json_data ，要检查其中是否包含特定的值或结构，可以使用 JSON_CONTAINS(json_data, '{"key": "value"}') 这样的形式。如果包含指定的内容，函数会返回 1 ，否则返回 0 。这个函数在处理和验证 JSON 数据的完整性和特定元素的存在性方面非常有用。

6. JSON_CONTAINS_PATH()：
“JSON_CONTAINS_PATH()” 是在数据库操作（如 MySQL ）中使用的函数。它用于检查 JSON 文档中是否存在指定路径的元素。
比如说，如果您有一个复杂的 JSON 数据结构，使用这个函数可以确定特定路径下是否存在您所关注的元素。通过指定路径表达式，能够更精确地查找和判断 JSON 数据中的特定部分是否符合预期。

7. JSON_DEPTH()：
“JSON_DEPTH()”是在数据库操作（比如 MySQL 中）使用的函数。它主要用于获取 JSON 文档的深度。
JSON 文档的深度指的是从根节点到最深叶子节点所经过的层数。例如，一个简单的 JSON 对象 {"key": "value"} 的深度为 1 ，而更复杂的结构如 {"key1": {"key2": "value"}} 的深度为 2 。通过“JSON_DEPTH()”函数可以方便地获取 JSON 文档的这种结构深度信息，这在处理和分析复杂的 JSON 数据时可能会很有用。

8. JSON_EXTRACT()：
“JSON_EXTRACT()” 是在数据库操作（例如在 MySQL 中）使用的函数。它的作用是从 JSON 文档中提取特定的部分。
打个比方，如果有一个 JSON 数据形如 {"name": "张三", "age": 25} ，如果您想提取出其中的 “name” 值，就可以使用 “JSON_EXTRACT()” 函数来实现。这个函数通常需要指定要提取的 JSON 数据以及提取的路径或表达式。

9. JSON_INSERT()：
“JSON_INSERT()”是数据库（如 MySQL ）中的一个函数，用于向 JSON 文档中插入数据。
比如说，假设有一个 JSON 字段 json_data ，原本的值是 {"name": "张三"} ，通过 JSON_INSERT(json_data, '$.age', 25) 这样的操作，就可以在这个 JSON 文档中插入新的键值对 {"name": "张三", "age": 25} 。

10. JSON_KEYS()：
“JSON_KEYS()” 是数据库操作（例如在 MySQL 中）使用的函数。它主要用于获取 JSON 文档中的所有键。
比如说，如果有一个 JSON 数据 {"name": "张三", "age": 25} ，使用 “JSON_KEYS()” 函数就可以得到这个 JSON 文档的键，即 ["name", "age"] 。这个函数在需要获取 JSON 数据的键信息时非常有用。

11. JSON_LENGTH()：
“JSON_LENGTH()”是在数据库操作（比如 MySQL 中）使用的函数。它用于获取 JSON 文档中的元素数量。
打个比方，如果有一个 JSON 数据 {"name": "张三", "age": 25, "city": "北京"} ，使用 “JSON_LENGTH()” 函数就能得到这个 JSON 文档中的元素数量，也就是 3 。

12. JSON_MERGE()：
“JSON_MERGE()”是数据库（例如 MySQL 中）使用的函数，它主要用于合并多个 JSON 文档。
打个比方，如果有两个 JSON 文档，一个是 {"name": "张三", "age": 25} ，另一个是 {"city": "北京", "job": "程序员"} ，使用“JSON_MERGE()”函数就可以将它们合并成一个，比如得到 {"name": "张三", "age": 25, "city": "北京", "job": "程序员"} 。这个函数在处理需要整合多个 JSON 数据的场景时会很有用。

13. JSON_MERGE_PRESERVE()：
“JSON_MERGE_PRESERVE()” 是数据库（比如在 MySQL 中）的一个函数。它的作用类似于 “JSON_MERGE()” ，也是用于合并多个 JSON 文档，但它会保留重复键的值。
打个比方，如果有两个 JSON 文档，一个是 {"name": "张三", "age": 25} ，另一个是 {"name": "李四", "city": "北京"} ，使用 “JSON_MERGE_PRESERVE()” 函数合并时，会同时保留两个 “name” 的值，可能得到类似 {"name": ["张三", "李四"], "age": 25, "city": "北京"} 的结果。这个函数在需要保留重复键值的 JSON 合并场景中较为适用。

14. JSON_OBJECT()：
“JSON_OBJECT()”是数据库（例如在 MySQL 中）使用的函数，用于创建一个新的 JSON 对象。
打个比方，如果您想要创建一个包含姓名和年龄信息的 JSON 对象，可以使用类似 JSON_OBJECT('name', '张三', 'age', 25) 这样的方式，得到的结果就类似于 {"name": "张三", "age": 25} 。这个函数在构建自定义的 JSON 数据时非常有用。

15. JSON_QUOTE()：
“JSON_QUOTE()”是数据库（比如在 MySQL 中）使用的函数，主要用于给一个值添加 JSON 格式的引号。
打个比方，如果有一个值是“张三”，使用“JSON_QUOTE(‘张三’)”函数，就会得到““张三””这样符合 JSON 格式的带引号的结果。这个函数在将普通值转换为符合 JSON 规范的带引号的值时很有用。

16. JSON_REMOVE()：
“JSON_REMOVE()”是数据库（例如在 MySQL 中）使用的函数，用于从 JSON 文档中移除指定的部分。
打个比方，如果有一个 JSON 文档 {"name": "张三", "age": 25, "city": "北京"} ，使用 “JSON_REMOVE(json_doc, ‘$.age’)” 这样的语句，就可以移除 “age” 这个部分，得到 {"name": "张三", "city": "北京"} 。这个函数在需要对 JSON 数据进行特定部分删除操作的场景中很有用。

17. JSON_REPLACE()：
“JSON_REPLACE()”是数据库（比如在 MySQL 中）使用的函数，用于替换 JSON 文档中的部分内容。
打个，如果有一个 JSON 文档 {"name": "张三", "age": 25} ，使用 “JSON_REPLACE(json_doc, ‘$.age’, 30)” 这样的语句，就可以将 “age” 的值从 25 替换为 30，得到 {"name": "张三", "age": 30} 。这个函数在需要对 JSON 数据中的特定部分进行值替换的场景中很有用。

18. JSON_SEARCH()：
“JSON_SEARCH()”是数据库（比如在 MySQL 中）使用的函数，主要用于在 JSON 文档中搜索指定的值。
打个比方，如果有一个 JSON 文档 {"name": "张三", "city": "北京", "hobbies": ["读书", "跑步"]} ，使用 “JSON_SEARCH(json_doc, ‘one’, ‘跑步’)” 这样的语句，就可以查找 “跑步” 这个值在文档中的位置。这个函数在需要从 JSON 数据中快速定位特定值的场景中很有用。

19. JSON_SET()：
“JSON_SET()”是数据库（例如在 MySQL 中）使用的函数，用于在 JSON 文档中设置指定的键值对
打个比方，如果有一个 JSON 文档 {"name": "张三", "age": 25} ，使用 “JSON_SET(json_doc, ‘$.city’, ‘上海’)” 这样的语句，就可以添加一个新的键值对 “city” : “上海” ，得到 {"name": "张三", "age": 25, "": "上海"} 。这个函数在需要向 JSON 数据中添加新的键值对的场景中非常实用。

20. JSON_TYPE()：
“JSON_TYPE()”是数据库（比如在 MySQL 中）使用的函数，用于获取 JSON 值的数据类型。
打个比方，如果有 JSON 值 {"name": "张三"} ，使用 “JSON_TYPE(json_value)” 这样的语句，就能知道这个值的数据类型。这个函数在需要确定 JSON 数据的具体类型的场景中很有用，能帮助开发者更准确地处理和操作 JSON 数据。

21. JSON_UNQUOTE()：
“JSON_UNQUOTE()”是数据库（比如在 MySQL 中）使用的函数，用于去除 JSON 字符串中的引号。
打个比方，如果有一个 JSON 字符串 '""' ，使用 “JSON_UNQUOTE(json_string)” 这样的语句，就可以得到张三。这个函数在需要获取去除引号后的 JSON 字符串值的场景中很有用。

22 JSON_VALID()：
“JSON_VALID()”是数据库（比如在 MySQL 中）使用的函数，用于判断给定的字符串是否为有效的 JSON 格式。
打个比方，如果有一个字符串 {"name": "张三", "age": 20} ，使用 “JSON_VALID(string)” 这样的语句，它会返回一个结果表明这个字符串是不是有效的 JSON 。这个函数在需要验证输入的字符串是否符合 JSON 格式要求的场景中很有用，能帮助开发者提前发现格式错误，避免后续处理中出现问题。