跳到主要内容

MySQL JSON 类型与函数

MySQL 从 5.7.8 版本开始原生支持 JSON 数据类型，并提供了一系列强大的函数来处理 JSON 数据。在 8.0 版本中，JSON 支持进一步增强，性能也得到了极大提升。本章将深入讲解 JSON 类型的存储原理、完整函数列表和实战应用。

为什么使用 JSON 类型？​

在传统的 SQL 数据库设计中，表结构是固定的，每列都有预定义的类型。但现代应用往往需要存储灵活多变的数据结构，比如：

  • 用户配置项（不同用户可能有不同的配置选项）
  • 商品属性（不同品类的商品属性差异很大）
  • 日志数据（结构可能随时间变化）
  • 第三方 API 响应（格式可能更新）

JSON 类型正是为解决这类问题而设计的。它结合了 SQL 数据库的事务安全性和 NoSQL 数据库的灵活性。

JSON 类型 vs TEXT 存储的区别​

很多人可能会问：为什么不直接用 TEXT 或 VARCHAR 存储 JSON 字符串？实际上，MySQL 原生 JSON 类型提供了显著优势：

特性JSON 类型TEXT 类型
格式验证插入时自动验证 JSON 格式无验证，可存储任意文本
存储格式二进制优化格式，读取更快原始文本，需每次解析
数据访问支持路径表达式直接访问内部元素需应用层解析
索引支持可通过虚拟列建索引无法直接索引内部字段
部分更新支持原地更新优化需整体替换
存储空间略大于或等于 TEXT原始大小

JSON 存储原理​

理解 JSON 的底层存储机制，有助于我们更好地设计数据结构和优化查询性能。

二进制存储格式​

MySQL 不是简单地将 JSON 文本原样存储，而是将其转换为一种优化的二进制格式。这种格式具有以下特点：

快速访问：二进制格式允许 MySQL 直接定位到文档中的任意元素，而无需从头扫描整个文档。这就像在一本书中，你可以直接翻到某一页，而不必从第一页开始逐页查找。

结构化存储：JSON 文档被解析为内部结构，包含：

  • 类型信息（对象、数组、字符串、数字等）
  • 元素位置索引
  • 实际数据值

空间效率：二进制格式的存储空间与 TEXT 类型相当，有时甚至更小。这是因为：

  • 重复的键名只存储一次
  • 数字以二进制形式存储
  • 空白符和格式化字符被移除

规范化处理​

当 JSON 文档被存储时，MySQL 会进行规范化处理：

  1. 重复键处理：如果一个 JSON 对象中有重复的键，只保留最后一个出现的键值对
-- 插入时有重复的 name 键
INSERT INTO users (data) VALUES ('{"name": "张三", "age": 25, "name": "李四"}');

-- 实际存储的是（后面的 name 覆盖了前面的）
SELECT data FROM users;
-- {"age": 25, "name": "李四"}

  1. 键排序：MySQL 会自动对对象的键进行排序，这有助于提高查找效率

  2. 空白符处理：多余的空白符会被移除，保留最小化的格式

存储大小限制​

JSON 文档的大小受 max_allowed_packet 系统变量限制（默认 4MB，最大可设为 1GB）。可以使用 JSON_STORAGE_SIZE() 函数查看实际存储大小：

SELECT JSON_STORAGE_SIZE('{"name": "张三", "age": 25, "hobbies": ["reading", "coding"]}');
-- 返回存储所需的字节数

创建 JSON 数据​

建表时定义 JSON 列​

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    profile JSON,           -- 用户资料
    preferences JSON,       -- 用户偏好设置
    metadata JSON           -- 元数据
);

插入 JSON 数据​

MySQL 提供多种方式插入 JSON 数据：

方式一：直接使用 JSON 字符串

INSERT INTO users (username, profile) 
VALUES (
    'zhangsan', 
    '{"name": "张三", "age": 28, "city": "北京", "skills": ["Java", "MySQL", "Redis"]}'
);

方式二：使用 JSON_OBJECT() 函数

INSERT INTO users (username, profile)
VALUES (
    'lisi',
    JSON_OBJECT(
        'name', '李四',
        'age', 30,
        'city', '上海',
        'skills', JSON_ARRAY('Python', 'Django', 'PostgreSQL')
    )
);

JSON_OBJECT() 函数接受键值对参数，返回一个 JSON 对象。键名必须是字符串，值可以是任何有效的 JSON 值。

方式三：使用 JSON_ARRAY() 函数

-- 创建 JSON 数组
SELECT JSON_ARRAY(1, 'hello', NULL, TRUE, NOW());
-- [1, "hello", null, true, "2024-01-15 10:30:00"]

-- 嵌套使用
INSERT INTO users (username, profile)
VALUES (
    'wangwu',
    JSON_OBJECT(
        'name', '王五',
        'projects', JSON_ARRAY(
            JSON_OBJECT('name', '项目A', 'role', '负责人'),
            JSON_OBJECT('name', '项目B', 'role', '成员')
        )
    )
);

