如何将JSON数组按指定规则自动转换为多张MySQL SQL表

方案可行性与合理性说明 #

• 需求完全可以实现，但不推荐纯用MySQL原生功能完成，整套流程更适合用脚本预处理JSON后再导入MySQL，成本低、可维护性更高
• 适用场景：你这套JSON转关系表的方案适合JSON结构相对固定、需要高频查询JSON内部字段的场景；如果JSON结构变动频繁，直接用MySQL原生JSON字段+生成索引的方案更合适，无需每次调整表结构
• 注意点：步骤2中判断key是否同时存在object和非object取值需要全量扫描JSON数据，数据量级过大时预处理耗时会相应提升

具体实现步骤 #

步骤1：JSON预处理（推荐用Python/JS等脚本实现，远快于纯MySQL实现） #

核心逻辑如下，附Python核心实现代码：

import json
from collections import defaultdict

# 全局状态变量
used_keys = set()
key_type_map = defaultdict(set)
auto_increment_id = 1
unique_id_key = None
path_object_map = defaultdict(list)

# 第一轮扫描：收集所有已用key、每个key对应的取值类型
def scan_json(node, parent_key=None):
    if isinstance(node, dict):
        for k, v in node.items():
            used_keys.add(k)
            key_type_map[k].add(type(v).__name__)
            scan_json(v, k)
    elif isinstance(node, list):
        for item in node:
            scan_json(item, parent_key)

# 第二轮处理：转换数组、统一object类型、加唯一ID、记录路径数据
def process_json(node, parent_id=None, current_path=(), is_top_array=False):
    global auto_increment_id
    if is_top_array:
        # 最外层数组不做转换
        return [process_json(item, parent_id, current_path) for item in node]
    elif isinstance(node, list):
        # 非最外层数组转下标从1开始的object
        converted_obj = {str(idx+1): val for idx, val in enumerate(node)}
        return process_json(converted_obj, parent_id, current_path)
    elif isinstance(node, dict):
        # 给当前object加唯一ID
        current_obj_id = auto_increment_id
        node[unique_id_key] = current_obj_id
        auto_increment_id += 1
        # 记录当前object所属路径、父ID、字段值
        path_object_map[current_path].append({
            "recordid_parent": parent_id,
            **node
        })
        # 递归处理子节点
        for k, v in node.items():
            if k == unique_id_key:
                continue
            # 当前key同时有object和非object取值时，非object值转genericvalue结构
            if "dict" in key_type_map[k] and type(v).__name__ != "dict":
                v = {"genericvalue": v}
                node[k] = v
            if isinstance(v, (dict, list)):
                process_json(v, current_obj_id, current_path + (k,))
        return node
    else:
        return node

# 主执行逻辑
if __name__ == "__main__":
    # 加载原始JSON
    with open("你的JSON文件路径.json", "r", encoding="utf-8") as f:
        raw_data = json.load(f)
    # 第一轮扫描
    scan_json(raw_data)
    # 找到未被使用的唯一ID键名
    unique_id_key = "recordid"
    while unique_id_key in used_keys:
        unique_id_key = f"_{unique_id_key}"
    # 第二轮处理JSON
    processed_json = process_json(raw_data, is_top_array=True)
    # 输出处理后的JSON
    print(json.dumps(processed_json, indent=2, ensure_ascii=False))
    # 输出每个路径对应的表结构信息
    for path, obj_list in path_object_map.items():
        table_name = "flavors_" + "_".join(path) if path else "flavors"
        all_fields = set()
        for obj in obj_list:
            all_fields.update(obj.keys())
        print(f"表名：{table_name}，字段：{', '.join(all_fields)}")

步骤2：MySQL建表与数据导入 #

1. 建表规则：
   • 表名用路径拼接，比如空路径对应flavors表，tags路径对应flavors_tags表（MySQL表名不支持点，用下划线替代）
   • 固定包含recordid_parent（int类型，父级ID，无父级则为null）、recordid（int类型，主键）两个字段，其余字段为该路径下所有object出现过的键，字段类型可根据实际需求设为varchar或JSON
2. 导入规则：
   把path_object_map中每个路径对应的object列表，批量插入到对应表中，不存在的字段值填null即可

纯MySQL实现说明（不推荐） #

如果必须用纯MySQL实现，需要依赖MySQL 8.0以上版本的递归CTE、JSON_TABLE、JSON_SEARCH、JSON_SET等函数，代码非常冗长，且性能远低于脚本预处理方案，仅适合数据量极小的场景，这里不展开提供代码。

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