【MySQL】：轻松掌握 JSON 函数

已于 2024-12-27 00:02:21 修改
阅读量1.7k 收藏 41
点赞数 36
分类专栏： MySQL 文章标签： mysql json 数据库
于 2024-11-24 21:57:52 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_34272964/article/details/144009563
版权

前言

在现代数据库设计中，JSON 格式的数据因其灵活性和可扩展性而变得越来越受欢迎。MySQL 8.0 引入了许多强大的 JSON 函数，使得处理 JSON 数据变得更加方便和高效。本文将通过一个简化的订单表 orders，展示如何使用这些 JSON 函数来创建、搜索、修改和验证 JSON 数据，从而优化订单管理系统。

1. 表结构定义

1.1 创建订单表 orders

首先，我们定义一个简单的订单表 orders，其中包含一个主键 id 和一个存储订单详细信息的 JSON 字段 data。

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    data JSON
);

1.2 插入示例数据

接下来，我们插入一些示例数据，模拟不同水果的订单信息。

INSERT INTO orders (DATA) VALUES
  ('{"fruit": "Apple", "quantity": 100, "price": 25.0, "labels": ["Fresh", "Sweet"]}'),
  ('{"fruit": "Banana", "quantity": 150, "price": 8.0, "labels": ["Ripe"]}'),
  ('{"fruit": "Cherry", "quantity": 120, "price": 15.0, "labels": ["Small"]}'),
  ('{"fruit": "Apple", "quantity": 50, "price": 12.5, "labels": ["Fresh", "Sweet"]}');

1.3 查询全部数据

mysql> SELECT * FROM orders;
+----+----------------------------------------------------------------------------------+
| id | data                                                                             |
+----+----------------------------------------------------------------------------------+
|  1 | {"fruit": "Apple", "price": 25.0, "labels": ["Fresh", "Sweet"], "quantity": 100} |
|  2 | {"fruit": "Banana", "price": 8.0, "labels": ["Ripe"], "quantity": 150}           |
|  3 | {"fruit": "Cherry", "price": 15.0, "labels": ["Small"], "quantity": 120}         |
|  4 | {"fruit": "Apple", "price": 12.5, "labels": ["Fresh", "Sweet"], "quantity": 50}  |
+----+----------------------------------------------------------------------------------+

2. 使用 JSON 函数

2.1 创建 JSON 值

  • JSON_ARRAY(val1, val2, ...)
    创建一个 JSON 数组。val1, val2, ... : 要包含在 JSON 数组中的值。

mysql> SELECT JSON_ARRAY('Apple', 'Banana', 'Cherry');
+-----------------------------------------+
| JSON_ARRAY('Apple', 'Banana', 'Cherry') |
+-----------------------------------------+
| ["Apple", "Banana", "Cherry"]           |
+-----------------------------------------+

  • JSON_OBJECT(key1, value1, key2, value2, ...)
    创建一个 JSON 对象。key1, key2: JSON 对象的键；value1, value2: 与键相关联的值。

mysql> SELECT JSON_OBJECT('fruit', 'Apple', 'quantity', 100, 'price', 25.0, 'labels', JSON_ARRAY('Fresh', 'Sweet'));
+-------------------------------------------------------------------------------------------------------+
| JSON_OBJECT('fruit', 'Apple', 'quantity', 100, 'price', 25.0, 'labels', JSON_ARRAY('Fresh', 'Sweet')) |
+-------------------------------------------------------------------------------------------------------+
| {"fruit": "Apple", "price": 25.0, "labels": ["Fresh", "Sweet"], "quantity": 100}                      |
+-------------------------------------------------------------------------------------------------------+

2.2 搜索 JSON 值

  • JSON_EXTRACT(json_doc, path[, type])
    从 JSON 文档中选择的数据返回与 path 参数匹配的部分。

mysql> SELECT id, JSON_EXTRACT(data, '$.price') AS price, JSON_EXTRACT(data, '$.labels') AS labels FROM orders ORDER BY price DESC;
+----+-------+--------------------+
| id | price | labels             |
+----+-------+--------------------+
|  1 | 25.0  | ["Fresh", "Sweet"] |
|  3 | 15.0  | ["Small"]          |
|  4 | 12.5  | ["Fresh", "Sweet"] |
|  2 | 8.0   | ["Ripe"]           |
+----+-------+--------------------+