方式四：使用 JSON_MERGE_PRESERVE() 合并多个 JSON

-- 合并多个 JSON 对象
INSERT INTO users (username, profile)
VALUES (
    'zhaoliu',
    JSON_MERGE_PRESERVE(
        '{"name": "赵六"}',
        '{"age": 25}',
        '{"city": "深圳"}',
        '{"skills": ["Go", "Docker"]}'
    )
);
-- 结果: {"name": "赵六", "age": 25, "city": "深圳", "skills": ["Go", "Docker"]}

JSON_MERGE_PRESERVE vs JSON_MERGE_PATCH​

这两个函数都用于合并 JSON，但行为不同：

-- JSON_MERGE_PRESERVE: 保留重复键的所有值（作为数组）
SELECT JSON_MERGE_PRESERVE(
    '{"tags": ["red"]}',
    '{"tags": ["blue"]}'
);
-- 结果: {"tags": ["red", "blue"]}

-- JSON_MERGE_PATCH: 后面的值覆盖前面的值
SELECT JSON_MERGE_PATCH(
    '{"name": "张三", "age": 25}',
    '{"age": 30, "city": "北京"}'
);
-- 结果: {"name": "张三", "age": 30, "city": "北京"}

选择建议：

  • 需要合并数组或保留所有值时，使用 JSON_MERGE_PRESERVE
  • 需要更新/覆盖某些字段时，使用 JSON_MERGE_PATCH

JSON 路径表达式​

路径表达式是访问 JSON 文档内部元素的核心机制。掌握路径语法是高效使用 JSON 函数的关键。

基本语法​

路径表达式以 $ 开头，表示 JSON 文档的根：

语法说明示例
$文档根元素$
$.key对象的键$.name
$[index]数组的索引（从 0 开始）$[0], $[2]
$.key[ index ]嵌套访问$.skills[0]
$.*对象的所有成员值$.*
$[*]数组的所有元素$[*]
$.key.*嵌套对象的所有成员$.address.*
**递归匹配所有路径$**.name

路径表达式示例​

假设有以下 JSON 文档：

{
    "name": "张三",
    "age": 28,
    "skills": ["Java", "MySQL", "Redis"],
    "address": {
        "city": "北京",
        "district": "朝阳"
    },
    "projects": [
        {"name": "项目A", "budget": 100000},
        {"name": "项目B", "budget": 200000}
    ]
}

各路径表达式对应的值：

-- $.name → "张三"
-- $.age → 28
-- $.skills → ["Java", "MySQL", "Redis"]
-- $.skills[0] → "Java"
-- $.skills[1] → "MySQL"
-- $.skills[-1] → "Redis"（-1 表示最后一个元素）
-- $.address.city → "北京"
-- $.projects[0].name → "项目A"
-- $.projects[1].budget → 200000
-- $.* → ["张三", 28, ["Java", "MySQL", "Redis"], {...}, [...]]

数组范围语法​

MySQL 8.0 支持数组范围语法：

-- 获取数组的前两个元素
SELECT JSON_EXTRACT('["a", "b", "c", "d", "e"]', '$[0 to 1]');
-- 结果: ["a", "b"]

-- 获取数组的最后三个元素
SELECT JSON_EXTRACT('["a", "b", "c", "d", "e"]', '$[2 to last]');
-- 结果: ["c", "d", "e"]

-- last 关键字表示最后一个元素的索引
SELECT JSON_EXTRACT('["a", "b", "c"]', '$[last]');
-- 结果: "c"

-- 相对索引
SELECT JSON_EXTRACT('["a", "b", "c", "d", "e"]', '$[last-2 to last-1]');
-- 结果: ["c", "d"]

键名需要引号的情况​

如果键名包含空格或特殊字符，必须用双引号括起来：

SELECT JSON_EXTRACT('{"first name": "张三", "last-name": "李"}', '$."first name"');
-- 结果: "张三"

SELECT JSON_EXTRACT('{"key-with-dash": "value"}', '$."key-with-dash"');
-- 结果: "value"

查询 JSON 数据​

JSON_EXTRACT() 函数​

JSON_EXTRACT() 是最基础的 JSON 查询函数，从 JSON 文档中提取指定路径的值：

SELECT JSON_EXTRACT(profile, '$.name') FROM users WHERE username = 'zhangsan';
-- 结果: "张三"（带引号的字符串）

SELECT JSON_EXTRACT(profile, '$.age') FROM users WHERE username = 'zhangsan';
-- 结果: 28（数字不带引号）

SELECT JSON_EXTRACT(profile, '$.skills[0]') FROM users WHERE username = 'zhangsan';
-- 结果: "Java"

-> 和 ->> 操作符​

这两个操作符是 JSON_EXTRACT() 的简写形式：

-> 操作符：等价于 JSON_EXTRACT()，返回值带类型标识（字符串有引号）

SELECT profile->'$.name' FROM users;
-- 等价于 JSON_EXTRACT(profile, '$.name')

