XuLaLa.Tech

MySQL 5.7 版本引入了对 JSON 数据类型的原生支持，这为存储和查询 JSON 数据提供了极大的便利。MySQL 中的 JSON_VALID 和JSON_EXTRACT 函数在业务开发的过程中可以极大提供我们处理数据的效率，特别对业务中灵活字段的处理场景，我们可以快速的对这些数据中提取想要的字段。

文章目录

• 1 一、MySQL 中的 JSON 数据类型
• 2 二、使用 JSON_VALID 验证 JSON 数据
• 3 三、JSON 数据的存储与管理
• 4 四、使用 JSON_EXTRACT 提取 JSON 数据
• 5 五、处理复杂的 JSON 数据结构
• 6 六、JSON 函数在查询优化中的应用

一、MySQL 中的 JSON 数据类型

JSON（JavaScript Object Notation）是一种轻量级的数据交换格式，易于人阅读和编写，同时也易于机器解析和生成。在 MySQL 中，JSON 数据类型提供了一种存储和操作 JSON 数据的方式。

实例：

CREATE TABLE products (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255),
details JSON
);
INSERT INTO products (name, details) VALUES ('Laptop', '{"color": "black", "price": 999.99}');

二、使用 JSON_VALID 验证 JSON 数据

JSON_VALID 函数用于检查一个字符串是否是有效的 JSON 格式。如果字符串是有效的 JSON，函数返回 1；如果不是，则返回 0。

SELECT JSON_VALID('{"name": "MySQL", "version": "8.0"}');
-- 返回 1，表示是有效的 JSON
SELECT JSON_VALID('This is not a JSON string');
-- 返回 0，表示不是有效的 JSON

三、JSON 数据的存储与管理

在 MySQL 中，存储 JSON 数据时，可以使用 JSON_SET、JSON_INSERT 等函数来更新 JSON 对象。

UPDATE products SET details = JSON_SET(details, '$.price', 1099.99) WHERE id = 1;
-- 更新 JSON 对象中的 price 属性

四、使用 JSON_EXTRACT 提取 JSON 数据

JSON_EXTRACT 函数用于从 JSON 文档中提取数据。它允许你通过路径表达式来定位并提取 JSON 对象或数组中的值。

SELECT JSON_EXTRACT(details, '$.color') FROM products WHERE id = 1;
-- 提取 JSON 对象中的 color 属性值
SELECT JSON_EXTRACT(details, '$.price') FROM products WHERE id = 1;
-- 提取 JSON 对象中的 price 属性值

五、处理复杂的 JSON 数据结构

当 JSON 数据结构变得复杂时，JSON_EXTRACT 可以配合使用数组索引和嵌套路径来提取深层数据。

CREATE TABLE orders (
id INT AUTO_INCREMENT PRIMARY KEY,
customer VARCHAR(255),
order_details JSON
);
INSERT INTO orders (customer, order_details) VALUES ('John Doe', '[{"product": "Laptop", "quantity": 2}, {"product": "Mouse", "quantity": 1}]');
SELECT JSON_EXTRACT(order_details, '$[0].product') FROM orders WHERE id = 1;
-- 提取第一个订单项的产品名称

六、JSON 函数在查询优化中的应用

使用 JSON 函数可以在查询中直接处理 JSON 数据，减少应用程序层的数据处理负担，提高查询效率。

SELECT name, JSON_EXTRACT(details, '$.price') AS price FROM products WHERE JSON_EXTRACT(details, '$.price') > 500;
-- 选择价格超过 500 的产品及其名称

MySQL 的 JSON 支持为开发者提供了强大的工具来处理 JSON 数据。通过 JSON_VALID 和 JSON_EXTRACT 函数，开发者可以确保数据的有效性，并灵活地从 JSON 文档中提取所需信息。