JSON 文档存储

MySQL JSON 文档存储

MySQL 5.7.8 引入原生 JSON 数据类型，8.0 大幅增强 JSON 函数，使 MySQL 在处理半结构化数据时具备了媲美 MongoDB 的灵活性，同时保留了关系型数据库的 ACID 事务和 SQL 查询能力。

JSON 列基础

-- 创建包含 JSON 列的表
CREATE TABLE user_profiles (
    id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id     BIGINT UNSIGNED NOT NULL,
    preferences JSON            COMMENT '用户偏好设置',
    social      JSON            COMMENT '社交账号信息',
    metadata    JSON            COMMENT '扩展元数据',
    created_at  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uk_user (user_id)
) ENGINE=InnoDB;

-- 插入 JSON 数据（直接写 JSON 字符串）
INSERT INTO user_profiles (user_id, preferences, social)
VALUES (
    12345,
    '{"theme": "dark", "language": "zh-CN", "notifications": {"email": true, "sms": false}}',
    '{"github": "alice", "twitter": "@alice_dev", "linkedin": "alice-smith"}'
);

-- MySQL 会自动校验 JSON 格式，非法 JSON 会报错
INSERT INTO user_profiles (user_id, preferences)
VALUES (99999, 'not a json');
-- ERROR 3140: Invalid JSON text

JSON 列与 TEXT 列的区别

路径表达式

-- 路径表达式语法
-- $ 表示根节点
-- $.key 访问对象属性
-- $[n] 访问数组第 n 个元素（0 起）
-- $.key[*] 访问数组所有元素
-- $**.key 递归搜索

-- 两种等价的路径查询写法
SELECT
    preferences -> '$.theme'           AS theme,     -- 返回带引号的 JSON 字符串
    preferences ->> '$.theme'          AS theme_str, -- 返回去引号的普通字符串（常用）
    JSON_EXTRACT(preferences, '$.theme')              AS theme_json  -- 等价于 ->
FROM user_profiles WHERE user_id = 12345;

-- 访问嵌套路径
SELECT
    preferences ->> '$.notifications.email' AS email_notify,
    social ->> '$.github'                   AS github_handle
FROM user_profiles WHERE user_id = 12345;

-- 数组路径
SELECT
    attrs -> '$[0]'            AS first_element,
    attrs -> '$[last]'         AS last_element,
    attrs -> '$[0 to 2]'       AS first_three
FROM products;

JSON 函数大全

查询函数

-- JSON_EXTRACT / -> : 提取值
SELECT JSON_EXTRACT('{"a":1,"b":[1,2,3]}', '$.b[1]');  -- 2

-- JSON_CONTAINS: 检查是否包含值
SELECT * FROM products WHERE JSON_CONTAINS(tags, '"electronics"');
SELECT * FROM products WHERE JSON_CONTAINS(attrs, '{"color":"red"}');

-- JSON_CONTAINS_PATH: 检查路径是否存在
SELECT JSON_CONTAINS_PATH('{"a":{"b":1}}', 'one', '$.a.b');  -- 1
SELECT JSON_CONTAINS_PATH('{"a":1}', 'one', '$.a', '$.b');   -- 1 ('one': 任意一个存在)
SELECT JSON_CONTAINS_PATH('{"a":1}', 'all', '$.a', '$.b');   -- 0 ('all': 所有路径都存在)

-- JSON_SEARCH: 查找值对应的路径
SELECT JSON_SEARCH(tags, 'one', 'electronics');  -- "$.color" 类型的路径

-- JSON_OVERLAPS (8.0.17+): 两个 JSON 是否有重叠
SELECT * FROM products
WHERE JSON_OVERLAPS(tags, '["electronics","phone"]');
-- 等价于: tags 中包含 electronics 或 phone 任意一个

-- JSON_VALUE (8.0.21+): 提取并转换类型
SELECT JSON_VALUE(preferences, '$.age' RETURNING UNSIGNED) AS age
FROM user_profiles WHERE user_id = 1;

创建/构建函数

