Loading [MathJax]/jax/input/TeX/config.js
首页
学习
活动
专区
圈层
工具
发布
首页
学习
活动
专区
圈层
工具
MCP广场
社区首页 >专栏 >深入探索 MySQL 8 中的 JSON 类型:功能与应用

深入探索 MySQL 8 中的 JSON 类型:功能与应用

作者头像
公众号:码到三十五
修改于 2024-03-21 00:39:16
修改于 2024-03-21 00:39:16
2.7K00
代码可运行
举报
文章被收录于专栏:数据核心数据核心
运行总次数:0
代码可运行

1. 引言

在早期的 MySQL 版本中,开发者通常将 JSON 数据以字符串的形式存储在数据库中,这导致了查询效率低下和数据处理复杂。为了解决这个问题,MySQL 8 引入了原生的 JSON 数据类型,允许我们以结构化的方式存储和查询 JSON 数据。

2. JSON 数据类型特性

  • 验证:当插入或更新 JSON 列时,MySQL 会自动验证数据的 JSON 格式,确保数据的完整性。
  • 优化存储:JSON 数据类型以二进制格式存储,相比纯文本存储更加高效。
  • 灵活性:JSON 列可以存储数组、对象、嵌套结构等,为数据的表示提供了极大的灵活性。

3. JSON 函数

MySQL 8 提供了一系列内置函数来操作和查询 JSON 数据:

  • 提取数据:使用 JSON_EXTRACT() 函数可以从 JSON 文档中提取指定的数据片段。
  • 修改数据:JSON_INSERT()、JSON_REPLACE() 和 JSON_REMOVE() 函数允许你向 JSON 文档中添加、替换或删除数据。
  • 创建 JSON:JSON_ARRAY() 和 JSON_OBJECT() 函数用于创建 JSON 数组和对象。
  • 查询功能:JSON_LENGTH()、JSON_KEYS()、JSON_VALID() 等函数,用于获取 JSON 数据的长度、键或验证 JSON 数据的有效性。
  • 其他函数,如 JSON_QUOTE(), JSON_UNQUOTE(), JSON_CONTAINS(), JSON_CONTAINS_PATH(), JSON_ARRAY_APPEND(), JSON_ARRAY_INSERT() 等等。

下面的例子看看每个函数的具体使用方法:

首先,我们创建一个名为 json_example 的表,并插入一条 JSON 数据:

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
CREATE TABLE json_example (  
  id INT AUTO_INCREMENT PRIMARY KEY,  
  data JSON  
);  
  
INSERT INTO json_example (data) VALUES (  
  '{  
    "name": "John Doe",  
    "age": 30,  
    "address": {  
      "street": "123 Main St",  
      "city": "Anytown",  
      "zip": "12345"  
    },  
    "phoneNumbers": ["123-456-7890", "987-654-3210"],  
    "isActive": true  
  }'  
);

表 json_example 中有一条包含 JSON 数据的记录。接下来,我们将使用不同的 JSON 函数来查询和修改这个数据.

3.1 JSON_EXTRACT()
  • 提取 JSON 数据中的特定部分
代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
-- 提取 name 字段的值  
SELECT JSON_EXTRACT(data, '$.name') AS extracted_name FROM json_example;  
-- 结果: "John Doe"  
  
-- 提取 address 对象的 city 字段的值  
SELECT JSON_EXTRACT(data, '$.address.city') AS city FROM json_example;  
-- 结果: "Anytown"
3.2 JSON_INSERT()
  • 向 JSON 数据中插入新的部分,如果路径已存在则不会替换。
代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
-- 在 phoneNumbers 数组后面插入一个新的电话号码  
UPDATE json_example  
SET data = JSON_INSERT(data, '$.phoneNumbers[2]', '555-123-4567');  
-- 此时 phoneNumbers 变为 ["123-456-7890", "987-654-3210", "555-123-4567"]
3.3 JSON_REPLACE()
  • 替换 JSON 数据中的部分,如果路径不存在则不会添加。
代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
-- 替换 name 字段的值  
UPDATE json_example  
SET data = JSON_REPLACE(data, '$.name', 'Jane Smith');  
-- 此时 name 变为 "Jane Smith"
3.4 JSON_REMOVE()
  • 从 JSON 数据中移除指定的部分。