SELECT profile->'$.skills' FROM users;
-- 返回: ["Java", "MySQL", "Redis"]

->> 操作符：提取值并去除引号（最常用）

SELECT profile->>'$.name' FROM users;
-- 返回: 张三（不带引号，可直接用于字符串比较）

SELECT profile->>'$.skills[0]' FROM users;
-- 返回: Java

使用场景区分：

  • -> 用于需要保留 JSON 格式的场景（如返回数组、对象）
  • ->> 用于获取纯文本值（如字符串比较、显示）

条件查询​

查询 JSON 字段的值

-- 查询年龄大于 25 的用户
SELECT username, profile->>'$.name' AS name
FROM users 
WHERE JSON_EXTRACT(profile, '$.age') > 25;

-- 或使用 -> 操作符
SELECT username, profile->>'$.name' AS name
FROM users 
WHERE profile->'$.age' > 25;

JSON_CONTAINS() 检查是否包含值

-- 检查 skills 数组是否包含 "MySQL"
SELECT username 
FROM users 
WHERE JSON_CONTAINS(profile->'$.skills', '"MySQL"');

-- 检查对象是否包含特定键值对
SELECT username 
FROM users 
WHERE JSON_CONTAINS(profile, '{"city": "北京"}');

JSON_CONTAINS_PATH() 检查路径是否存在

-- 检查单个路径是否存在
SELECT username FROM users 
WHERE JSON_CONTAINS_PATH(profile, 'one', '$.email');
-- 'one' 表示只要有任意一个路径存在就返回 true

-- 检查多个路径是否都存在
SELECT username FROM users 
WHERE JSON_CONTAINS_PATH(profile, 'all', '$.name', '$.age');
-- 'all' 表示所有路径都必须存在

使用 MEMBER OF 检查数组元素（MySQL 8.0+）

-- 更简洁的数组元素检查语法
SELECT username 
FROM users 
WHERE 'MySQL' MEMBER OF(profile->'$.skills');

JSON_SEARCH() 搜索路径​

JSON_SEARCH() 返回包含指定字符串的路径：

-- 查找包含 "Java" 的路径
SET @data = '{"name": "张三", "skills": ["Java", "MySQL", "JavaEE"]}';
SELECT JSON_SEARCH(@data, 'one', 'Java');
-- 结果: "$.skills[0]"（返回第一个匹配的路径）

SELECT JSON_SEARCH(@data, 'all', 'Java');
-- 结果: ["$.skills[0]", "$.skills[2]"]（返回所有匹配的路径）

-- 支持通配符
SELECT JSON_SEARCH(@data, 'all', 'Java%');
-- 匹配 "Java", "JavaEE" 等

修改 JSON 数据​

MySQL 提供三个主要函数来修改 JSON 数据，它们的行为有重要区别：

JSON_SET() - 设置值（推荐）​

存在则更新，不存在则添加。这是最常用的修改函数：

-- 更新现有字段
UPDATE users 
SET profile = JSON_SET(profile, '$.age', 29)
WHERE username = 'zhangsan';

-- 添加新字段
UPDATE users 
SET profile = JSON_SET(profile, '$.email', '[email protected]')
WHERE username = 'zhangsan';

-- 同时更新多个字段
UPDATE users 
SET profile = JSON_SET(
    profile,
    '$.age', 30,
    '$.city', '上海',
    '$.department', '技术部'
)
WHERE username = 'zhangsan';

-- 修改数组元素
UPDATE users 
SET profile = JSON_SET(profile, '$.skills[0]', 'Java 17')
WHERE username = 'zhangsan';

JSON_INSERT() - 仅添加新字段​

只在路径不存在时添加，已存在的值不会被修改：

-- 如果 $.nickname 不存在，则添加；如果存在，则忽略
UPDATE users 
SET profile = JSON_INSERT(profile, '$.nickname', '小张')
WHERE username = 'zhangsan';

-- 这个操作不会修改已存在的 age 字段
UPDATE users 
SET profile = JSON_INSERT(profile, '$.age', 99)
WHERE username = 'zhangsan';
-- age 保持原值不变

JSON_REPLACE() - 仅更新已有字段​

只在路径存在时更新，不存在的路径会被忽略：

-- 只更新已存在的字段，不添加新字段
UPDATE users 
SET profile = JSON_REPLACE(profile, '$.age', 31)
WHERE username = 'zhangsan';

-- 如果 $.nickname 不存在，这个操作不会添加它
UPDATE users 
SET profile = JSON_REPLACE(profile, '$.nickname', '小张')
WHERE username = 'zhangsan';
-- 操作无效，nickname 字段不会被添加

三个修改函数的对比​

函数已存在的路径不存在的路径
JSON_SET更新值添加新值
JSON_INSERT不修改添加新值
JSON_REPLACE更新值不操作

记忆口诀：SET 最全能，INSERT 只加不改，REPLACE 只改不加。

JSON_REMOVE() 删除字段​

删除 JSON 文档中指定路径的元素：