-- JSON_OBJECT: 构建 JSON 对象
SELECT JSON_OBJECT('id', id, 'name', name, 'price', price) FROM products LIMIT 3;

-- JSON_ARRAY: 构建 JSON 数组
SELECT JSON_ARRAY(1, 'hello', true, NULL);  -- [1, "hello", true, null]

-- JSON_ARRAYAGG: 聚合为 JSON 数组
SELECT category, JSON_ARRAYAGG(name) AS product_names
FROM products GROUP BY category;

-- JSON_OBJECTAGG: 聚合为 JSON 对象 (key-value)
SELECT JSON_OBJECTAGG(config_key, config_val)
FROM game_configs
WHERE config_key LIKE 'ui_%';

-- JSON_MERGE_PATCH (8.0+): RFC 7396 标准合并（后者覆盖前者）
SELECT JSON_MERGE_PATCH('{"a":1,"b":2}', '{"b":3,"c":4}');
-- {"a": 1, "b": 3, "c": 4}

-- JSON_MERGE_PRESERVE: 合并并保留所有值（数组化）
SELECT JSON_MERGE_PRESERVE('{"a":1}', '{"a":2}');
-- {"a": [1, 2]}

类型和格式函数

-- JSON_TYPE: 返回 JSON 值的类型
SELECT JSON_TYPE('{"a":1}');    -- OBJECT
SELECT JSON_TYPE('[1,2,3]');    -- ARRAY
SELECT JSON_TYPE('"hello"');    -- STRING
SELECT JSON_TYPE('42');         -- INTEGER
SELECT JSON_TYPE('3.14');       -- DOUBLE
SELECT JSON_TYPE('true');       -- BOOLEAN
SELECT JSON_TYPE('null');       -- NULL

-- JSON_VALID: 检查是否合法 JSON
SELECT JSON_VALID('{"a":1}');   -- 1
SELECT JSON_VALID('not json');  -- 0

-- JSON_PRETTY: 格式化输出（调试用）
SELECT JSON_PRETTY(preferences) FROM user_profiles WHERE user_id = 1\G

-- JSON_DEPTH: 嵌套深度
SELECT JSON_DEPTH('{"a":{"b":{"c":1}}}');  -- 3

-- JSON_LENGTH: 数组长度 / 对象属性数
SELECT JSON_LENGTH('[1,2,3]');          -- 3
SELECT JSON_LENGTH('{"a":1,"b":2}');    -- 2

修改 JSON 数据

-- JSON_SET: 设置值（存在则覆盖，不存在则添加）
UPDATE user_profiles
SET preferences = JSON_SET(
    preferences,
    '$.theme',     'light',   -- 修改主题
    '$.fontSize',  16         -- 新增字体大小
)
WHERE user_id = 12345;

-- JSON_INSERT: 插入（只在路径不存在时添加，存在则忽略）
UPDATE user_profiles
SET preferences = JSON_INSERT(preferences, '$.newFeature', true)
WHERE user_id = 12345;

-- JSON_REPLACE: 替换（只替换已存在的路径）
UPDATE user_profiles
SET preferences = JSON_REPLACE(preferences, '$.theme', 'auto')
WHERE user_id = 12345;

-- JSON_REMOVE: 删除路径
UPDATE user_profiles
SET social = JSON_REMOVE(social, '$.twitter')  -- 删除 twitter 字段
WHERE user_id = 12345;

-- JSON_ARRAY_APPEND: 向数组追加元素
UPDATE products
SET tags = JSON_ARRAY_APPEND(tags, '$', 'new-tag')
WHERE id = 1;

-- JSON_ARRAY_INSERT: 在数组指定位置插入
UPDATE products
SET tags = JSON_ARRAY_INSERT(tags, '$[0]', 'priority-tag')
WHERE id = 1;

JSON 索引

JSON 列本身不能直接建索引，但可以通过两种方式实现索引加速：

方法 1：虚拟列（Generated Column）+ 索引

