MYSQL JSON数据操作

最新推荐文章于 2024-08-20 10:31:07 发布
最新推荐文章于 2024-08-20 10:31:07 发布
阅读量1.7k 收藏
点赞数 1
分类专栏： mysql 文章标签： mysql 数据库 sql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/zhoushimiao1990/article/details/121517825
版权
本文介绍了MySQL从5.7版本开始引入的JSON数据类型及其处理函数，包括JSON_EXTRACT、->、->>等，详细阐述了如何从JSON文档中获取和操作数据，以及如何创建、修改和验证JSON值。此外，还提供了各种函数的实例，如JSON_ARRAY、JSON_INSERT和JSON_SEARCH等，帮助理解JSON在数据库中的应用。
摘要由CSDN通过智能技术生成

推荐文档： mysql官网 -- JSON函数

Mysql 从5.7开始引入json数据类型， 相比于blob存储二进制字符、text存储文本类型数据， json存储的数据会验证是否符合json格式。 而且json能存储的最大文本长度与longBlob 和 longText 文本长度相同。

数据类型存储长度
TINYBLOB 、 TINYTEXT

 L+ 1 个字节，其中 L< 2^{8}

BLOB、TEXT

 L+ 2 个字节，其中 L< 2^{16}

MEDIUMBLOB、MEDIUMTEXT

 L+ 3 个字节，其中 L< 2^{24}

LONGBLOB 、LONGTEXT、 JSON

 L+ 3 个字节，其中 L< 2^{32}

mysql中JSON的处理函数： 

函数描述
->评估路径后从 JSON 列返回值；相当于 JSON_EXTRACT()。
->>在评估路径并取消引用结果后从 JSON 列返回值；相当于 JSON_UNQUOTE(JSON_EXTRACT())。
JSON_ARRAY()创建 JSON 数组
JSON_ARRAY_APPEND()将数据附加到 JSON 文档
JSON_ARRAY_INSERT()插入 JSON 数组
JSON_CONTAINS()JSON 文档是否包含路径中的特定对象
JSON_CONTAINS_PATH()JSON 文档是否包含路径中的任何数据
JSON_DEPTH()JSON 文档的最大深度
JSON_EXTRACT()从 JSON 文档返回数据
JSON_INSERT()将数据插入 JSON 文档
JSON_KEYS()来自 JSON 文档的键数组
JSON_LENGTH()JSON 文档中的元素数
JSON_MERGE()合并 JSON 文档，保留重复的键。不推荐使用的 JSON_MERGE_PRESERVE() 同义词（已弃用）
JSON_MERGE_PATCH()合并 JSON 文档，替换重复键的值
JSON_MERGE_PRESERVE()合并 JSON 文档，保留重复键
JSON_OBJECT()创建 JSON 对象
JSON_OVERLAPS()(8.0.17版本)比较两个 JSON 文档，如果它们有任何共同的键值对或数组元素，则返回 TRUE (1)，否则返回 FALSE (0)
JSON_PRETTY()以人类可读的格式打印 JSON 文档
JSON_QUOTE()引用 JSON 文档
JSON_REMOVE()从 JSON 文档中删除数据
JSON_REPLACE()替换 JSON 文档中的值
JSON_SCHEMA_VALID()(8.0.17版本)根据 JSON 模式验证 JSON 文档；如果文档根据模式进行验证，则返回 1，否则返回 0
JSON_SCHEMA_VALIDATION_REPORT()(8.0.17版本)根据 JSON 模式验证 JSON 文档；以 JSON 格式返回关于验证结果的报告，包括成功或失败以及失败的原因
JSON_SEARCH()JSON 文档中的值路径
JSON_SET()将数据插入 JSON 文档
JSON_STORAGE_FREE()部分更新后 JSON 列值的二进制表示中释放的空间
JSON_STORAGE_SIZE()用于存储 JSON 文档的二进制表示的空间
JSON_TABLE()将 JSON 表达式中的数据作为关系表返回
JSON_TYPE()JSON 值的类型
JSON_UNQUOTE()取消引用 JSON 值
JSON_VALID()JSON 值是否有效
JSON_VALUE()(8.0.21版本)在提供的路径指向的位置从 JSON 文档中提取值；将此值作为 VARCHAR(512) 或指定类型返回
MEMBER OF()(8.0.17版本)第一个操作数与作为第二个操作数传递的 JSON 数组的任何元素匹配，返回1，否则返回 0