-- 删除单个字段
UPDATE users 
SET profile = JSON_REMOVE(profile, '$.email')
WHERE username = 'zhangsan';

-- 删除多个字段
UPDATE users 
SET profile = JSON_REMOVE(profile, '$.email', '$.department')
WHERE username = 'zhangsan';

-- 删除数组元素
UPDATE users 
SET profile = JSON_REMOVE(profile, '$.skills[1]')
WHERE username = 'zhangsan';
-- 删除后数组索引自动重排

数组操作函数​

JSON_ARRAY_APPEND() 追加元素

-- 在数组末尾追加元素
UPDATE users 
SET profile = JSON_ARRAY_APPEND(profile, '$.skills', 'Docker')
WHERE username = 'zhangsan';

-- 追加到嵌套数组
UPDATE users 
SET profile = JSON_ARRAY_APPEND(profile, '$.projects[0].members', 'user001')
WHERE username = 'zhangsan';

JSON_ARRAY_INSERT() 插入元素

-- 在指定位置插入元素
UPDATE users 
SET profile = JSON_ARRAY_INSERT(profile, '$.skills[0]', 'Python')
WHERE username = 'zhangsan';
-- Python 会插入到索引 0 的位置，其他元素后移

部分更新优化​

MySQL 8.0 支持 JSON 列的部分更新优化。当满足以下条件时，UPDATE 操作可以只修改变化的部分，而不是重写整个 JSON 文档：

优化条件：

  1. 被更新的列声明为 JSON 类型
  2. 使用 JSON_SET()、JSON_REPLACE() 或 JSON_REMOVE() 函数
  3. 新值不比旧值大（或之前的部分更新已释放了足够空间）
-- 这种写法可以利用部分更新优化
UPDATE users 
SET profile = JSON_SET(profile, '$.age', 30)
WHERE id = 1;

-- 这种写法无法利用优化（直接赋值）
UPDATE users 
SET profile = '{"age": 30, "name": "张三"}'
WHERE id = 1;

使用 JSON_STORAGE_FREE() 查看部分更新释放的空间：

SELECT JSON_STORAGE_FREE(profile) FROM users WHERE id = 1;
-- 返回部分更新释放的字节数

JSON 聚合函数​

MySQL 提供两个聚合函数，可以将多行数据聚合成 JSON 格式。

JSON_ARRAYAGG() 聚合为数组​

将一列的值聚合成 JSON 数组：

-- 将所有用户名聚合成数组
SELECT JSON_ARRAYAGG(username) FROM users;
-- 结果: ["zhangsan", "lisi", "wangwu", "zhaoliu"]

-- 分组聚合
SELECT department, JSON_ARRAYAGG(username) AS members
FROM users
GROUP BY department;
-- 结果:
-- | department | members |
-- | 技术部     | ["zhangsan", "lisi"] |
-- | 市场部     | ["wangwu", "zhaoliu"] |

-- 聚合复杂对象
SELECT 
    department,
    JSON_ARRAYAGG(
        JSON_OBJECT('name', username, 'age', profile->'$.age')
    ) AS members
FROM users
GROUP BY department;

JSON_OBJECTAGG() 聚合为对象​

将两列值聚合成键值对对象：

-- 将 username 作为键，profile 作为值
SELECT JSON_OBJECTAGG(username, profile) FROM users;
-- 结果: {"zhangsan": {...}, "lisi": {...}, ...}

-- 创建配置映射
SELECT JSON_OBJECTAGG(config_key, config_value) 
FROM system_config;
-- 结果: {"max_size": "100", "timeout": "30", "theme": "dark"}

实战：构建嵌套 JSON 结构​

-- 构建部门-员工的嵌套结构
SELECT 
    JSON_OBJECT(
        'department', d.name,
        'employees', (
            SELECT JSON_ARRAYAGG(
                JSON_OBJECT(
                    'name', u.username,
                    'age', u.profile->'$.age'
                )
            )
            FROM users u
            WHERE u.department_id = d.id
        )
    ) AS department_data
FROM departments d;

JSON_TABLE() 函数（MySQL 8.0+）​

JSON_TABLE() 是一个非常强大的函数，可以将 JSON 数据转换为关系表格式，然后使用标准 SQL 进行查询。

基本语法​

JSON_TABLE(json_doc, path COLUMNS(columns_definition))

将 JSON 数组转为表​

-- 将 JSON 数组展开为行
SELECT *
FROM JSON_TABLE(
    '["张三", "李四", "王五"]',
    '$[*]' COLUMNS (
        name VARCHAR(50) PATH '$'
    )
) AS jt;
-- 结果:
-- | name  |
-- | 张三  |
-- | 李四  |
-- | 王五  |

将 JSON 对象数组转为表​

SELECT *
FROM JSON_TABLE(
    '[{"name": "张三", "age": 28}, {"name": "李四", "age": 30}]',
    '$[*]' COLUMNS (
        name VARCHAR(50) PATH '$.name',
        age INT PATH '$.age'
    )
) AS jt;
-- 结果:
-- | name  | age |
-- | 张三  | 28  |
-- | 李四  | 30  |