mysql> SELECT id, JSON_EXTRACT(data, '$.labels[0]') AS label FROM orders;
+----+---------+
| id | label   |
+----+---------+
|  1 | "Fresh" |
|  2 | "Ripe"  |
|  3 | "Small" |
|  4 | "Fresh" |
+----+---------+

  • JSON_CONTAINS(target, candidate[, path])
    函数用于检查一个 JSON 文档（target）是否包含另一个 JSON 文档或值（candidate），或者检查在指定的路径（path）下是否包含该候选者。它返回一个整数来表示结果：如果找到匹配项，则返回 1；否则返回 0。

mysql> SELECT id, data FROM orders WHERE JSON_CONTAINS(data, '"Apple"', '$.fruit');
+----+----------------------------------------------------------------------------------+
| id | data                                                                             |
+----+----------------------------------------------------------------------------------+
|  1 | {"fruit": "Apple", "price": 25.0, "labels": ["Fresh", "Sweet"], "quantity": 100} |
|  4 | {"fruit": "Apple", "price": 12.5, "labels": ["Fresh", "Sweet"], "quantity": 50}  |
+----+----------------------------------------------------------------------------------+

  • JSON_SEARCH(json_doc, one_or_all, search_str[, escape_char[, path] ...])
    函数返回 JSON 文档中指定字符串的路径。 json_doc，如果 search_str 或 path 参数为 、 path 在文档中不存在，或者 search_ 如果未找到 str，则返回 。

mysql> SELECT id, JSON_SEARCH(data, 'one','%e%', 'a') FROM orders;
+----+-------------------------------------+
| id | JSON_SEARCH(data, 'one','%e%', 'a') |
+----+-------------------------------------+
|  1 | "$.fruit"                           |
|  2 | "$.labels[0]"                       |
|  3 | "$.fruit"                           |
|  4 | "$.fruit"                           |
+----+-------------------------------------+

mysql> SELECT id, JSON_SEARCH(data,'all' ,'%e%') FROM orders;
+----+-------------------------------------------+
| id | JSON_SEARCH(data,'all' ,'%e%')            |
+----+-------------------------------------------+
|  1 | ["$.fruit", "$.labels[0]", "$.labels[1]"] |
|  2 | "$.labels[0]"                             |
|  3 | "$.fruit"                                 |
|  4 | ["$.fruit", "$.labels[0]", "$.labels[1]"] |
+----+-------------------------------------------+

2.3 修改 JSON 值

  • JSON_SET(json_doc, path, val[, path, val] ...)
    函数在 JSON 文档中插入或更新数据并返回结果。 如果任一参数是 或 path，则返回 （如果指定）。

mysql> UPDATE orders SET data = JSON_SET(data, '$.price', 26.0) WHERE id = 1;

mysql> SELECT * FROM orders WHERE id = 1;
+----+----------------------------------------------------------------------------------+
| id | data                                                                             |
+----+----------------------------------------------------------------------------------+
|  1 | {"fruit": "Apple", "price": 26.0, "labels": ["Fresh", "Sweet"], "quantity": 100} |
+----+----------------------------------------------------------------------------------+

  • JSON_REPLACE(json_doc, path, val[, path, val] ...)
    函数替换 JSON 文档中的现有值并返回结果。 如果任何参数为 ，则返回 。

mysql> UPDATE orders SET data = JSON_REPLACE(data, '$.quantity', 110) WHERE id = 1;

mysql> SELECT * FROM orders WHERE id = 1;
+----+----------------------------------------------------------------------------------+
| id | data                                                                             |
+----+----------------------------------------------------------------------------------+
|  1 | {"fruit": "Apple", "price": 26.0, "labels": ["Fresh", "Sweet"], "quantity": 110} |
+----+----------------------------------------------------------------------------------+

mysql> UPDATE orders SET data = JSON_REPLACE(data, '$.labels[0]', 'Crisp‌') WHERE id = 1;

mysql> SELECT * FROM orders WHERE id = 1;
+----+----------------------------------------------------------------------------------+
| id | data                                                                             |
+----+----------------------------------------------------------------------------------+
|  1 | {"fruit": "Apple", "price": 26.0, "labels": ["Crisp‌", "Sweet"], "quantity": 110} |
+----+----------------------------------------------------------------------------------+

  • JSON_REMOVE(json_doc, path[, path] ...)
    函数从 JSON 文档中删除数据并返回结果。path 参数从左到右计算。 通过评估一个路径生成的文档将成为评估下一个路径所依据的新值。