-- 添加虚拟列（不占用存储）
ALTER TABLE user_profiles
ADD COLUMN theme VARCHAR(20)
    GENERATED ALWAYS AS (preferences ->> '$.theme') VIRTUAL;

-- 对虚拟列建索引
ALTER TABLE user_profiles ADD INDEX idx_theme (theme);

-- 查询时优化器自动使用索引
SELECT * FROM user_profiles WHERE preferences ->> '$.theme' = 'dark';
-- type=ref, key=idx_theme  ← 使用了索引！

方法 2：函数索引（MySQL 8.0.13+）

-- 直接对 JSON 路径表达式建函数索引（不需要显式添加虚拟列）
ALTER TABLE user_profiles
ADD INDEX idx_language ((preferences ->> '$.language'));

ALTER TABLE products
ADD INDEX idx_price ((CAST(attrs->>'$.price' AS DECIMAL(10,2))));

多值索引（Multi-Valued Index，MySQL 8.0.17+）

-- 对 JSON 数组建多值索引
CREATE TABLE products (
    id   INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(200),
    tags JSON
);

-- 多值索引：对 JSON 数组中的每个元素都建立索引项
ALTER TABLE products
ADD INDEX idx_tags ((CAST(tags AS CHAR(50) ARRAY)));

-- 使用 MEMBER OF 操作符利用多值索引
SELECT * FROM products WHERE 'electronics' MEMBER OF (tags);
-- type=ref, key=idx_tags  ← 高效！

-- JSON_CONTAINS 也可以使用多值索引
SELECT * FROM products WHERE JSON_CONTAINS(tags, '"phone"');

JSON_TABLE：将 JSON 展开为表

-- JSON_TABLE 将 JSON 数组展开为行
SELECT jt.*
FROM products p,
JSON_TABLE(
    p.tags,
    '$[*]' COLUMNS (
        tag VARCHAR(50) PATH '$'
    )
) AS jt
WHERE p.id = 1;
-- 如果 tags = '["electronics","phone","5G"]'
-- 返回 3 行: electronics, phone, 5G

-- 复杂对象数组展开
SELECT p.id, p.name, oi.*
FROM orders o,
JSON_TABLE(
    o.items,
    '$[*]' COLUMNS (
        item_id   INT     PATH '$.id',
        quantity  INT     PATH '$.qty',
        price     DECIMAL(10,2) PATH '$.price',
        subtotal  DECIMAL(10,2) PATH '$.price' -- 可以计算
    )
) AS oi
JOIN products p ON p.id = oi.item_id
WHERE o.id = 1001;

Schema 设计原则

性能注意事项

JSON 列的常见性能陷阱：

1. 在 WHERE 中直接使用 JSON 路径而没有虚拟列索引 → 全表扫描
2. JSON 列过大（超过 1MB）→ 影响 Buffer Pool 效率
3. 频繁 UPDATE JSON 列的某个字段 → MySQL 8.0+ 支持部分更新优化，但仍比普通列慢
4. 对整个 JSON 列做 JSON_CONTAINS 全表扫描 → 考虑多值索引

-- 检查 JSON 部分更新是否生效（MySQL 8.0 优化）
SET SESSION optimizer_trace = 'enabled=on';
UPDATE t SET j = JSON_SET(j, '$.a', 1) WHERE id = 1;
SELECT * FROM information_schema.optimizer_trace\G
-- 查看 partial_update 字段是否为 true

-- 查看 JSON 列实际存储大小
SELECT id,
       LENGTH(preferences)        AS pref_bytes,
       JSON_STORAGE_SIZE(preferences) AS pref_storage  -- 内部二进制大小
FROM user_profiles
ORDER BY pref_storage DESC LIMIT 10;

MySQL JSON vs MongoDB：MySQL JSON 适合"主要是关系型数据，部分字段需要灵活扩展"的场景。如果你的数据是以文档为核心（嵌套深、字段变化大、需要文档级原子操作），MongoDB 仍然是更好的选择。不要为了用 JSON 而把所有数据都存成 JSON，这会完全放弃 MySQL 的关系型优势。