嵌套列定义（NESTED）​

处理嵌套的 JSON 结构：

SELECT *
FROM JSON_TABLE(
    '{
        "department": "技术部",
        "employees": [
            {"name": "张三", "skills": ["Java", "MySQL"]},
            {"name": "李四", "skills": ["Python", "Redis"]}
        ]
    }',
    '$' COLUMNS (
        department VARCHAR(50) PATH '$.department',
        NESTED PATH '$.employees[*]' COLUMNS (
            name VARCHAR(50) PATH '$.name',
            NESTED PATH '$.skills[*]' COLUMNS (
                skill VARCHAR(50) PATH '$'
            )
        )
    )
) AS jt;
-- 结果:
-- | department | name  | skill  |
-- | 技术部     | 张三  | Java   |
-- | 技术部     | 张三  | MySQL  |
-- | 技术部     | 李四  | Python |
-- | 技术部     | 李四  | Redis  |

与实际表关联​

-- 将存储的 JSON 数据展开并与部门表关联
SELECT 
    u.username,
    jt.skill
FROM users u
JOIN JSON_TABLE(
    u.profile,
    '$.skills[*]' COLUMNS (
        skill VARCHAR(50) PATH '$'
    )
) AS jt;
-- 结果:
-- | username  | skill  |
-- | zhangsan  | Java   |
-- | zhangsan  | MySQL  |
-- | zhangsan  | Redis  |

JSON 索引策略​

JSON 列本身不能直接建立索引，但 MySQL 提供了多种方式来优化 JSON 字段的查询性能。

方法一：生成列索引​

这是最常用的方法，通过在 JSON 字段上创建生成列，然后对生成列建索引：

-- 添加存储的生成列
ALTER TABLE users 
ADD COLUMN age_extracted INT 
    GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(profile, '$.age'))) STORED;

-- 在生成列上创建索引
CREATE INDEX idx_age ON users(age_extracted);

-- 查询时会自动使用索引
SELECT * FROM users WHERE age_extracted > 25;

STORED vs VIRTUAL 生成列：

  • STORED：值被实际存储，占用存储空间，可以被索引
  • VIRTUAL：值在查询时计算，不占用存储空间，MySQL 8.0 后也可被索引
-- 使用虚拟生成列（MySQL 8.0+）
ALTER TABLE users 
ADD COLUMN city_extracted VARCHAR(50) 
    GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(profile, '$.city'))) VIRTUAL;

CREATE INDEX idx_city ON users(city_extracted);

方法二：多值索引（MySQL 8.0.17+）​

对于 JSON 数组，可以创建多值索引，使查询数组元素更加高效：

-- 创建多值索引
CREATE INDEX idx_skills ON users(
    (CAST(profile->'$.skills' AS CHAR(255) ARRAY))
);

-- 使用 MEMBER OF 或 JSON_CONTAINS 查询时会使用索引
SELECT * FROM users WHERE 'MySQL' MEMBER OF(profile->'$.skills');
SELECT * FROM users WHERE JSON_CONTAINS(profile->'$.skills', '"MySQL"');

方法三：函数索引（MySQL 8.0+）​

直接在 JSON 函数表达式上创建索引：

-- 创建函数索引
CREATE INDEX idx_profile_city ON users(
    (JSON_UNQUOTE(JSON_EXTRACT(profile, '$.city')))
);

-- 查询时会使用索引
SELECT * FROM users 
WHERE JSON_UNQUOTE(JSON_EXTRACT(profile, '$.city')) = '北京';

JSON 工具函数​

类型判断和验证​

JSON_TYPE() 返回 JSON 值的类型

SELECT JSON_TYPE('{"name": "张三"}');  -- OBJECT
SELECT JSON_TYPE('[1, 2, 3]');         -- ARRAY
SELECT JSON_TYPE('"hello"');           -- STRING
SELECT JSON_TYPE('123');               -- INTEGER
SELECT JSON_TYPE('true');              -- BOOLEAN
SELECT JSON_TYPE('null');              -- NULL

JSON_VALID() 验证 JSON 格式

SELECT JSON_VALID('{"name": "张三"}');  -- 1（有效）
SELECT JSON_VALID('not json');          -- 0（无效）

-- 在插入前验证
INSERT INTO logs (data) 
SELECT @input_data WHERE JSON_VALID(@input_data);

格式化和美化​

JSON_PRETTY() 格式化输出

SELECT JSON_PRETTY('{"name":"张三","age":28,"skills":["Java","MySQL"]}');
-- 结果:
-- {
--   "name": "张三",
--   "age": 28,
--   "skills": [
--     "Java",
--     "MySQL"
--   ]
-- }

深度和长度​

JSON_DEPTH() 返回 JSON 文档的最大深度

SELECT JSON_DEPTH('{}');                          -- 1
SELECT JSON_DEPTH('[]');                          -- 1
SELECT JSON_DEPTH('[1, 2, 3]');                   -- 2
SELECT JSON_DEPTH('{"a": {"b": {"c": 1}}}');      -- 4

