MySQL中JSON 函数的具体使用

2025-07-26 09:29 数据库作者：寻找09之夏

前言

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

1. 表结构定义

1.1 创建订单表 javascriptorders

首先，我们定义一个简单的订单表 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                                                                             |
+--www.devze.com--+----------------------------------------------------------------------------------+
|  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" php |
|  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 | {"fruitjavascript": "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 数据时更加得心应手。

到此这篇关于MySwww.devze.comQL中JSON 函数的具体使用的文章就介绍到这了,更多相关MySQL JSON 函数内容请搜索编程客栈(www.devze.com)以前的文章或继续浏览下面的相关文章希望大家以后多多支持编程客栈(www.devze.com)！

继续阅读：mysql json

MySQL中JSON 函数的具体使用

目录

前言

1. 表结构定义

1.1 创建订单表 javascriptorders

1.2 插入示例数据

1.3 查询全部数据

2. 使用 JSON 函数

2.1 创建 JSON 值

2.2 搜索 JSON 值

2.3 修改 JSON 值

2.4 返回 JSON 属性

2.5 生成 JSON 表

2.6 其他 JSON 函数

总结

更多精彩内容

精彩评论

最新数据库

基于MySQL实现基础图数据库的详细步骤

初始Redis概念、特性、安装使用场景

搭建Caffeine+Redis多级缓存机制

Redis 部署模式详解

MySQL 排序规则Collation实例详解

数据库排行榜

Hadoop Key Management Server (KMS)配置及测试

spark报错ERROR ObjectStore: Version information found in metastore differs 2.1.0 from expected schema version 1.2.0. Schema verififcation is disabled hive.metastore.schema.verification so setting version.

解决Navicat远程连接MySQL出现 10060 unknow error的方法

Navicat连接Oracle数据库的详细步骤与注意事项

redis-cluster集群调优之cluster-require-full-coverage参数