mysql> UPDATE orders SET data = JSON_REMOVE(data, '$.quantity') WHERE id = 1;

mysql> SELECT * FROM orders;
+----+---------------------------------------------------------------------------------+
| id | data                                                                            |
+----+---------------------------------------------------------------------------------+
|  1 | {"fruit": "Apple", "price": 26.0, "labels": ["Crisp‌", "Sweet"]}                 |
|  2 | {"fruit": "Banana", "price": 8.0, "labels": ["Ripe"], "quantity": 150}          |
|  3 | {"fruit": "Cherry", "price": 15.0, "labels": ["Small"], "quantity": 120}        |
|  4 | {"fruit": "Apple", "price": 12.5, "labels": ["Fresh", "Sweet"], "quantity": 50} |
+----+---------------------------------------------------------------------------------+

2.4 返回 JSON 属性

  • JSON_KEYS(json_doc[, path])
    函数以 JSON 数组的形式返回 JSON 对象的顶级值的键。 如果指定了 path 参数，则返回所选路径的顶级键。

mysql> SELECT id, JSON_KEYS(data) AS `keys` FROM orders;
+----+------------------------------------------+
| id | keys                                     |
+----+------------------------------------------+
|  1 | ["fruit", "price", "labels"]             |
|  2 | ["fruit", "price", "labels", "quantity"] |
|  3 | ["fruit", "price", "labels", "quantity"] |
|  4 | ["fruit", "price", "labels", "quantity"] |
+----+------------------------------------------+

2.5 生成 JSON 表

  • JSON_TABLE(expr, path COLUMNS (column_list) [AS] alias)

    从 JSON 文档中提取数据，并将其作为具有指定列的关系表返回。

mysql> SELECT o.id, jt.fruit, jt.quantity, jt.price, jt.comments
FROM orders o,
JSON_TABLE(
    o.data,
    '$' COLUMNS(
        fruit VARCHAR(255) PATH '$.fruit',
        quantity INT PATH '$.quantity',
        price DECIMAL(10, 2) PATH '$.price',
        comments JSON PATH '$.comments'
    )
) AS jt;
+----+--------+----------+-------+----------+
| id | fruit  | quantity | price | comments |
+----+--------+----------+-------+----------+
|  1 | Apple  | NULL     | 26.00 | NULL     |
|  2 | Banana |      150 | 8.00  | NULL     |
|  3 | Cherry |      120 | 15.00 | NULL     |
|  4 | Apple  |       50 | 12.50 | NULL     |
+----+--------+----------+-------+----------+

2.6 其他 JSON 函数

  • JSON_PRETTY(json_val)

    格式化 JSON 文档，使其更易读。

mysql> SELECT id, JSON_PRETTY(data) FROM orders;
+----+-----------------------------------------------------------------------------------------------+
| id | JSON_PRETTY(data)                                                                             |
+----+-----------------------------------------------------------------------------------------------+
|  1 | {
  "fruit": "Apple",
  "price": 26.0,
  "labels": [
    "Crisp‌",
    "Sweet"
  ]
}                  |
|  2 | {
  "fruit": "Banana",
  "price": 8.0,
  "labels": [
    "Ripe"
  ],
  "quantity": 150
}             |
|  3 | {
  "fruit": "Cherry",
  "price": 15.0,
  "labels": [
    "Small"
  ],
  "quantity": 120
}           |
|  4 | {
  "fruit": "Apple",
  "price": 12.5,
  "labels": [
    "Fresh",
    "Sweet"
  ],
  "quantity": 50
} |
+----+-----------------------------------------------------------------------------------------------+

  • JSON_LENGTH(json_doc[, path] ...)

    返回 JSON 文档的长度。

mysql> SELECT id, JSON_LENGTH(data) AS length FROM orders;
+----+--------+
| id | length |
+----+--------+
|  1 |      3 |
|  2 |      4 |
|  3 |      4 |
|  4 |      4 |
+----+--------+