JSON_LENGTH() 返回 JSON 文档的长度

SELECT JSON_LENGTH('{"a": 1, "b": 2, "c": 3}');   -- 3（键的数量）
SELECT JSON_LENGTH('[1, 2, 3, 4, 5]');            -- 5（元素数量）
SELECT JSON_LENGTH('"hello"');                    -- 1（标量值为 1）
SELECT JSON_LENGTH('{"a": [1, 2, 3]}', '$.a');    -- 3（指定路径的长度）

JSON_KEYS() 返回对象的所有键

SELECT JSON_KEYS('{"name": "张三", "age": 28, "city": "北京"}');
-- 结果: ["name", "age", "city"]

-- 返回嵌套对象的键
SELECT JSON_KEYS('{"person": {"name": "张三", "age": 28}}', '$.person');
-- 结果: ["name", "age"]

JSON 与其他类型转换​

CAST 转换​

-- JSON 转字符串
SELECT CAST(JSON_EXTRACT('{"name": "张三"}', '$.name') AS CHAR);
-- 结果: 张三（不带引号）

-- 字符串转 JSON
SELECT CAST('{"name": "张三"}' AS JSON);
-- 结果: JSON 类型的对象

-- 数字转 JSON
SELECT CAST(123 AS JSON);     -- 123（JSON 数字）
SELECT CAST(123.45 AS JSON);  -- 123.45（JSON 浮点数）

-- 布尔值转换
SELECT CAST(TRUE AS JSON);    -- true
SELECT CAST(FALSE AS JSON);   -- false

JSON_UNQUOTE() 去除引号​

-- JSON 字符串去除引号
SELECT JSON_UNQUOTE('"hello"');           -- hello
SELECT JSON_UNQUOTE(JSON_EXTRACT('{"name": "张三"}', '$.name'));
-- 结果: 张三

-- 等价于 ->> 操作符
SELECT profile->>'$.name';  -- 自动调用 JSON_UNQUOTE

实战案例​

案例 1：电商商品属性管理​

不同品类的商品具有完全不同的属性，使用 JSON 存储可以灵活应对：

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    category_id INT NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    attributes JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 电视机
INSERT INTO products (name, category_id, price, attributes) VALUES
('智能电视 A1', 1, 2999.00, JSON_OBJECT(
    'screen_size', '55寸',
    'resolution', '4K',
    'ports', JSON_ARRAY('HDMI', 'USB', '网线'),
    'smart_tv', TRUE,
    'brand', 'BrandA'
));

-- 手机
INSERT INTO products (name, category_id, price, attributes) VALUES
('智能手机 X1', 2, 3999.00, JSON_OBJECT(
    'screen_size', '6.5寸',
    'storage', '256GB',
    'ram', '8GB',
    'camera', JSON_OBJECT('main', '64MP', 'front', '32MP'),
    'battery', '4500mAh',
    'brand', 'BrandB'
));

-- 服装
INSERT INTO products (name, category_id, price, attributes) VALUES
('休闲T恤', 3, 99.00, JSON_OBJECT(
    'size', JSON_ARRAY('S', 'M', 'L', 'XL'),
    'colors', JSON_ARRAY('白色', '黑色', '蓝色'),
    'material', '棉',
    'brand', 'BrandC'
));

-- 查询所有 4K 分辨率的电视
SELECT name, price FROM products
WHERE category_id = 1
AND attributes->>'$.resolution' = '4K';

-- 查询所有支持 5G 的产品（如果属性存在）
SELECT name, price, attributes->>'$.network' AS network
FROM products
WHERE JSON_CONTAINS_PATH(attributes, 'one', '$.network')
AND JSON_CONTAINS(attributes->'$.network', '"5G"');

-- 更新商品的促销信息
UPDATE products
SET attributes = JSON_SET(
    attributes,
    '$.promotion', JSON_OBJECT(
        'discount', 0.8,
        'start_date', '2024-01-01',
        'end_date', '2024-01-31'
    )
)
WHERE category_id = 2;

案例 2：用户配置管理​

用户的偏好设置千差万别，JSON 类型非常适合这种场景：

