当前位置: 首页 > news >正文

【MySQL】MySQL的JSON特性

引言

MySQL从5.7版本开始引入了JSON数据类型,并在8.0版本中大大增强了JSON的支持,包括函数和索引功能。JSON数据类型允许你在MySQL表中存储JSON文档,这些文档可以是对象或数组,并且你可以使用SQL查询来检索、搜索、更新和修改这些JSON文档的内容。

MySQL的JSON特性

数据类型和存储

  • MySQL中的JSON类型可以存储JSON格式的字符串,这些字符串在内部被解析为JSON文档,允许你使用专门的JSON函数来操作它们。
  • JSON类型的数据以二进制格式存储,因此比纯文本格式更高效。

JSON函数

  • MySQL提供了大量的JSON函数,允许你创建JSON文档、修改JSON文档、提取JSON文档中的值等。
  • 常用的JSON函数包括JSON_ARRAY()JSON_OBJECT()JSON_SET()JSON_REPLACE()JSON_EXTRACT()JSON_CONTAINS()JSON_LENGTH()JSON_KEYS()JSON_VALUES()

索引

  • MySQL 5.7及以上版本支持在JSON文档上创建虚拟列索引,这允许你根据JSON文档中的值来优化查询。
  • MySQL 8.0引入了JSON路径表达式索引,这进一步增强了在JSON文档上执行高效查询的能力。

路径表达式: 

  • JSON路径表达式(JSONPath)用于指定JSON文档中的元素或子文档。在MySQL中,你可以使用JSONPath来提取、修改或查询JSON文档中的数据。

 例子

假设我们有一个名为users的表,它有一个名为info的JSON列,用于存储用户的额外信息(如姓名、年龄、兴趣爱好等)。

CREATE TABLE users (id INT AUTO_INCREMENT PRIMARY KEY,name VARCHAR(100),info JSON
);INSERT INTO users (name, info) VALUES
('Alice', '{"age": 30, "hobbies": ["reading", "traveling"]}'),
('Bob', '{"age": 25, "hobbies": ["swimming", "gaming"]}');

提取JSON数据

-- 提取Alice的年龄
SELECT JSON_EXTRACT(info, '$.age') AS age FROM users WHERE name = 'Alice';-- 或者使用更简洁的语法(MySQL 5.7.9+)
SELECT info->>"$.age" AS age FROM users WHERE name = 'Alice';-- 提取Bob的第一个爱好
SELECT JSON_EXTRACT(info, '$.hobbies[0]') AS first_hobby FROM users WHERE name = 'Bob';-- 或者
SELECT info->>"$.hobbies[0]" AS first_hobby FROM users WHERE name = 'Bob';

修改JSON数据

- 给Alice添加一个新的爱好"cooking"
UPDATE users
SET info = JSON_SET(info, '$.hobbies', JSON_ARRAY_APPEND(info->'$.hobbies', 'cooking'))
WHERE name = 'Alice';-- 或者,如果你知道Alice的爱好数组是什么,可以直接使用JSON_REPLACE或JSON_INSERT(如果爱好不存在的话)
UPDATE users
SET info = JSON_INSERT(info, '$.hobbies[2]', 'cooking')
WHERE name = 'Alice' AND JSON_CONTAINS(info->'$.hobbies', '"cooking"', '$') = 0;

注意:上面的JSON_SET示例可能不是添加新元素到数组的最直接方式,因为JSON_SET会替换整个数组。对于数组,你可能想使用JSON_ARRAY_APPEND或类似的函数。然而,JSON_ARRAY_APPEND不直接支持路径表达式作为目标,因此你可能需要先将数组提取出来,修改它,然后再设置回去。

使用虚拟列(Generated Columns)和索引(MySQL 5.7+)

在MySQL 5.7及以上版本中,你可以使用虚拟列(也称为生成列)来存储JSON文档中某个字段的值,并在这个虚拟列上创建索引。

-- 假设你有一个users表和一个info JSON列
ALTER TABLE users
ADD COLUMN age INT AS (JSON_UNQUOTE(JSON_EXTRACT(info, '$.age'))) STORED;-- 然后在这个虚拟列上创建索引
CREATE INDEX idx_age ON users(age);

注意,这里使用了STORED关键字,这意味着MySQL会物理地存储这个虚拟列的值,并可以在其上创建索引。如果你使用VIRTUAL(MySQL 5.7.6+),则MySQL不会在磁盘上存储这个列的值,但它仍然可以在查询优化期间使用它(但不能直接在其上创建索引)。

JSON特性常用函数

JSON_EXTRACT()

  •  提取 JSON 数据中的特定部分

JSON_INSERT()

  • 向 JSON 数据中插入新的部分,如果路径已存在则不会替换。

JSON_REPLACE() 

  • 替换 JSON 数据中的部分,如果路径不存在则不会添加。

JSON_REMOVE() 

  • 从 JSON 数据中移除指定的部分。

JSON_ARRAY() 和 JSON_OBJECT() 

  • 创建 JSON 数组和对象

JSON_KEYS() 

  • 获取 JSON 对象的所有键

 JSON_VALID()

  • 验证 JSON 数据的有效性。

JSON_QUOTE() 和 JSON_UNQUOTE() 

  • 将字符串转换为 JSON 格式的字符串,以及反向操作。

JSON_CONTAINS() 

  • 检查 JSON 文档是否包含指定的值。

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

JSON_CONTAINS_PATH() 

  • 检查 JSON 文档是否包含指定的路径。

JSON_ARRAY_APPEND() 

  • 向 JSON 数组追加元素。

 

 

 

 

 

 

 

 

 

http://www.lryc.cn/news/416980.html

相关文章:

  • 微信小程序 - 自定义计数器 - 优化(键盘输入校验)
  • Nacos 容器化安装和代理配置指南
  • css水波浪动画效果
  • SQL二次注入
  • 深入学习小程序开发第二天:数据绑定与动态更新
  • 【ai】 时间序列分析的python例子
  • 生成订单幂等性(防止订单重复提交)
  • IDEA自定义注释模版
  • Spring Cloud Gateway实现API访问频率限制
  • 单例模式:确保唯一实例的设计模式
  • MCU调试技巧-串口打印
  • VS+Qt+C++点云PCL三维显示编辑系统
  • 代码随想录算法训练营第七天(一)| 454.四数相加II 383. 赎金信
  • SpringBoot+Mybatis 分页
  • 学习进行到了第十七天(2024.8.5)
  • 【Nuxt】Layout 布局和渲染模式
  • C:指针学习(1)-学习笔记
  • 【LVS】负载均衡之NAT模式
  • ASP.NET Core 基础 - 入门实例
  • 机器人主板维修|ABB机械手主板元器件故障
  • 大数据Flink(一百零六):什么是阿里云实时计算Flink版
  • ERCOT中的专业术语解释
  • Python酷库之旅-第三方库Pandas(069)
  • 基于hutools的国密SM2、3、4
  • 进程的等待(非阻塞轮询+阻塞)和替换控制详解
  • 24/8/6算法笔记 支持向量机
  • 测试用例等级划分
  • 打造Perl编译器前端:自定义语言处理的魔法
  • Visual Studio 和 Visual Studio Code 的比较与应用偏向
  • Python打开JSON/CSV文件的正确方式(针对UnicodeDecodeError)