mysql> SELECT id, JSON_LENGTH(data, '$.labels') AS length FROM orders;
+----+--------+
| id | length |
+----+--------+
|  1 |      2 |
|  2 |      1 |
|  3 |      1 |
|  4 |      2 |
+----+--------+
  • JSON_VALID(val) 

    判断 JSON 值是否有效。返回 0 或 1 以指示该值是否为有效的 JSON。

mysql> SELECT JSON_VALID('hello'), JSON_VALID('"hello"');
+---------------------+-----------------------+
| JSON_VALID('hello') | JSON_VALID('"hello"') |
+---------------------+-----------------------+
|                   0 |                     1 |
+---------------------+-----------------------+

注意事项

  • 大小写敏感：大多数 JSON 函数对大小写敏感，因此在比较字符串时需要注意。
  • 参数有效性：当输入参数为空或无效时，函数可能会返回 NULL。
  • 性能优化：对于包含大量 JSON 数据的表，直接使用 JSON 函数可能导致性能下降。为了提高查询效率，可以考虑使用虚拟列（Generated Columns）结合索引来加速特定条件下的查询，或者将经常查询的字段提取到单独的列中进行索引。

总结

MySQL 8.0 提供了丰富的 JSON 函数，使得处理 JSON 数据变得更加简单和高效。通过本文的详细介绍和实际应用示例，读者可以更好地理解和利用这些函数，在实际开发中发挥其最大价值。合理的设计和优化也是确保系统性能的关键因素之一。通过合理使用这些 JSON 函数，可以显著提升数据处理的灵活性和效率。希望本文能帮助读者在使用 MySQL 8.0 处理 JSON 数据时更加得心应手。