代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
-- 移除 phoneNumbers 数组中的第一个电话号码  
UPDATE json_example  
SET data = JSON_REMOVE(data, '$.phoneNumbers[0]');  
-- 此时 phoneNumbers 变为 ["987-654-3210", "555-123-4567"]
3.5 JSON_ARRAY() 和 JSON_OBJECT()
  • 创建 JSON 数组和对象
代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
-- 创建一个新的 JSON 数组  
SELECT JSON_ARRAY('a', 1, TRUE);  
-- 结果: ["a", 1, true]  
  
-- 创建一个新的 JSON 对象  
SELECT JSON_OBJECT('key1', 'value1', 'key2', 2);  
-- 结果: {"key1": "value1", "key2": 2}
JSON_LENGTH() - 获取 JSON 文档或数组的长度。
sql
-- 获取 phoneNumbers 数组的长度  
SELECT JSON_LENGTH(data->'$.phoneNumbers') AS phone_numbers_length FROM json_example;  
-- 结果: 2 (因为 phoneNumbers 数组现在有两个元素)
3.6 JSON_KEYS()
  • 获取 JSON 对象的所有键
代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
-- 获取 JSON 对象的所有键  
SELECT JSON_KEYS(data) AS object_keys FROM json_example;  
-- 结果: ["name", "age", "address", "phoneNumbers", "isActive"]
3.7 JSON_VALID()
  • 验证 JSON 数据的有效性。
代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
-- 验证 data 列是否包含有效的 JSON  
SELECT JSON_VALID(data) AS is_valid_json FROM json_example;  
-- 结果: 1 (表示 true,因为 data 列包含有效的 JSON)
3.8 JSON_QUOTE() 和 JSON_UNQUOTE()
  • 将字符串转换为 JSON 格式的字符串,以及反向操作。

假设json_example 表中存在这样一条数据

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
INSERT INTO json_example (data) VALUES (  
  '{  
    "name": "John",  
    "interests": ["reading", "music"],  
    "friends": [  
      {"name": "Alice", "age": 28},  
      {"name": "Bob", "age": 32}  
    ]  
  }'  
);

现在我们将使用上述函数对这条数据进行操作:

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
-- 使用 JSON_QUOTE 将普通字符串转换为 JSON 字符串  
SELECT JSON_QUOTE('Hello, World!') AS quoted_string;  
-- 结果: ""Hello, World!""  
  
-- 使用 JSON_UNQUOTEJSON 字符串转换回普通字符串  
SELECT JSON_UNQUOTE('"Hello, World!"') AS unquoted_string;  
-- 结果: Hello, World!

请注意,在实际的数据列上使用这些函数时,你通常会对已存储的 JSON 值或要插入的值进行操作。

3.9 JSON_CONTAINS()
  • 检查 JSON 文档是否包含指定的值。
代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
-- 检查 interests 数组是否包含 "reading"  
SELECT JSON_CONTAINS(data->'$.interests', '"reading"') AS contains_reading FROM json_example;  
-- 结果: 1 (表示 true,因为 interests 数组包含 "reading")

注意,因为 JSON 中的字符串是被双引号包围的,所以我们在查询时也需要对搜索的字符串值加上双引号。

3.9 JSON_CONTAINS_PATH()
  • 检查 JSON 文档是否包含指定的路径。
代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
-- 检查是否存在 friends 数组中的对象的 name 路径  
SELECT JSON_CONTAINS_PATH(data, 'one', '$.friends[*].name') AS contains_path FROM json_example;  
-- 结果: 1 (表示 true,因为存在该路径)
3.10 JSON_ARRAY_APPEND()
  • 向 JSON 数组追加元素。
代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
-- 向 interests 数组追加 "traveling"  
UPDATE json_example  
SET data = JSON_SET(data, '$.interests[2]', 'traveling');  
-- 注意:这里使用了 JSON_SET,因为 JSON_ARRAY_APPEND 需要指定路径到具体数组  
-- 在 MySQL 8.0.17 及更高版本中,可以使用 JSON_ARRAY_APPEND 正确地追加元素  
-- 例如: JSON_ARRAY_APPEND(data, '$.interests', 'traveling')