常用的函数介绍： 

创建表、并插入数据： 

CREATE TABLE `zsm_json` (
  `json_test` json DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin

INSERT INTO `zsm_json`(`json_test`) VALUES ('[1,2,3,4]');
INSERT INTO `zsm_json`(`json_test`) VALUES ('{\"a\": 1, \"b\": 2, \"c\": [3, 4, 5]}');

实际应用中最常用的是前两个函数， 从json文本中获取制定值

1、 ->  、 ->>  获取json元素

1.1、 基础操作， 获取json元素： jsonColumn -> '$.keyName' 

select json_test -> '$.a' from zsm_json
select json_test -> '$."a"' from zsm_json  // a带双引号

select json_test -> '$[0]' from zsm_json   // 取第一个值
select json_test -> '$[1]' from zsm_json   // 取第二个值

result1： 

result2：

result3：

result4： 

 从上面的结果可以得出结论： 

  1.  json列既可以存储json对象也可以存储json数组
  2.  获取json中特定key的值，可以使用 $.keyName ,  keyName加不加双引号都行
  3. 如果json列中存储的是JSON数组， 可以使用$[index] 获取特定索引下的值， 而json对象被认为是只有一个元素的数组

1.2、json对象中存在数组元素或数组元素中存在json对象， 获取下层元素

// 获取JSON对象中key为 ‘c’的JSON数组中下标为2的元素
select json_test -> '$.c[2]' from zsm_json;

select json_test -> '$[0].c[2]' from zsm_json;

两个的查询结果都一样： 

1.3   ->>  处理特殊字符 

先往数据库中插入一条数据

INSERT INTO `zsm_json`(`json_test`) VALUES ('{"mascot": "Our mascot is a dolphin named \\"Sakila\\"."}');

查询：

select json_test -> '$.mascot' from zsm_json;

select json_test ->> '$.mascot' from zsm_json;

result1：

result2：

 可以看到使用 -> 查询到的数据， 对于转义字符 \" 并没有进行转义； 而使用 ->>  查询的结果， 转义字符被转成正常的字符。

-> 的使用不限于select， 也可以用于where 、 update等操作中

2、JSON_EXTRACT 获取json数据

json_extract 的功能与 -> 相似，从json文本中获取数据。 他的语法格式： 

JSON_EXTRACT(json_doc, path[, path] ...)

他可以一次获取多个value值， 并存放到一个数组中

SELECT JSON_EXTRACT('[10, 20, [30, 40]]', '$[1]', '$[0]');

SELECT JSON_EXTRACT('[10, 20, [30, 40]]', '$[2][*]');

具体的执行结果，这里就不再展示了。

3、其它搜索json值函数

  1. JSON_CONTAINS(target, candidate[, path]) ： 判断candidate文档中是否包含target文档， 如果制定了path参数，则在 candidate 的 path 路径中寻找 target。 这里path不能使用通配符。
  2. JSON_CONTAINS_PATH(json_doc, one / all, path[, path] ...) : 判断json文档中是否包含制定路径。

    1. 'one': 如果文档中至少存在一个路径，则为 1，否则为 0。

    2. 'all': 如果文档中存在所有路径，则为 1，否则为 0。

  3. JSON_KEYS(json_doc[, path]) ： 返回json文档中的所有一级key， 如果path指定， 返回指定路径下的一级key

  4. JSON_OVERLAPS(json_doc1, json_doc2) ： 比较两个 JSON 文档是否具有任何共同的键值对或数组元素。

  5.  value MEMBER OF(json_array) ： json数组中是否包含value值

  6. JSON_SEARCH(json_doc, one / all, search_str[, escape_char[, path] ...]) ： 返回json文档中给定字符串的路径。

    1. 'one'：搜索在第一次匹配后终止并返回一个路径字符串。未定义首先考虑哪个匹配。当我们要匹配的值有多个， 返回第一个匹配到的value的key。

    2. 'all'：搜索返回所有匹配的路径字符串，这样就不会包含重复的路径。如果有多个字符串，它们会自动包装为一个数组。数组元素的顺序未定义。

    3. // 下面是mysql官方文档给出的示例语句
SET @j = '["abc", [{"k": "10"}, "def"], {"x":"abc"}, {"y":"bcd"}]';

SELECT JSON_SEARCH(@j, 'one', 'abc');

SELECT JSON_SEARCH(@j, 'all', 'abc');

SELECT JSON_SEARCH(@j, 'all', 'ghi');

  7. JSON_VALUE(json_doc, path) ： 从json文档中获取指定路径下的值。完整的语法格式如下：

    JSON_VALUE(json_doc, path [RETURNING type] [on_empty] [on_error])

-- RETURNING  type 用于制定返回值类型， 
    -- type 取值 ： float/double/decimal/signed/unsigned/date/char/json.... 如果不指定， 默认为 varchar(512)

-- on_empty 未找到数据时，操作行为： 
    -- NULL ON EMPTY : 默认行为
    -- DEFAULT 'value' ON EMPTY ： 返回value， 注意类型匹配
    -- ERROR ON EMPTY ： 抛出错误
-- on_error 发生错误时， 操作行为
    -- NULL ON ERROR: 返回null， 默认行为
    -- DEFAULT 'value' ON ERROR ： 返回value， 注意类型匹配
    -- ERROR ON ERROR ： 抛出错误

4、修改JSON值得函数

  1.  JSON_ARRAY_APPEND(json_doc, path, val[, path, val] ...) ： 将值添加到json文档中指定数组的末尾并返回结果。
  2. JSON_ARRAY_INSERT(json_doc, path, val[, path, val] ...) ： 在JSON文档的指定位置插入指定值。
  3. JSON_INSERT(json_doc, path, val[, path, val] ...) ：  在json文档的指定路径上插入值，并返回整个json串
  4. JSON_MERGE_PATCH(json_doc, json_doc[, json_doc] ...)： 合并多个json对象( json对象不是json数组)。 当多个json文档中存在相同的key时， 后面的key值会覆盖前面的json中的key值。
    1. 如果第一个参数不是json对象， 则会返回第二个参数， 无论第二个参数是否是json文档。
    2. 如果第二个参数不是json对象， 第一个参数是json对象， 返回第一个json对象
  5. JSON_MERGE_PRESERVE(json_doc, json_doc[, json_doc] ...) ： 合并多个json对象， 不同的是它不会将值覆盖。 
    1. 可以合并json数组
    2. 如果多个json对象中有相同的key， 这里不是覆盖，而是合并多个json对象中的key值
  6. JSON_REMOVE(json_doc, path[, path] ...) ： 删除json文档中的指定值， 返回删除后的json串
  7. JSON_REPLACE(json_doc, path, val[, path, val] ...) ： 替换json文档中的值
  8. JSON_SET​​​​​​(json_doc, path, val[, path, val] ...)  ： 设置JSON文档中的值

    1. JSON_SET() 替换现有值并添加不存在的值。

    2. JSON_INSERT() 插入值而不替换现有值。

    3. JSON_REPLACE()仅替换 现有值。

5、JSON 属性函数： 

  1. JSON_DEPTH(json_doc) ： 返回json最大深度
  2. JSON_LENGTH(json_doc[, path]) ： 返回json文档长度-- json数组长度， JSON对象key个数
  3. JSON_TYPE(json_val) ： 返回utf8mb4指示 JSON 值类型的字符串。这可以是对象、数组或标量类型
  4.  JSON_VALID(val) ： 返回0 或 1 以指示是否为有效json串

6、JSON其它函数

  1.  JSON_PRETTY(json_val) ： 格式化json文档
  2. JSON_STORAGE_FREE(json_val) ： 对于json列， 还有多少存储空间
  3. JSON_STORAGE_SIZE(json_val) ： 存储json文档的字节数
三水写代码
关注
专栏目录
探秘MySQL JSON数据类型：深入解析与代码实战大全
java专栏
04-19 386
JSON（JavaScript Object Notation）是一种轻量级的数据交换格式，以其简洁的语法、易读性与广泛的语言支持而广受欢迎。MySQL的JSON数据类型允许直接在数据库中存储JSON文档，同时提供了丰富的内置函数以支持对JSON数据的查询、更新和操作。
MySQL中数组内的JSON数据中获取值
wsx1014的博客
05-20 6057
MySQL中JSON数据获取值 1.MySQL中JSON数据中获取值 数据源： { "observeTruth": "111", "preventHumenError": "DLYZ", "twoIndex": "502", "evaluate": "S" } sql语句： SELECT OBSERVE_TRUTH->'$**.twoIndex' as twoIndexJson FROM PAS_OPERATION_MANAGEMENT_SCORE WHERE ID = '
mysql取json数组中的数据
m0_37759590的博客
02-02 1651
mysql取json数组中的数据
MySQL中处理JSON数据：大数据分析新方向，技术详解与应用场景
最新发布
字节熊猫
08-20 1968
随着大数据时代的来临，数据分析和处理成为企业决策和业务优化的关键手段。JSON（JavaScript Object Notation）作为一种轻量级的数据交换格式，因其易于阅读和编写，以及易于机器解析和生成，被广泛应用于Web应用的数据传输。MySQL作为广泛使用的关系型数据库管理系统，自5.7版本起引入了原生的JSON数据类型，为大数据分析开辟了新的方向。本文将深入探讨MySQL中处理JSON数据的原理、优势、应用场景及最佳实践，并通过实际可用的详细代码示例进行说明。
mysql 取出json数组第一个
weixin_30784365的博客
07-30 147
MySQL取出JSON数组的第一个元素 在MySQL中，JSON数据类型提供了一种存储和操作JSON数据的方式。在处理JSON数据时，我们可能会遇到需要从JSON数组中取出第一个元素的情况。本文将介绍如何在MySQL中实现这一操作，并提供相应的代码示例。 1. 理解JSON数据类型 在MySQL中，JSON数据类型是一种用...
mysql自定义函数，获取Json列中json数组某个属性的值
harry034的博客
12-14 1584
mysql自定义函数，获取Json列中json数组某个属性的值
MySQL如何获取JSON串的值
u013851294的博客
11-14 2804
我们使用JSON_EXTRACT()、JSON_UNQUOTE() 这两个函数来操作。
MySQL操作之JSON数据类型操作详解
12-16
上一篇文章我们介绍了mysql数据存储过程参数实例详解，今天我们看看MySQL操作之JSON数据类型的相关内容。 概述 mysql自5.7.8版本开始，就支持了json结构的数据存储和查询，这表明了mysql也在不断的学习和增加nosql...
mysql json格式数据查询操作
01-19
预设表名是 base_data , json列名是 json_value json_value的值是 {“ids”:[“a”,“b”],“datas”:[{“name”:“张三”}]} 那么查询语句应该是 select * from base_data where JSON_CONTAINS(json_value -> ...
MySQL数据生成JSON数据
08-03
MySQL提供了一些内置的函数，如`JSON_ARRAY()`、`JSON_OBJECT()`、`JSON_EXTRACT()`等，来帮助我们操作JSON数据。例如，如果要将一个表的所有行转换为JSON数组，可以编写一个SQL查询，使用`SELECT JSON_ARRAYAGG...
mysql 取JSON数据
从未完美过的博客
10-10 1万+
正常的json使用mysql 取数据如下即可 -- 查询记录 SELECT sname,JSON_EXTRACT(info,'$.age') FROM t_json; SELECT sname,info->'$.age' FROM t_json; 当从数组重取数据时，需要添加数组下标， -- 查询记录 SELECT sname,JSON_EXTRACT(info,'$[0].age') FROM t_json; SELECT sname,info->'$[0].age' FROM .
MySQL从jsonarray获取某个字段的所有数据
a984171281的博客
07-30 352
目前思路有两种：①重新解析表里字段放到新表里（直接排除，要么一开始就存好，要么就不要做二次解析，导致多个数据源，串串连接）表里的order_goods_info_vo_list是jsonarray字段。现在业务要求获取其中的sku_id和goods_number字段。这样虽然获取了所有的值，但是还是有中括号，有点美中不足。②sql解析，这样分页也不会受到影响。
mysql5.7获取json数组中的某个对象
Meditation_Crazy
10-10 3247
表中的一个字段类型是字符串，存的是一个对象数据。现在要根据对象中的某个树形，获取到整个对象信息。如果是java8，则可以使用JSON_TABLE，但是5.7的没找到相关函数。
【mysql根据json字段内容作为查询条件筛选数据】
weixin_44665318的博客
08-18 3689
mysql根据json字段内容作为查询条件筛选数据
mysql json数组对象，单独提取某一个对象并提取某一个属性数据
wangh92
03-08 5504
如 字段 detail_data 值 类型： [{“name”: “技术活动名称”, “value”: “活动1”}, {“name”: “计划评审时间”, “value”: “2021-03-20”}, {“name”: “项目名称”, “value”: “修改编辑时的选人问题”}, {“name”: “项目类别”, “value”: “地震勘探”}, {“name”: “成果名称”, “value”: “成果名称”}, {“name”: “评审主持人”, “value”: “[“2158150928
java获取json数组中的值
Tz.的博客
03-29 8181
所需jar包的maven依赖: <dependency> <groupId>net.sf.json-lib</groupId> <artifactId>json-lib</artifactId> <version>2.4</version> <class...
mysql分组后，取每组第一条数据或最新一条
热门推荐
山鬼谣的专栏
05-06 8万+
环境 MySQL：5.7 Java：1.8 SQL语句的写法： select * from (select distinct(a.id) tid, a.* from template_detail a where a.template_id in (3, 4) order by a.id desc) tt group by tt.template_id; 思路：先进行排序，然后再进行分组，获取每组的第一条。 Q: 为什么要写distinct(a.i
mysql随机取第一条数据
qq_35324569的博客
08-14 221
通用：SELECT user_id FROM dd_circle_member WHERE circle_id=2927 ORDER BY RAND() LIMIT 1 但是效率低 下面的效率较高 SELECT user_id FROM `dd_circle_member` WHERE member_id >= (SELECT floor( RAND() * ((SELECT MAX...
Mysql 提取JSON数组中的某一个属性或某一个属性列表
_修铁路的、的博客
04-20 1万+
场景： 昨天为解决业务需求，需要在mysql 数据库中类型为json字段的列中提取指定的属性列表，因为之前对mysql数据库中操作json数据的经验比较少，特此记录下，以备以后回顾。 首先，展示下我数据库中保存的数据 (数据已做脱敏处理) [{"id": "dad014a2f9594a62ae3f7be3", "del": 10, "code": "XC02104162990","orgId": "5e0364ff8139f600163ff", "state": 10, ...
Python操作MySQL生成JSON数据
该Python脚本用于从MySQL数据库中获取数据，并将其转换为预定义的JSON结构体进行输出。脚本首先导入了MySQLdb模块来连接MySQL数据库和sys模块来处理编码问题。接着，它定义了数据库连接的参数，如主机地址、用户名、...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值