【MYSQL学习】MySQL内置函数：JSON函数的5大妙用你GET到了吗？
java专栏
10-15 998
通过以上介绍，相信你对 JSON 函数的5大妙用有了更加全面的理解。从 JSON 函数的基本概念到使用场景、注意事项和高级用法，每个知识点都在编写复杂查询时发挥着重要作用。记住，理论知识是基础，实践才是检验真理的唯一标准。多动手尝试，你会发现自己在不知不觉中已经成为了 MySQL 高手！
11.10 JSON函数
weixin_41275260的博客
06-01 659
11.10　JSON函数 JSON函数是对数据库中JSON数据类型的数据进行处理的函数，MySQL中内置了一系列的JSON函数。本节就简单介绍一下MySQL中常用的JSON函数。 为了更好地理解JSON函数的用法和作用，我们将会以示例的形式详细介绍每种JSON函数的作用和用法。首先，在名称为test的数据库中创建数据表test_json。 mysql> CREATE T...
MySQL JSON 常用函数
10-19 2881
MySQL 提供了丰富的函数对 JSON 类型执行操作，详见。
MySQL中常用的 JSON查询函数
最新发布
小李同学的博客
03-09 1074
在当今的软件开发中，数据的存储和处理变得越来越多样化。MySQL 作为一款广泛使用的关系型数据库，从 5.7 版本开始引入了对 JSON 数据类型的支持，这使得开发者能够在关系型数据库中高效地存储和处理半结构化数据。和这三个核心函数，就像是三把利刃，为我们在 MySQL 中处理 JSON 数据提供了强大的支持。函数用于检查一个 JSON 文档是否包含另一个指定的 JSON 文档。如果包含，则返回1；否则返回0。函数用于去除 JSON 字符串中的引号。
Mysql-JSON函数
weixin_44681259的博客
02-21 224
Mysql当中Json相关的函数详解
怪咖@的博客
04-30 1万+
从5.7开始，MySQL开始支持json类型，用于存储JSON数据。提供数据类型的同时也提供了很多关于json的函数供我们使用，本篇文章基本上都来源于官网;
mysql的json函数
qq_39726798的博客
11-03 7014
mysql5.7以上提供了一种新的字段格式json，大概是mysql想把非关系型和关系型数据库一口通吃，所以推出了这种非常好用的格式，这样，我们的很多基于mongoDb或者clickHouse的业务都可以用mysql去实现了。当然了，5.7的版本只是最基础的版本，对于海量数据的效率是远远不够的，不过这些都在mysql8.0解决了。今天我们就针对mysql的json数据格式操作做一个简单的介绍 JSON 数组包含由逗号分隔并包含在[和] 字符中的值列表： ["abc", 10, null, true, fal
MYSQL JSON 函数
程小明的博客
07-01 8694
MYSQL JSON函数 json API
轻松上手MYSQL：掌握MYSQL聚合函数，数据分析不再难
制定明确可量化的目标，坚持默默的做事。
07-14 5298
本文介绍了如何通过轻松上手MYSQL并掌握其聚合函数，使数据分析变得简单易行。MYSQL的聚合函数是数据处理与分析的强大工具，能够帮助用户轻松汇总、计算统计数据。通过本文的指导，读者将学会使用SUM、AVG、COUNT等聚合函数，从而快速完成数据分析和整理工作。无论你是初学者还是有一定数据库基础的用户，都能通过本文快速掌握MYSQL聚合函数的使用方法，让数据分析变得不再困难。跟随本文，开启你的MYSQL数据分析之旅，释放数据的巨大潜力。
SQL 实战：MySQL JSON 函数解析 – 处理非结构化数据
秋元的博客
12-28 1518
在现代应用中，非结构化数据如 JSON 格式广泛应用于存储复杂的配置信息、动态表单以及嵌套数据。MySQL 从 5.7 版本起正式支持，允许开发者直接在数据库中存储和查询 JSON 格式的数据。本文将介绍如何使用 MySQL 的处理复杂的嵌套 JSON 字段，解析和查询非结构化数据。在用户管理系统中，users表中的config字段存储用户配置信息，格式为 JSON。我们需要查找所有具有特定权限或角色的用户。
发现最新的 MySQL 功能：增强的 JSON 函数、高级安全性和改进的性能
hongfu951的专栏
09-28 865
MySQL 仍将是免费的关系数据库管理系统之一，因其可靠性、性能和易用性而受到广泛认可。在每个新版本中，MySQL 都会引入更多功能和改进，重点关注更好的数据库管理、应用程序开发和各种其他性能。现在，让我们仔细看看最新 MySQL 版本中包含的一些最重要的功能 2.JSON高级功能 MySQL 中 JSON 支持的领先开发领域之一已作为数据交换格式得到广泛支持。最新版本引入了 JSON 功能的许多增强功能，使在 MySQL 数据库范围内处理 JSON 数据变得更加容易。 - **JSON\_TAB
详解Mysql中的JSON系列操作函数
12-16
前言 JSON是一种轻量级的数据交换格式，采用了独立于语言的文本格式，类似XML，但是比XML简单，易读并且易编写。对机器来说易于解析和生成，并且会减少网络带宽的传输。 JSON的格式非常简单：名称/键值。之前MySQL版本里面要实现这样的存储，要么用VARCHAR要么用TEXT大文本。 MySQL5.7发布后，专门设计了JSON数据类型以及关于这种类型的检索以及其他函数解析。 下面一起来实际操作一下。 创建带有 JSON 字段的表 比如一个‘文章’表，字段包括 id、标题 title、标签 tags 一篇文章会有多个标签，tags 就可以设为 JSON 类型 建表语句如下： CREATE
对MySQL中JSON数据类型的操作和分析
热门推荐
manongpengzai的博客
08-15 2万+
JSON作为以键值对为主的存储形式，符合很多业务场景，尤其对于python中dict字典数据类型，能直接相互转换，更加适应了python的某些使用情况。 此外，JSON作为不限定固定结构的键值对，能方便灵活的使用在数据变动性比较大的场景，如对于网络数据的爬取收集等，当然其中也存在一些问题，下文再分析。这里本文先列举MySQL中对一些JSON数据类型的常用语句和函数（参考MySQL官方文档，建议大家
MySQL json相关函数详解
l_s_r的博客
11-08 1421
MySQL提供了一系列的JSON函数，用于解析、提取、修改和操作JSON数据。以下是一些常用的JSON函数及其功能。
MySQL常用Json函数
baidu_41267789的博客
07-01 192
转载：https://www.cnblogs.com/waterystone/p/5626098.html
mysql解析json/数组
小崔的专栏
01-26 4308
mysql在5.7开始支持json解析了 也可以解析数组哦！ 直接上demo: 　SELECT Substr(col, 2, Length(col) - 2), Length(col) FROM (SELECT Json_extract(Json_extract(Json_extract(state, "$.tpl"),"$.items" ), "$[0].url") AS col FROM page ORDER BY id DESC .
评论 16
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

寻找09之夏

喜欢就点赞or赞赏。

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

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

余额充值