MySQL JSON 类型:功能与应用

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS PostgreSQL,高可用系列 2核4GB
云数据库 RDS MySQL,高可用系列 2核4GB
简介: MySQL JSON 类型:功能与应用

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 数据:

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 数据中的特定部分
-- 提取 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 数据中插入新的部分,如果路径已存在则不会替换。
-- 在 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 数据中的部分,如果路径不存在则不会添加。
-- 替换 name 字段的值  
UPDATE json_example  
SET data = JSON_REPLACE(data, '$.name', 'Jane Smith');  
-- 此时 name 变为 "Jane Smith"

3.4 JSON_REMOVE()

  • 从 JSON 数据中移除指定的部分。
-- 移除 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 数组和对象
-- 创建一个新的 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 对象的所有键
-- 获取 JSON 对象的所有键  
SELECT JSON_KEYS(data) AS object_keys FROM json_example;  
-- 结果: ["name", "age", "address", "phoneNumbers", "isActive"]

3.7 JSON_VALID()

  • 验证 JSON 数据的有效性。
-- 验证 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 表中存在这样一条数据

INSERT INTO json_example (data) VALUES (  
  '{  
    "name": "John",  
    "interests": ["reading", "music"],  
    "friends": [  
      {"name": "Alice", "age": 28},  
      {"name": "Bob", "age": 32}  
    ]  
  }'  
);

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

-- 使用 JSON_QUOTE 将普通字符串转换为 JSON 字符串  
SELECT JSON_QUOTE('Hello, World!') AS quoted_string;  
-- 结果: ""Hello, World!""  
  
-- 使用 JSON_UNQUOTE 将 JSON 字符串转换回普通字符串  
SELECT JSON_UNQUOTE('"Hello, World!"') AS unquoted_string;  
-- 结果: Hello, World!

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

3.9 JSON_CONTAINS()

  • 检查 JSON 文档是否包含指定的值。
-- 检查 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 文档是否包含指定的路径。
-- 检查是否存在 friends 数组中的对象的 name 路径  
SELECT JSON_CONTAINS_PATH(data, 'one', '$.friends[*].name') AS contains_path FROM json_example;  
-- 结果: 1 (表示 true,因为存在该路径)

3.10 JSON_ARRAY_APPEND()

  • 向 JSON 数组追加元素。
-- 向 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 用法如下:

-- 向 interests 数组追加 "traveling"(适用于 MySQL 8.0.17 及更高版本)  
UPDATE json_example  
SET data = JSON_ARRAY_APPEND(data, '$.interests', 'traveling');

3.11 JSON_ARRAY_INSERT()

  • 在 JSON 数组的指定位置插入元素。
-- 在 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 数组的第一个元素。

ALTER TABLE json_example  
ADD first_interest VARCHAR(255) GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(data, '$.interests[0]'))) VIRTUAL;

4.2 为虚拟列创建索引

CREATE INDEX idx_first_interest ON json_example(first_interest);

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

4.3 查询优化

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

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 功能来简化你的工作。

相关实践学习
每个IT人都想学的“Web应用上云经典架构”实战
本实验从Web应用上云这个最基本的、最普遍的需求出发,帮助IT从业者们通过“阿里云Web应用上云解决方案”,了解一个企业级Web应用上云的常见架构,了解如何构建一个高可用、可扩展的企业级应用架构。
MySQL数据库入门学习
本课程通过最流行的开源数据库MySQL带你了解数据库的世界。   相关的阿里云产品:云数据库RDS MySQL 版 阿里云关系型数据库RDS(Relational Database Service)是一种稳定可靠、可弹性伸缩的在线数据库服务,提供容灾、备份、恢复、迁移等方面的全套解决方案,彻底解决数据库运维的烦恼。 了解产品详情: https://www.aliyun.com/product/rds/mysql 
相关文章
|
28天前
|
SQL 监控 关系型数据库
MySQL事务处理:ACID特性与实战应用
本文深入解析了MySQL事务处理机制及ACID特性,通过银行转账、批量操作等实际案例展示了事务的应用技巧,并提供了性能优化方案。内容涵盖事务操作、一致性保障、并发控制、持久性机制、分布式事务及最佳实践,助力开发者构建高可靠数据库系统。
|
2月前
|
关系型数据库 MySQL 分布式数据库
安全可靠的PolarDB V2.0 (兼容MySQL)产品能力及应用场景
PolarDB分布式轻量版采用软件输出方式,能够部署在您的自主环境中。PolarDB分布式轻量版保留并承载了云原生数据库PolarDB分布式版技术团队深厚的内核优化成果,在保持高性能的同时,显著降低成本。
|
1月前
|
存储 关系型数据库 MySQL
MYSQL数据加密压缩函数应用实战指南。
总的来说,加密和压缩是维护MySQL数据库中数据安全性和效率的有效手段。使用时需权衡性能与安全,合理应用加密和压缩函数。在设计数据库架构时要考虑到加密字段的查询性能,因为加密可能使得一些索引失效。压缩数据能有效减少存储空间的占用,但在服务器负载较高时应避免实时压缩和解压,以免影响总体性能。
74 10
|
2月前
|
SQL 存储 关系型数据库
MySQL功能模块探秘:数据库世界的奇妙之旅
]带你轻松愉快地探索MySQL 8.4.5的核心功能模块,从SQL引擎到存储引擎,从复制机制到插件系统,让你在欢声笑语中掌握数据库的精髓!
115 26
|
6月前
|
存储 关系型数据库 MySQL
MySQL细节优化:关闭大小写敏感功能的方法。
通过这种方法,你就可以成功关闭 MySQL 的大小写敏感功能,让你的数据库操作更加便捷。
496 19
|
6月前
|
关系型数据库 MySQL 数据库
|
8月前
|
JSON 小程序 UED
微信小程序 app.json 配置文件解析与应用
本文介绍了微信小程序中 `app.json` 配置文件的详细
1263 12
|
10月前
|
关系型数据库 MySQL Java
MySQL索引优化与Java应用实践
【11月更文挑战第25天】在大数据量和高并发的业务场景下,MySQL数据库的索引优化是提升查询性能的关键。本文将深入探讨MySQL索引的多种类型、优化策略及其在Java应用中的实践,通过历史背景、业务场景、底层原理的介绍,并结合Java示例代码,帮助Java架构师更好地理解并应用这些技术。
267 2
|
10月前
|
存储 JSON 监控
公司用什么软件监控电脑:JSON 在监控信息交互中的应用探索
在现代企业管理中,电脑监控软件广泛应用于保障信息安全和提升工作效率。JSON(JavaScript Object Notation)因其简洁和易读性,在监控信息的收集、传输和处理中扮演着关键角色。本文介绍了 JSON 在监控数据结构、信息传输及服务器端处理中的具体应用,展示了其在高效监控系统中的重要性。
115 0
|
10月前
|
JSON 关系型数据库 MySQL
MySQL JSON数据存储结构与操作
通过本文的介绍,我们了解了MySQL中JSON数据类型的基本操作、常用JSON函数、以及如何通过索引和优化来提高查询性能。JSON数据类型为存储和操作结构化数据提供了灵活性和便利性,在现代数据库应用中具有广泛的应用前景。希望本文对您在MySQL中使用JSON数据类型有所帮助。
1139 0

推荐镜像

更多