注意:上面的例子中使用了 JSON_SET 而不是 JSON_ARRAY_APPEND,因为在 MySQL 8.0.17 之前,JSON_ARRAY_APPEND 的语法有些不同,它要求指定路径到一个具体的数组元素。从 8.0.17 开始,JSON_ARRAY_APPEND 可以正确地追加到数组末尾。

正确的 JSON_ARRAY_APPEND 用法如下:

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
-- 向 interests 数组追加 "traveling"(适用于 MySQL 8.0.17 及更高版本)  
UPDATE json_example  
SET data = JSON_ARRAY_APPEND(data, '$.interests', 'traveling');
3.11 JSON_ARRAY_INSERT()
  • 在 JSON 数组的指定位置插入元素。
代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
-- 在 interests 数组的第一个位置插入 "gaming"  
UPDATE json_example  
SET data = JSON_ARRAY_INSERT(data, '$.interests[0]', 'gaming');  
-- 结果: interests 数组现在是 ["gaming", "reading", "music", "traveling"]

4. JSON 索引

为了提高查询性能,MySQL 8 支持为 JSON 列创建索引。但由于 JSON 数据的灵活性,直接对整个 JSON 文档创建索引并不高效。因此,MySQL 引入了虚拟列(Virtual Columns)的概念。

  • 虚拟列:虚拟列允许你根据 JSON 列中的值生成一个新的列,并为这个新列创建索引。这样,当你根据 JSON 数据中的某个字段进行查询时,MySQL 可以使用索引来加速查询。(关于虚拟列我将在之后的文章详解)
  • 创建索引:通过结合使用 JSON_EXTRACT() 函数和虚拟列,你可以轻松地为 JSON 数据中的特定字段创建索引。

基于上面的json_example 表,我们来看下为json字段创建索引

4.1 添加虚拟列

我们将添加一个名为 first_interest 的虚拟列,该列将存储 interests 数组的第一个元素。

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
ALTER TABLE json_example  
ADD first_interest VARCHAR(255) GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(data, '$.interests[0]'))) VIRTUAL;

在这里,我们使用了 JSON_EXTRACT() 来获取 interests 数组的第一个元素,并用 JSON_UNQUOTE() 去除引号,因为 JSON_EXTRACT() 返回的是 JSON 格式的字符串。

4.2 为虚拟列创建索引
代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
CREATE INDEX idx_first_interest ON json_example(first_interest);

现在,我们为 first_interest 列创建了一个索引,这将加速基于该列的查询。

4.3 查询优化

现在,我们可以基于 first_interest 列进行查询,并利用索引来加速查询过程。

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
SELECT * FROM json_example WHERE first_interest = 'reading';

由于我们为 first_interest 创建了索引,这个查询将会更加高效。但是,请注意,这种方法仅适用于查询 interests 数组的第一个元素。如果你需要查询数组中的其他元素,你可能需要采用其他策略,比如使用全文搜索、倒排索引或者将 JSON 数据规范化到关系型结构中。

5. 实际应用场景

  • 配置文件存储:应用程序的配置信息通常以 JSON 格式表示。使用 MySQL 8 的 JSON 数据类型,你可以轻松地将这些配置信息存储在数据库中,并使用 JSON 函数进行查询和修改。
  • 日志记录:日志条目通常以结构化的格式存储,JSON 是一个理想的选择。通过将日志数据存储在 JSON 列中,你可以轻松地分析和查询日志数据。
  • 与前端集成:使用 JSON 与后端进行数据交换。使用 MySQL 8 的 JSON 支持,你可以简化数据库与前端之间的数据交互。

6. 注意事项

  • 性能:虽然 MySQL 8 提供了对 JSON 的支持,但与传统的关系型数据相比,JSON 查询可能仍然不够高效。
  • 数据验证:虽然 MySQL 会验证 JSON 数据的格式,但它不会验证数据的业务规则或完整性。
  • 复杂性:JSON 数据的结构可能比传统的关系型数据更复杂,这可能会增加查询和维护的难度。

7. 结语