CREATE TABLE user_settings (
    user_id INT PRIMARY KEY,
    preferences JSON,
    notification_config JSON,
    privacy_settings JSON,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- 初始化用户配置
INSERT INTO user_settings (user_id, preferences, notification_config, privacy_settings)
VALUES (
    1,
    JSON_OBJECT(
        'theme', 'dark',
        'language', 'zh-CN',
        'timezone', 'Asia/Shanghai',
        'date_format', 'YYYY-MM-DD',
        'items_per_page', 20
    ),
    JSON_OBJECT(
        'email', JSON_OBJECT('enabled', TRUE, 'frequency', 'daily'),
        'push', JSON_OBJECT('enabled', TRUE, 'sound', TRUE),
        'sms', JSON_OBJECT('enabled', FALSE)
    ),
    JSON_OBJECT(
        'profile_visible', 'friends',
        'activity_visible', TRUE,
        'searchable', TRUE
    )
);

-- 更新主题
UPDATE user_settings
SET preferences = JSON_SET(preferences, '$.theme', 'light')
WHERE user_id = 1;

-- 批量更新通知配置
UPDATE user_settings
SET notification_config = JSON_MERGE_PATCH(notification_config, JSON_OBJECT(
    'email', JSON_OBJECT('enabled', FALSE),
    'push', JSON_OBJECT('sound', FALSE)
))
WHERE user_id = 1;

-- 查询所有使用深色主题的用户
SELECT user_id FROM user_settings
WHERE preferences->>'$.theme' = 'dark';

-- 统计各种主题的使用人数
SELECT 
    preferences->>'$.theme' AS theme,
    COUNT(*) AS user_count
FROM user_settings
GROUP BY preferences->>'$.theme';

案例 3：日志数据分析​

日志数据结构多变，使用 JSON 存储，然后用 JSON_TABLE 分析：

CREATE TABLE api_logs (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    endpoint VARCHAR(255) NOT NULL,
    method VARCHAR(10) NOT NULL,
    request_data JSON,
    response_data JSON,
    status_code INT,
    duration_ms INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 插入日志
INSERT INTO api_logs (endpoint, method, request_data, response_data, status_code, duration_ms)
VALUES (
    '/api/users',
    'POST',
    JSON_OBJECT(
        'body', JSON_OBJECT('name', '张三', 'email', '[email protected]'),
        'headers', JSON_OBJECT('Content-Type', 'application/json'),
        'ip', '192.168.1.100'
    ),
    JSON_OBJECT(
        'status', 'success',
        'data', JSON_OBJECT('user_id', 1001)
    ),
    201,
    45
);

-- 分析请求参数
SELECT 
    al.id,
    al.endpoint,
    al.request_data->>'$.body.name' AS request_name,
    al.response_data->>'$.data.user_id' AS created_user_id,
    al.duration_ms
FROM api_logs al
WHERE al.method = 'POST'
AND al.status_code = 201;

-- 使用 JSON_TABLE 展开嵌套的响应数据
SELECT 
    al.id,
    al.endpoint,
    jt.field_name,
    jt.field_value
FROM api_logs al,
JSON_TABLE(
    al.response_data,
    '$.data' COLUMNS (
        field_name VARCHAR(50) PATH '$.' IN ('user_id', 'name', 'email'),
        field_value VARCHAR(255) PATH CONCAT('$.', field_name)
    )
) AS jt
WHERE al.status_code = 200;

-- 创建索引优化查询
ALTER TABLE api_logs 
ADD COLUMN endpoint_path VARCHAR(255) 
    GENERATED ALWAYS AS (endpoint) STORED;
CREATE INDEX idx_endpoint ON api_logs(endpoint_path);

案例 4：内容管理系统​

文章的元数据、标签、自定义字段等都适合使用 JSON：

CREATE TABLE articles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    content TEXT,
    metadata JSON,
    tags JSON,
    custom_fields JSON,
    published_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 插入文章
INSERT INTO articles (title, content, metadata, tags, custom_fields)
VALUES (
    'MySQL JSON 类型详解',
    '文章内容...',
    JSON_OBJECT(
        'author', JSON_OBJECT('id', 1, 'name', '张三'),
        'category', '数据库',
        'reading_time', 10,
        'difficulty', '中级'
    ),
    JSON_ARRAY('MySQL', 'JSON', '数据库', '教程'),
    JSON_OBJECT(
        'seo_title', 'MySQL JSON 完整教程',
        'seo_description', '深入理解 MySQL JSON 类型',
        'featured_image', '/images/mysql-json.png'
    )
);

-- 通过标签搜索文章
SELECT title, tags FROM articles
WHERE JSON_CONTAINS(tags, '"MySQL"');

-- 按阅读时间分类统计
SELECT 
    metadata->>'$.difficulty' AS difficulty,
    COUNT(*) AS article_count,
    AVG(CAST(metadata->>'$.reading_time' AS UNSIGNED)) AS avg_reading_time
FROM articles
GROUP BY metadata->>'$.difficulty';

-- 添加标签
UPDATE articles
SET tags = JSON_ARRAY_APPEND(tags, '$', '入门')
WHERE id = 1;

-- 使用多值索引优化标签查询（MySQL 8.0.17+）
CREATE INDEX idx_tags ON articles(
    (CAST(tags AS CHAR(50) ARRAY))
);

SELECT title FROM articles WHERE 'MySQL' MEMBER OF(tags);

性能优化建议​

1. 合理使用 JSON 类型​

适合使用 JSON 的场景：

  • 数据结构多变或不固定
  • 不需要频繁查询内部字段
  • 仅需存储和展示，很少在 SQL 中处理
  • 原型开发阶段，数据模型尚未稳定

不适合使用 JSON 的场景：

  • 字段固定且需要频繁查询
  • 需要在多个字段上建立索引
  • 数据量大且查询复杂
  • 需要严格的数据类型约束

2. 索引策略​

-- 对于经常查询的 JSON 字段，务必创建生成列索引
ALTER TABLE users 
ADD COLUMN city_virtual VARCHAR(50) 
    GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(profile, '$.city'))) VIRTUAL;
