MySQL JSON 类型与函数

MySQL 从 5.7.8 版本开始原生支持 JSON 数据类型，并提供了一系列强大的函数来处理 JSON 数据。在 8.0 版本中，JSON 支持进一步增强，性能也得到了极大提升。

为什么使用 JSON 类型？

1. 灵活性：无需预先定义所有列，适合存储半结构化数据。
2. 数据验证：插入时自动验证 JSON 格式是否正确。
3. 高效查询：支持通过路径表达式直接访问内部项，配合虚似列可创建索引。
4. 节省空间：使用二进制格式存储，比纯文本更节省空间。

基础操作

创建包含 JSON 的表

CREATE TABLE user_profiles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    info JSON  -- 定义 JSON 列
);

插入 JSON 数据

-- 直接插入 JSON 字符串
INSERT INTO user_profiles (username, info) 
VALUES ('张三', '{"age": 25, "gender": "male", "tags": ["技术", "阅读"]}');

-- 使用 JSON_OBJECT 函数创建
INSERT INTO user_profiles (username, info)
VALUES ('李四', JSON_OBJECT("age", 30, "gender", "female", "tags", JSON_ARRAY("旅行", "美食")));

查询 JSON 数据

路径表达式基础

• $：代表整个 JSON 文档。
• $.key：代表对象的某个键。
• $[index]：代表数组的某个索引（从 0 开始）。
• $.*：代表对象的所有成员。
• **：通配符，表示多层嵌套。

提取数据

-- 使用 -> 提取（结果带引号）
SELECT info->'$.age' AS age FROM user_profiles;

-- 使用 ->> 提取（结果不带引号，最常用）
SELECT info->>'$.gender' AS gender FROM user_profiles;

-- 使用 JSON_EXTRACT 函数
SELECT JSON_EXTRACT(info, '$.tags[0]') AS first_tag FROM user_profiles;

条件查询

-- 查询年龄大于 25 的用户
SELECT username FROM user_profiles WHERE info->'$.age' > 25;

-- 查询包含 "技术" 标签的用户
SELECT username FROM user_profiles 
WHERE JSON_CONTAINS(info, '"技术"', '$.tags');

-- 检查 JSON 中是否存在某个键
SELECT username FROM user_profiles 
WHERE JSON_CONTAINS_PATH(info, 'one', '$.age');

修改 JSON 数据

JSON_SET、JSON_REPLACE、JSON_INSERT

函数	说明
JSON_SET	存在则替换，不存在则插入
JSON_REPLACE	存在则替换，不存在则忽略
JSON_INSERT	存在则忽略，不存在则插入

-- 修改年龄
UPDATE user_profiles 
SET info = JSON_SET(info, '$.age', 26) 
WHERE username = '张三';

-- 添加新属性 city
UPDATE user_profiles 
SET info = JSON_SET(info, '$.city', '北京') 
WHERE username = '张三';

-- 数组追加元素
UPDATE user_profiles 
SET info = JSON_ARRAY_APPEND(info, '$.tags', '编程') 
WHERE username = '张三';

-- 删除属性
UPDATE user_profiles 
SET info = JSON_REMOVE(info, '$.gender') 
WHERE username = '张三';

高级应用

JSON 索引（通过虚拟列）

JSON 列本身不支持直接建立索引。但可以通过生成的虚拟列（Generated Column）来实现对 JSON 属性的索引。

-- 1. 添加一个生成的虚拟列，提取 JSON 中的 age
ALTER TABLE user_profiles 
ADD COLUMN age_virtual INT AS (info->'$.age') STORED;

-- 2. 在虚拟列上创建索引
CREATE INDEX idx_age ON user_profiles(age_virtual);

-- 3. 后续查询 age 时，MySQL 会自动使用该索引
SELECT * FROM user_profiles WHERE age_virtual > 25;

JSON_TABLE 函数 (MySQL 8.0+)

将 JSON 数据转换为关系表格式，方便进行 JOIN 或聚合计算。

SELECT * FROM JSON_TABLE(
    '[{"id":1, "name":"A"}, {"id":2, "name":"B"}]',
    "$[*]" COLUMNS(
        id INT PATH "$.id",
        name VARCHAR(20) PATH "$.name"
    )
) AS jt;

常用 JSON 函数速查表

分类	函数	说明
创建	JSON_ARRAY	创建 JSON 数组
	JSON_OBJECT	创建 JSON 对象
查询	JSON_EXTRACT	提取数据 (别名 ->)
	->>	提取并去引号 (Inline Path operator)
	JSON_CONTAINS	检查是否包含指定值
	JSON_LENGTH	返回 JSON 文档/数组/对象的长度
修改	JSON_SET	插入或更新
	JSON_INSERT	仅插入
	JSON_REPLACE	仅替换
	JSON_REMOVE	删除指定路径的值
	JSON_ARRAY_APPEND	向数组末尾追加值
实用	JSON_VALID	验证 JSON 格式
	JSON_PRETTY	格式化输出
	JSON_STORAGE_SIZE	查看存储大小

小结

1. JSON 类型 提供了极大的灵活性，适合存储多变的配置信息。
2. ->> 操作符是提取 JSON 数据最简洁的方式。
3. 虚拟列索引 是解决 JSON 查询性能问题的关键手段。
4. JSON_TABLE 让 JSON 数据与 SQL 的交互变得无比强大。

练习

1. 创建一个 products 表，使用 JSON 存储商品规格（如颜色、尺寸）。
2. 写一条 SQL，查询所有包含 "Red" 颜色规格的商品。
3. 使用 JSON_SET 更新一个用户的多个配置项。
4. 尝试创建一个虚拟列并在其上建立索引，观察 EXPLAIN 的变化。