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

【MySQL】MySQL的JSON特性

news 2025/9/25 11:26:53

引言

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 数组追加元素。

引言