MySQL 8 的 JSON 数据类型为存储和查询 JSON 数据提供了强大的支持。通过内置的 JSON 函数和虚拟列索引,开发者可以高效地处理 JSON 数据,满足现代应用程序的需求。如果你正在开发需要存储和查询 JSON 数据的应用程序,不妨考虑使用 MySQL 8 的 JSON 功能来简化你的工作。


术因分享而日新,每获新知,喜溢心扉。 诚邀关注公众号 码到三十五 ,获取更多技术资料。

本文参与 腾讯云自媒体同步曝光计划,分享自作者个人站点/博客。
原始发表:2024-03-13,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 作者个人站点/博客 前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
暂无评论
推荐阅读
编辑精选文章
换一批
json查询解析在mysql5.7+有多方便?
在mysql5.7之前我们查询解析json需要先查出json字符串,再通过程序进行解析。(如果你使用的navicat版本太低的话,看不到json类型的) 现在mysql5.7引入了json类型格式,大大方便了我们的查询解析。 MySQL官方列出json相关的函数,完整列表如下: 分类 函数 描述 创建json json_array 创建json数组 json_object 创建json对象 json_quote 将json转成json字符串类型 查询json json_contains 判断是否包含某个
友儿
2022/09/11
2.6K0
一文说透MySQL JSON数据类型
JSON 数据类型是 MySQL 5.7.8 开始支持的。在此之前,只能通过字符类型(CHAR,VARCHAR 或 TEXT )来保存 JSON 文档。
星哥玩云
2022/06/06
5.3K0
一文说透MySQL JSON数据类型
MySQL8.0 JSON函数之搜索JSON值(五)
之前的几篇文章介绍了JSON数据类型,相信大家已经对JSON有了一定的了解,上面一篇文章介绍了《MySQL8.0 JSON函数之创建与返回JSON属性(四)》JSON函数的使用;本节中的函数对JSON值执行搜索或比较操作,以从中提取数据;
SEian.G
2021/07/07
8.1K0
mysql5.7强势支持原生json格式!!全面掌握
mysql一直是如此优秀,但是随着最近一些nosql的强劲发展,甚为关系型数据库的mysql,也不例外在某些层面稍有逊色。其中,是否支持json格式是最常被用来比较的。
后端技术探索
2018/08/09
8970
MySQL的JSON数据类型介绍以及JSON的解析查询
MySQL从5.7后引入了json数据类型以及json函数,可以有效的访问json格式的数据。json数据类型相对于字符串,具有以下优点:
Java技术债务
2022/08/09
12.1K0
MySQL的JSON数据类型介绍以及JSON的解析查询
MySQL 5.7新特性之五
本系列文章基于 5.7.12 版本讲述MySQL的新特性。从安装,文件结构,SQL ,优化 ,运维层面 复制,GITD等几个方面展开介绍 5.7 的新特性和功能。同时也建议大家跟踪官方blog和官方文档,以尽快知悉其新的变化。本文将重点介绍新版本对JSON格式的支持。
用户1278550
2018/08/09
5170
用 MySQL 实现 JSON 格式的数据存储
在绝大多数业务场景中,分布式数据库并非必需。事实上,约80%的应用程序都可以在单机环境下高效运行。
贺春旸的技术博客
2024/08/05
4990
用 MySQL 实现 JSON 格式的数据存储
MySQL 5.7新特性| Json Column 和 Generated Column(上)
MySQL5.7 新增两种字段类型:Json 和 Generated,Generated 型的产生和 Json 的关系密不可分,如果没有Generated 类型,Json 类型在强大,生产中可能也无法使用,因为 Json 不支持索引,但是如果要查询 Json 里的数据,没有索引就是全表扫描,在执行效率上肯定是不能用于生产环境的,但是有了 Generated 类型就不同了,Generated 类型简单地说是一个虚拟字段,值是不可更新的,值来源其他字段或者字段间计算或是转化而来的,这种类型是可以创建索引,利用 Generated 的特性,就可以间接的给 Json 类型中的 key 创建索引,解决 Json 不能创建索引的问题。简而言之, Generated 类型的产生,为 Json 类型在索引方面的问题提供了支持。JSON 的值包含单个值、数组、元组、标注的 Json 格式等几种格式。
阿炳数记
2019/02/27
2.2K0
mysql支持原生json使用说明
MySQL在5.7.8开始对json原生支持,本文将对MySQL中json类型的用法简单说明,希望对你有用。
我的小碗汤
2018/08/22
3.6K0
mysql支持原生json使用说明
MySQL 的 JSON 数据类型,YYDS!
用过 MySQL 都知道,关系型的结构化存储存在一定的弊端,因为它需要预先定义好所有的列以及列对应的类型。但是业务在发展过程中,或许需要扩展单个列的描述功能,这时,如果能用好 JSON 数据类型,那就能打通关系型和非关系型数据的存储之间的界限,为业务提供更好的架构选择。
JavaFish
2022/03/15
2.5K0
MySQL 支持JSON字段的基本操作、相关函数及索引使用如何索引JSON字段
Mysql5.7版本以后新增的功能,Mysql提供了一个原生的Json类型,Json值将不再以字符串的形式存储,而是采用一种允许快速读取文本元素(document elements)的内部二进制(internal binary)格式,并提供了不少内置函数,通过计算列,甚至还可以直接索引json中的数据。
chenchenchen
2022/03/09
31.2K0
Mysql json 数据查询
MySQL 5.7 之后提供了Json类型,是MySQL 结合结构化存储和非结构化存储设计出来的一个类型。
chuchur
2022/10/25
5.1K0
MySQL之Json类型
MySQL 5.7 之后提供了Json类型,是MySQL 结合结构化存储和非结构化存储设计出来的一个类型。
伍六七AI编程
2022/03/23
2.5K0
MySQL之Json类型
MySQL向MongoDB的妥协之JSON
MySQL在5.7.8版本中增加了对json数据的支持,而不再是需要使用字符串形式进行存储。下面简单介绍下MySQL对json的操作:
Jean
2018/10/17
3.5K1
MySQL向MongoDB的妥协之JSON
MySQL 5.7 JSON 数据类型使用总结
从MySQL5.7.8开始,MySQL支持原生的JSON数据类型。MySQL 支持RFC 7159定义的全部json 数据类型,具体的包含四种基本类型(strings, numbers, booleans, null)和两种结构化类型(objects and arrays)。
Tinywan
2024/02/22
8100
MySQL 5.7 JSON 数据类型使用总结
MySQL 5.7新特性| Json Column 和 Generated Column (中)
这个用法主要是处理 JSON 字段中值数组型的数据,主要是追加值,不是标准数组类型的,这个函数会自动转化为数组形式,把之前的值作为数组的第一个元素然后进行追加,有批量追加方法:
阿炳数记
2019/02/27
2.2K0
oracle mysql5.7 Json函数
oracle mysql 5.7.8 之后增加了对json数据格式的函数处理,可更加灵活的在数据库中操作json数据,如可变属性、自定义表单等等都使用使用该方式解决。
兜兜毛毛
2019/10/23
2.6K0
oracle mysql5.7  Json函数
体验 Mysql 操作 JSON 文档
新版 Mysql 中加入了对 JSON Document 的支持,可以创建 JSON 类型的字段,并有一套函数支持对JSON的查询、修改等操作,下面就实际体验一下 创建带有 JSON 字段的表 比如一个‘文章’表,字段包括 id、标题 title、标签 tags 一篇文章会有多个标签,tags 就可以设为 JSON 类型 建表语句如下: CREATE TABLE `article` ( `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT, `title
dys
2018/04/03
2.4K0
体验 Mysql 操作 JSON 文档
MySQL如何给JSON列添加索引(二)
上一篇文章《MySQL 8.0 JSON增强到底有多强?(一)》,我们简单介绍了MySQL中JSON数据类型,相信大家对JSON数据类型有了一定的了解,那么今天我们来简单看下如何在JSON列上添加索引?
SEian.G
2021/07/07
8K0
MySQL中的JSON
JSON类型的加入模糊了关系型数据库与NoSQL之间的界限,给日常开发也带来了很大的便利。
valineliu
2022/08/28
10.8K1
MySQL中的JSON
相关推荐
json查询解析在mysql5.7+有多方便?
更多 >
交个朋友
加入腾讯云官网粉丝站
蹲全网底价单品 享第一手活动信息
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档