CREATE INDEX idx_city ON users(city_virtual);

-- 对于数组元素查询，使用多值索引（MySQL 8.0.17+）
CREATE INDEX idx_skills ON users((CAST(profile->'$.skills' AS CHAR(50) ARRAY)));

3. 查询优化​

-- 不好：每次查询都解析 JSON
SELECT * FROM users WHERE JSON_EXTRACT(profile, '$.age') > 25;

-- 好：使用生成列索引
ALTER TABLE users ADD COLUMN age_int INT GENERATED ALWAYS AS (profile->'$.age') STORED;
CREATE INDEX idx_age ON users(age_int);
SELECT * FROM users WHERE age_int > 25;

-- 不好：查询时格式化输出
SELECT JSON_PRETTY(profile) FROM users;

-- 好：在应用层格式化，数据库只存储和查询
SELECT profile FROM users;

4. 部分更新优化​

-- 好：使用 JSON 函数更新，可触发部分更新优化
UPDATE users SET profile = JSON_SET(profile, '$.age', 30) WHERE id = 1;

-- 不好：整体替换，无法优化
UPDATE users SET profile = '{"age": 30, "name": "张三"}' WHERE id = 1;

5. 避免过度嵌套​

-- 不好：嵌套层级太深
{
    "level1": {
        "level2": {
            "level3": {
                "level4": {
                    "value": "data"
                }
            }
        }
    }
}

-- 好：保持扁平结构或适度嵌套
{
    "category": "tech",
    "subcategory": "database",
    "value": "data"
}

JSON 函数速查表​

创建函数​

函数说明示例
JSON_ARRAY()创建 JSON 数组JSON_ARRAY(1, 2, 'a')
JSON_OBJECT()创建 JSON 对象JSON_OBJECT('key', 'value')
JSON_QUOTE()将字符串转为 JSON 字符串JSON_QUOTE('hello') → "hello"

查询函数​

函数说明示例
JSON_EXTRACT()提取 JSON 值JSON_EXTRACT(data, '$.name')
->提取 JSON 值（别名）data->'$.name'
->>提取并去除引号data->>'$.name'
JSON_CONTAINS()检查是否包含值JSON_CONTAINS(data, '"value"', '$.key')
JSON_CONTAINS_PATH()检查路径是否存在JSON_CONTAINS_PATH(data, 'one', '$.name')
JSON_KEYS()返回对象的所有键JSON_KEYS(data)
JSON_SEARCH()搜索值的路径JSON_SEARCH(data, 'one', 'value')
MEMBER OF检查数组是否包含元素（8.0+）'value' MEMBER OF(data->'$.arr')

修改函数​

函数说明存在的路径不存在的路径
JSON_SET()设置值更新添加
JSON_INSERT()插入值不变添加
JSON_REPLACE()替换值更新不变
JSON_REMOVE()删除值删除无操作
JSON_ARRAY_APPEND()数组末尾追加--
JSON_ARRAY_INSERT()数组指定位置插入--
JSON_MERGE_PRESERVE()合并（保留重复）--
JSON_MERGE_PATCH()合并（覆盖重复）--

属性函数​

函数说明
JSON_TYPE()返回 JSON 值类型
JSON_VALID()验证 JSON 格式
JSON_DEPTH()返回最大深度
JSON_LENGTH()返回长度
JSON_STORAGE_SIZE()返回存储大小
JSON_STORAGE_FREE()返回部分更新释放的空间

聚合函数​

函数说明
JSON_ARRAYAGG()聚合为 JSON 数组
JSON_OBJECTAGG()聚合为 JSON 对象

表函数​

函数说明
JSON_TABLE()将 JSON 转为关系表

小结​

MySQL JSON 类型是一个强大的特性，它让关系型数据库获得了处理半结构化数据的能力。关键要点：

  1. 存储原理：JSON 被转换为优化的二进制格式，支持快速随机访问
  2. 路径表达式：掌握 $、$.key、$[index] 等路径语法是高效使用 JSON 函数的基础
  3. 修改函数：理解 JSON_SET、JSON_INSERT、JSON_REPLACE 的区别
  4. 索引策略：通过生成列、多值索引、函数索引优化 JSON 字段查询
  5. 性能优化：合理使用 JSON 类型，避免过度嵌套，利用部分更新优化

练习​

  1. 创建一个产品表，使用 JSON 存储产品规格，包含嵌套的颜色尺寸信息
  2. 实现一个配置表，支持动态添加配置项，并能快速按配置项名查询
  3. 使用 JSON_TABLE 将日志 JSON 数据展开为报表
  4. 创建多值索引优化数组元素的查询

参考资料​