MySQL8.0 JSON数据类型

原创

月饮沙 2023-08-02 15:51:16 博主文章分类：MySQL ©著作权

文章标签 JSON 文章分类 MySQL 数据库

©著作权归作者所有：来自51CTO博客作者月饮沙的原创作品，请联系作者获取转载授权，否则将追究法律责任

(目录)

JSON数据类型

MySQL8.0支持原生的JSON数据类型。 JSON数据类型存在以下优点

自动验证存储在json列中的JSON文档，无效文档会产生错误
优化存储格式。json列中的文档被转换为内部格式，可以直接对文档元素进行快速读取访问。

JSON值

JSON值类型

在MySQL中，JSON值可以是标量/数组/对象

标量包含字符串/数字/null/布尔值
数组数组包含在方括号[]中，并使用,分隔其中的元素
对象（类似于python中的字典）对象包含在大括号里{}，包含一系列的键值对，每个键值对直接使用,分隔。键值对的格式为"key":value

示例如下：

-- 字符串
"string"
-- 数组
["abc", 10, null, true, false]
-- 对象
{"k1": "value", "k2": 10}

JSON对象中的键必须是字符串数组中的元素和JSON对象中的值可以是字符串、数字、null、布尔值、时间除此之外，数组中的元素可以是数组或者JSON对象。JSON对象的值也可以是数组或者JSON对象

查看JSON值类型

可以使用JSON_TPYE()函数来查看JSON值的类型

mysql> select value,json_type(value) from test_json;
+-------------------------+------------------+
| value                   | json_type(value) |
+-------------------------+------------------+
| {"id": 1, "type": "no"} | OBJECT           |
| [1, 2]                  | ARRAY            |
| "status"                | STRING           |
+-------------------------+------------------+

-- 注意要查询的JSON值需要使用引号引起来
mysql> SELECT JSON_TYPE('"hello"');
+----------------------+
| JSON_TYPE('"hello"') |
+----------------------+
| STRING               |
+----------------------+

mysql> SELECT JSON_TYPE('hello');
ERROR 3146 (22032): Invalid data type for JSON data in argument 1
to function json_type; a JSON string or JSON type is required.

JSON值的字符集和排序规则

JSON值使用utf8mb4字符集以及utf8mb4_bin排序规则 utf8mb4_bin排序规则区分大小写，JSON值中的null,true,false必须使用小写字母来表示

JSON值中特殊字符的处理

如果要在JSON对象的值中使用"和'，需要在前面加上\进行转义

-- 在JSON_OBJECT函数中使用单个\转义即可
INSERT INTO test_json (value) VALUES (JSON_OBJECT("mascot", "Our mascot is a dolphin named \"Sakila\"."));
-- 如果直接使用JSON值，需要使用两个\进行转义
INSERT INTO test_json (value)  VALUES ('{"mascot": "Our mascot is a dolphin named \\"Sakila\\"."}');

查看JSON值时特殊字符的处理

-- ->显示转义字符
mysql> select value->"$.mascot" from test_json;
+---------------------------------------------+
| value->"$.mascot"                           |
+---------------------------------------------+
| "Our mascot is a dolphin named \"Sakila\"." |
+---------------------------------------------+
1 row in set (0.00 sec)

-- 不显示转义字符
mysql> select value->>"$.mascot" from test_json;
+-----------------------------------------+
| value->>"$.mascot"                      |
+-----------------------------------------+
| Our mascot is a dolphin named "Sakila". |
+-----------------------------------------+
1 row in set (0.00 sec)

创建JSON值

可以使用JSON函数创建JSON值

JSON_ARRAY() 创建JSON数组

mysql> SELECT JSON_ARRAY('a', 1, NOW());
+----------------------------------------+
| JSON_ARRAY('a', 1, NOW())              |
+----------------------------------------+
| ["a", 1, "2015-07-27 09:43:47.000000"] |
+----------------------------------------+

JSON_OBJECT() 创建JSON对象

mysql> SELECT JSON_OBJECT('key1', 1, 'key2', 'abc');
+---------------------------------------+
| JSON_OBJECT('key1', 1, 'key2', 'abc') |
+---------------------------------------+
| {"key1": 1, "key2": "abc"}            |
+---------------------------------------+

JSON对象的规则

