JSON 类型与函数

约 822 字大约 3 分钟

布欧-Lewyon

2026-05-15

MySQL 5.7 起支持原生 JSON 数据类型，8.0 进一步增强了 JSON 函数和索引能力。JSON 类型在保持关系型数据库优势的同时，提供了一定的文档灵活性。

JSON 数据类型基础

CREATE TABLE product (
    id       INT AUTO_INCREMENT PRIMARY KEY,
    name     VARCHAR(100),
    attrs    JSON    -- 属性字段，存 JSON
);

INSERT INTO product (name, attrs) VALUES
    ('iPhone 16', '{"color": "black", "storage": 256, "features": ["5G", "FaceID"]}'),
    ('MacBook Pro', '{"color": "silver", "storage": 512, "features": ["M4", "Retina"]}');

JSON 数据访问

-> 和 ->> 操作符

-- -> 返回带引号的 JSON 值
SELECT name, attrs->'$.color' AS color FROM product;
-- iPhone 16 | "black"

-- ->> 返回去引号的字符串
SELECT name, attrs->>'$.color' AS color FROM product;
-- iPhone 16 | black

JSON_EXTRACT

SELECT name, JSON_EXTRACT(attrs, '$.storage') AS storage_gb FROM product;
-- 等效于 attrs->'$.storage'

JSON 路径语法

-- 对象属性
attrs->'$.color'

-- 数组元素（从 0 开始）
attrs->'$.features[0]'

-- 通配符
attrs->'$.*'            -- 所有属性值
attrs->'$.features[*]'  -- 数组所有元素

-- 嵌套路径
attrs->'$.specs.weight'

JSON_TABLE（8.0+）

将 JSON 展开为关系表形式：

SELECT p.name, jt.*
FROM product p,
JSON_TABLE(
    p.attrs,
    '$' COLUMNS (
        color    VARCHAR(20) PATH '$.color',
        storage  INT         PATH '$.storage',
        feature  VARCHAR(20) PATH '$.features[0]'
    )
) AS jt;
-- name        | color  | storage | feature
-- iPhone 16   | black  | 256     | 5G
-- MacBook Pro | silver | 512     | M4

JSON 函数

创建与修改

-- JSON_OBJECT：从键值对创建 JSON
SELECT JSON_OBJECT('name', '张三', 'age', 28);
-- {"age": 28, "name": "张三"}

-- JSON_ARRAY：创建数组
SELECT JSON_ARRAY(1, 'a', TRUE);
-- [1, "a", true]

-- JSON_MERGE_PATCH：合并/覆盖
UPDATE product
SET attrs = JSON_MERGE_PATCH(attrs, '{"color": "white", "price": 9999}')
WHERE id = 1;
-- 保留原有字段，新增/覆盖指定字段

-- JSON_SET：设置值（不存在的路径创建）
UPDATE product
SET attrs = JSON_SET(attrs, '$.warranty', '1 year')
WHERE id = 1;

-- JSON_REMOVE：删除键
UPDATE product
SET attrs = JSON_REMOVE(attrs, '$.warranty')
WHERE id = 1;

搜索与判断

-- JSON_CONTAINS：是否包含指定值
SELECT * FROM product
WHERE JSON_CONTAINS(attrs, '"black"', '$.color');

-- JSON_CONTAINS_PATH：是否存在指定路径
SELECT * FROM product
WHERE JSON_CONTAINS_PATH(attrs, 'one', '$.price', '$.discount');
-- 'one'：任一存在即可；'all'：所有存在

-- JSON_KEYS：获取所有键
SELECT JSON_KEYS(attrs) FROM product;
-- ["color", "storage", "features"]

JSON 多值索引（MySQL 8.0.17+）

对 JSON 数组中的值建立索引，加速 JSON_CONTAINS 等查询：

-- 假设 product.attrs 中有 features 数组
-- 创建多值索引（针对数组值）
CREATE INDEX idx_features ON product(
    (CAST(attrs->'$.features' AS CHAR(100) ARRAY))
);

-- 现在以下查询可以使用索引
SELECT * FROM product
WHERE 'FaceID' MEMBER OF (attrs->'$.features');

JSON vs 关系表 vs MongoDB

场景	推荐	原因
属性不确定或频繁变化	JSON 列	无需 `ALTER TABLE`
属性需要强类型约束和完整性	关系表	约束 + 外键
复杂嵌套文档、无模式	MongoDB	原生文档数据库
少量灵活字段 + 大量结构化	MySQL JSON	两全其美

-- 实用原则：结构化字段用普通列，变动频繁或不确定的属性用 JSON
CREATE TABLE order_ (
    id           INT AUTO_INCREMENT PRIMARY KEY,
    user_id      INT NOT NULL,
    total_amount DECIMAL(10,2) NOT NULL,    -- 结构化的
    created_at   DATETIME NOT NULL,
    -- 不确定的属性：快递偏好、备注、自定义字段等
    extra_info   JSON
);

小结

JSON 类型支持原生 JSON 文档存储，-> / ->> 操作符访问属性。
JSON_TABLE 将 JSON 展开为关系表，方便 JOIN 和聚合。
JSON_MERGE_PATCH、JSON_SET、JSON_REMOVE 用于修改 JSON 值。
多值索引（8.0.17+）加速 JSON 数组的搜索。
JSON 列适合存储不确定或频繁变化的属性，结构化字段应当用普通列。