如何将含未知附加属性的JSON写入MySQL并支持后续访问？

针对你遇到的含动态options数组的JSON插入需求，以下两种方案可满足后续搜索、索引和输出操作的最优需求：

方案一：关系型结构化存储（推荐用于高频查询场景） #

将固定字段与动态属性拆分到两张关联表中，完全符合关系型数据库设计规范，便于高效索引和复杂查询。

1. 创建关联表 #

-- 主表：存储固定字段
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(20) NOT NULL,
    city VARCHAR(20) NOT NULL,
    kurs VARCHAR(20) NOT NULL,
    INDEX idx_name (name) -- 用于后续关联匹配
);

-- 属性表：存储动态options数据，与主表关联
CREATE TABLE user_options (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    ao_id INT NOT NULL,
    option_name VARCHAR(100) NOT NULL,
    option_value VARCHAR(100) NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id),
    -- 针对常用查询字段创建索引，提升搜索效率
    INDEX idx_user_id (user_id),
    INDEX idx_ao_id (ao_id),
    INDEX idx_option_name_value (option_name, option_value)
);

2. 嵌套解析JSON并插入数据 #

通过两层JSON_TABLE解析外层用户数组和内层options数组，分别插入主表和属性表：

SET @json = '[{
    "name":"ivan","city":"london","kurs":"1","options": [{
        "ao_id": 90630,
        "name": "Высота предмета",
        "value": "3.7 см"
    }, {
        "ao_id": 90673,
        "name": "Ширина предмета",
        "value": "4 см"
    }, {
        "ao_id": 90745,
        "name": "Ширина упаковки",
        "value": "4 см"
    }]
},{
    "name":"lena","city":"tokio","kurs":"5", "options": [{
        "ao_id": 90630,
        "name": "Высота предмета",
        "value": "9.7 см"
    }]
},{
    "name":"misha","city":"kazan","kurs":"3", "options": [{
        "ao_id": 90999,
        "name": "Высота",
        "value": "5.7 см"
    }]
}]';

-- 插入主表数据
INSERT INTO users (name, city, kurs)
SELECT name, city, kurs
FROM JSON_TABLE(@json, '$[*]' COLUMNS(
    name VARCHAR(20) PATH '$.name',
    city VARCHAR(20) PATH '$.city',
    kurs VARCHAR(20) PATH '$.kurs'
)) AS user_data;

-- 插入属性表数据，通过name关联主表（若name不唯一，可改用临时行号匹配）
INSERT INTO user_options (user_id, ao_id, option_name, option_value)
SELECT u.id, opt.ao_id, opt.option_name, opt.option_value
FROM JSON_TABLE(@json, '$[*]' COLUMNS(
    name VARCHAR(20) PATH '$.name',
    options JSON PATH '$.options'
)) AS user_with_options
JOIN users u ON u.name = user_with_options.name
JOIN JSON_TABLE(user_with_options.options, '$[*]' COLUMNS(
    ao_id INT PATH '$.ao_id',
    option_name VARCHAR(100) PATH '$.name',
    option_value VARCHAR(100) PATH '$.value'
)) AS opt;

方案优势 #

• 索引效率高：可对任意属性字段建索引，大幅提升搜索、筛选速度
• 数据结构清晰：符合关系型设计，便于复杂关联查询和维护
• 类型安全：字段类型明确，避免JSON查询的隐式转换性能损耗

方案二：JSON类型存储（适合属性频繁变化场景） #

将options直接以JSON类型存储在表中，通过生成列或全文索引实现基本搜索需求，保留灵活性。

1. 创建带JSON字段的表 #

CREATE TABLE users_with_json (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(20) NOT NULL,
    city VARCHAR(20) NOT NULL,
    kurs VARCHAR(20) NOT NULL,
    options JSON NOT NULL,
    -- 针对options中的ao_id生成虚拟列并建索引，支持快速匹配
    ao_ids JSON GENERATED ALWAYS AS (JSON_EXTRACT(options, '$[*].ao_id')) VIRTUAL,
    INDEX idx_ao_ids (ao_ids),
    -- MySQL 8.0+支持JSON全文索引，可对属性名称/值进行全文搜索
    FULLTEXT INDEX idx_options_fulltext (options)
);

2. 插入数据 #

INSERT INTO users_with_json (name, city, kurs, options)
SELECT name, city, kurs, options
FROM JSON_TABLE(@json, '$[*]' COLUMNS(
    name VARCHAR(20) PATH '$.name',
    city VARCHAR(20) PATH '$.city',
    kurs VARCHAR(20) PATH '$.kurs',
    options JSON PATH '$.options'
)) AS user_data;

查询示例 #

-- 筛选包含ao_id=90630的用户
SELECT * FROM users_with_json
WHERE JSON_CONTAINS(options, '{"ao_id": 90630}', '$[*]');

-- 全文搜索属性名称包含"Высота"的用户
SELECT * FROM users_with_json
WHERE MATCH(options) AGAINST('"Высота"' IN BOOLEAN MODE);

方案优势 #

• 灵活性强：无需修改表结构即可适配新的属性类型
• 插入简单：单表插入，无需关联多表操作
• 适合属性不固定、查询频率较低的场景

方案选择建议 #

• 若需要频繁对options内的字段进行搜索、筛选或排序，优先选择关系型结构化存储，性能和可维护性更优；
• 若属性变化频繁，且查询需求简单，可选择JSON类型存储，兼顾灵活性和开发效率。

内容的提问来源于stack exchange，提问作者Art Cul