在JSON对象中，如果输入重复的键，则该键值为后输入的值(8.0.3之前的版本保留第一个值）

mysql> SELECT JSON_OBJECT('key1', 1, 'key2', 'abc', 'key1', 'def');
+------------------------------------------------------+
| JSON_OBJECT('key1', 1, 'key2', 'abc', 'key1', 'def') |
+------------------------------------------------------+
| {"key1": "def", "key2": "abc"}                       |
+------------------------------------------------------+

合并JSON值

JSON MERGE PRESERVE() 不去除重复值
JSON_MERGE_PATCH() 去除重复值

合并数组

JSON MERGE PRESERVE() 将后面数组的元素追加到第一个数组结尾
JSON_MERGE_PATCH() 只保留最后一个数组的元素

mysql> SELECT JSON_MERGE_PRESERVE('[1, 2]', '["a", "b", "c"]', '[true, false]') AS Preserve,
    -> JSON_MERGE_PATCH('[1, 2]', '["a", "b", "c"]', '[true, false]') AS Patch\G
*************************** 1. row ***************************
Preserve: [1, 2, "a", "b", "c", true, false]
   Patch: [true, false]
1 row in set (0.00 sec)

合并对象

JSON MERGE PRESERVE() 对于重复的键，将键值合并为数组，如果键值为数组，则将其他键值追加到数组中
JSON_MERGE_PATCH() 对于重复键，只保留最后一次出现的键值

mysql>  SELECT
    ->      JSON_MERGE_PRESERVE('{"a": 1, "b": 2}', '{"c": 3, "a": 4}', '{"c": [4,5], "d": 3}') AS Preserve,
    ->      JSON_MERGE_PATCH('{"a": 1, "b": 2}', '{"c": 3, "a": 4}', '{"c": [4,5], "d": 3}') AS Patch\G
*************************** 1. row ***************************
Preserve: {"a": [1, 4], "b": 2, "c": [3, 4, 5], "d": 3}
   Patch: {"a": 4, "b": 2, "c": [4, 5], "d": 3}
1 row in set (0.00 sec)

合并数组和对象

JSON MERGE PRESERVE() 将对象作为数组中的一个元素追加到数组中
JSON_MERGE_PATCH() 之保留最后出现的数组/对象

mysql> SELECT
    -> JSON_MERGE_PRESERVE('[10, 20]', '{"a": "x", "b": "y"}') AS Preserve,
    -> JSON_MERGE_PATCH('[10, 20]', '{"a": "x", "b": "y"}') AS Patch,
    -> JSON_MERGE_PRESERVE('{"a": "x", "b": "y"}','[10, 20]' ) AS Preserve,
    -> JSON_MERGE_PATCH('{"a": "x", "b": "y"}','[10, 20]') AS Patch\G
*************************** 1. row ***************************
Preserve: [10, 20, {"a": "x", "b": "y"}]
   Patch: {"a": "x", "b": "y"}
Preserve: [{"a": "x", "b": "y"}, 10, 20]
   Patch: [10, 20]
1 row in set (0.00 sec)

查询和更新JSON值

JSON路径

JSON路径（path）可以用来提取和修改JSON文档中的部分内容。 $表示所选择的文档，后面加上文档内容的路径

.key 表示文档中key键对应的值
[N] 表示数组中的第n个元素。数组从0开始计数。
[M to N] 表示数组中的第m到第n个元素。其中可以使用last代表数组最后一个元素的索引
路径中可以包含*或者**通配符
- .[*] 等同于JSON对象的所有的值
- [*] 等同于JSON数组的所有元素
- prefix**suffix 表示所有以prefix开头以suffix结尾的路径
- 如果路径对应的内容不存在，会返回NULL

查询JSON值

可以使用JSON_EXTRACT(json_doc, path[, path] ...)函数来获取JSON文档中的部分内容，如果是对JSON列进行查询，也可以简写为column->path

-- 获取JSON对象中的内容
mysql> select value,value->"$.a",json_extract(value,'$.*') from test_json\G
*************************** 1. row ***************************
                    value: {"a": 1, "b": 2, "c": [3, 4, 5]}
             value->"$.a": 1 -- a键对应的值
json_extract(value,'$.*'): [1, 2, [3, 4, 5]] --JSON对象的所有值

-- 获取JSON数组中的元素
mysql> select value,value->"$.c[0]",value->"$.c[1 to last -1] ",value->"$.c[1 to 10]" from test_json\G
*************************** 1. row ***************************
                      value: {"a": 1, "b": 2, "c": [3, 4, 5]}
            value->"$.c[0]": 3
value->"$.c[1 to last -1] ": [4] -- [3, 4, 5]的最后一个元素的索引是2 last-1=1 等同于 $.c[1 to 1]
      value->"$.c[1 to 10]": [4, 5] -- 由于数组中没有11个元素 [1 to 10]只能获取到2个元素

-- 使用通配符
mysql> select value,value->"$**.b" from test_json\G
*************************** 1. row ***************************
         value: {"a": {"b": 1}, "c": {"b": 2}}
value->"$**.b": [1, 2] -- 获取所有键值下键为b对应的值

更新JSON值

JSON_SET() 如果存在值，就更新，否则就插入
JSON_INSERT() 如果存在值，不操作，否则插入值
JSON_REPLACE() 如果存在值，更新该值，否则无效果
JSON_REMOVE() 如果存在值，就删除，否则无效果

-- JSON_SET
mysql> select value,json_set(value,"$.c[3]",3),json_set(value,"$.d",7) from test_json\G
*************************** 1. row ***************************
                     value: {"a": 1, "b": 2, "c": [3, 4, 5]}
json_set(value,"$.c[3]",3): {"a": 1, "b": 2, "c": [3, 4, 5, 3]} -- $.c 只有3个元素，由于数组的索引从0开始，所以$.c[3]不存在，在数组中插入值
   json_set(value,"$.d",7): {"a": 1, "b": 2, "c": [3, 4, 5], "d": 7} --$中没有d做为键的值，在对象中插入键值对

mysql> select value,json_set(value,"$.a",5),json_set(value,"$.c[3]",3),json_set(value,"$.d",7) from test_json\G
*************************** 1. row ***************************
                     value: {"a": 1, "b": 2, "c": [3, 4, 5]}
   json_set(value,"$.a",5): {"a": 5, "b": 2, "c": [3, 4, 5]} -- $.a已存在，更新该值
json_set(value,"$.c[3]",3): {"a": 1, "b": 2, "c": [3, 4, 5, 3]}  -- $.c 只有3个元素，由于数组的索引从0开始，所以$.c[3]不存在，在数组中插入值
   json_set(value,"$.d",7): {"a": 1, "b": 2, "c": [3, 4, 5], "d": 7} --$中没有d做为键的值，在对象中插入键值对

-- JSON_INSERT 
mysql> select value,json_insert(value,"$.a",5),json_insert(value,"$.c[3]",3),json_insert(value,"$.d",7) from test_json\G
*************************** 1. row ***************************
                        value: {"a": 1, "b": 2, "c": [3, 4, 5]}
   json_insert(value,"$.a",5): {"a": 1, "b": 2, "c": [3, 4, 5]} -- $.a已存在，无效果
json_insert(value,"$.c[3]",3): {"a": 1, "b": 2, "c": [3, 4, 5, 3]} -- $.c[3]不存在，在数组中插入值
   json_insert(value,"$.d",7): {"a": 1, "b": 2, "c": [3, 4, 5], "d": 7} -- $.d不存在，在对象中插入键值对

-- JSON_REPLACE
mysql> select value,json_replace(value,"$.a",5),json_replace(value,"$.c[3]",3),json_replace(value,"$.d",7) from test_json\G
*************************** 1. row ***************************
                         value: {"a": 1, "b": 2, "c": [3, 4, 5]}
   json_replace(value,"$.a",5): {"a": 5, "b": 2, "c": [3, 4, 5]} -- $.a已存在，更新该值
json_replace(value,"$.c[3]",3): {"a": 1, "b": 2, "c": [3, 4, 5]} -- $.c[3]不存在，无效果
   json_replace(value,"$.d",7): {"a": 1, "b": 2, "c": [3, 4, 5]} -- $.d不存在，无效果

-- JSON_REMOVE
mysql> select value,json_remove(value,"$.a"),json_remove(value,"$.c[2]"),json_remove(value,"$.d") from test_json\G
*************************** 1. row ***************************
                      value: {"a": 1, "b": 2, "c": [3, 4, 5]}
   json_remove(value,"$.a"): {"b": 2, "c": [3, 4, 5]} --$.a 存在，删除键值对
json_remove(value,"$.c[2]"): {"a": 1, "b": 2, "c": [3, 4]} --$.c[2]存在，在数组中删除该元素
   json_remove(value,"$.d"): {"a": 1, "b": 2, "c": [3, 4, 5]} --$.d不存在，无效果

上一篇：MySQL 账号密码永不过期为什么不起作用？

下一篇：MySQL8.0 JSON相关函数(一) -创建和查找JSON

提问和评论都可以，用心的回复会被更多人看到评论

发布评论

相关文章

官方博客	全部文章	热门标签	班级博客
了解我们	网站地图	意见反馈

鸿蒙开发者社区	51CTO学堂
51CTO	软考资讯