首页 > 数据库 >MySQL8.0 JSON数据类型

MySQL8.0 JSON数据类型

时间：2023-08-02 16:02:43浏览次数：42  
标签：-- 数据类型 json value JSON MySQL8.0 数组 mysql

(目录)

JSON数据类型

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

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

JSON值

JSON值类型

在MySQL中，JSON值可以是字符串/数组/对象

  • 字符串
  • 数组 数组包含在方括号[]中，并使用,分隔其中的元素
  • 对象（类似于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不存在，无效果

标签：--,数据类型,json,value,JSON,MySQL8.0,数组,mysql
From： https://blog.51cto.com/yueyinsha/6939497

相关文章

  • 8、MySQL数据表基本数据类型
    数字整数还有其他的数据类型，分成了几种：1、数字类型2、字符串类型3、时间类型总共可以分成这么几类，咱们的数字里面有个int，除了int还有什么呢，还有tinyint、bigint，它们都是int，它们之间的区别是什么，它是不是能表示的数据范围是不一样的。比如对于tinyint，它只能表示有符号的-128......
  • post 传递参数 只有data json 和**kwargs, 不能是params
    importrequestsHOST='https://ip.com'URL='/posts/'url=HOST+URLjson={"title":"foo","body":"bar","userId":1}res=requests.post(url,json=json)print(res.json())print(......
  • Json Web Token
    什么是JsonWebTokenJWT代表JSONWebToken，是一种用于在网络应用中传递信息的安全、紧凑的标准。它主要用于身份验证和授权，并且被广泛用于前后端分离的应用和单点登录系统。JWT由三部分组成，通过点号(.)分隔，分别是：Header（头部）：包含了两部分信息，令牌的类型（通常是"JWT"）和所使用的......
  • 让nlohmann json支持std::wstring和嵌套结构的序列化与反序列化
    nlohmannjson是一个star很高的C++json解析库。要让nlohmannjson支持某个类型T，只要给这个类型T实现一个偏特化的structadl_serializer<T>即可。adl_serializer是这个库里面针对泛型T预定义的适配器。而嵌套结构，本身就支持的。使用预定义的宏NLOHMANN_DEFINE_TYPE_NON_INTRUSI......
  • PHPJSON数据格式常见应用及实例解析
    PHPJSON数据格式常见应用及实例解析随着Web应用的兴起和普及，数据的传输和处理已经成为Web开发中不可或缺的一部分。PHP作为一种广泛使用的服务器端编程语言，对于数据的处理和传输也有着非常丰富的支持。其中，JSON数据格式已经成为Web开发中最常用的数据格式之一。本文将结合实例，介......
  • requests--post中json中文编码问题
    问题requestspost提交json数据时，默认在库中ensure_ascii为True。会对中文进行unicode编码。但是有的时候服务端并没有处理中文，没有进行解码，而我们又改不了服务端，就会出现问题！解决修改库的代码，添加上对应的ensure_ascii参数。不推荐，换个环境就用不了了。推荐:自己......
  • JPA实体类映射PostgreSQL中的jsonb字段
    前言有时候我们需要在PostgreSQL表中存储jsonb类型的数据，JPA实体类中如何定义这个属性与之对应呢？本篇介绍两种方式：①自定义数据库方言和自定义类型②引入hibernate-types依赖方式一自定义数据库方言和自定义类型自定义方言publicclassCustomPostgreSqlDialect......
  • Java基础数据类型
    基础数据类型基础数据类型：byte（字节型），short（短整型），int（整型），long（长整型），float（单精度浮点型），double（双精度浮点型），char（字符型）  1.byte字节型占1个字节，范围-128到127bytea=5;byteb=6;//bytec=200;//编译错误，超出范围2.short短整型占2个字节，范围-32768......
  • 21 os/sys/json/subprocess模块
    os模块使用前老规矩：加上importos1.创建目录（文件夹）makedirsos.mkdir(r'aaa')#在os所在的路径下创建一个aaa文件夹（只能创建一个单级目录）os.makedir((r'ccc')os.makedirs(r'bbb/ccc/ddd')#makedirs支持创建多级目录2.删除目录（文件夹）removediros.rmdir(r'aaa')#可......
  • 最新中国地区各种数据大全如JSON、CSV等
    ✍️作者简介：沫小北（专注于Android、Web、TCP/IP等技术方向）......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99