mysql解析json数据组怎么获取数据组所有字段

在mysql的实际应用场景中，经常会遇到将结构化数据以json数组的形式存储在表字段中的情况，此时需要从中提取数据组包含的所有字段内容，mysql提供了多个内置函数支持这类json数据的解析操作。

基础解析函数json_extract的使用

json_extract是mysql中基础的json解析函数，可以通过路径表达式提取json数据中的指定内容，对于简单的json数据组，可以通过指定路径获取对应字段。

假设我们有如下表结构，其中info字段存储json数组数据：

-- 创建测试表
CREATE TABLE test_json (
    id INT PRIMARY KEY AUTO_INCREMENT,
    info JSON
);

-- 插入测试数据，info字段存储包含两个对象的json数组
INSERT INTO test_json (info) VALUES (
    '[{"name": "张三", "age": 20, "score": 85}, {"name": "李四", "age": 22, "score": 90}]'
);

如果要获取json数组中第一个对象的所有字段，可以使用如下语句：

-- 提取第一个对象的所有字段
SELECT 
    json_extract(info, '$[0].name') AS name,
    json_extract(info, '$[0].age') AS age,
    json_extract(info, '$[0].score') AS score
FROM test_json;

这种方式需要手动指定每个字段的路径，当字段数量较多时操作会比较繁琐，适合字段数量固定且较少的场景。

使用JSON_TABLE批量提取所有字段

mysql 8.0及以上版本提供了JSON_TABLE函数，可以将json数组转换为关系型表的行和列，一次性提取数据组的所有字段，不需要逐个指定路径。

针对上面的测试数据，使用JSON_TABLE提取所有对象的全部字段的语句如下：

-- 使用JSON_TABLE提取json数组所有字段
SELECT 
    jt.name,
    jt.age,
    jt.score
FROM test_json,
    JSON_TABLE(
        info,
        '$[*]' COLUMNS (
            name VARCHAR(20) PATH '$.name',
            age INT PATH '$.age',
            score INT PATH '$.score'
        )
    ) AS jt;

上述语句中，'$[*]'表示遍历json数组的所有元素，COLUMNS子句中定义了要提取的字段名、类型和对应的json路径，执行后会返回json数组中所有对象的所有指定字段内容，每个对象对应一行结果。

动态获取未知字段的场景处理

如果json数据组中的字段不固定，无法提前预知所有字段名称，可以先通过json_keys函数获取所有键名，再结合动态sql实现所有字段的提取。

首先获取json数组第一个对象的所有键名：

-- 获取json数组第一个对象的所有键名
SELECT json_keys(json_extract(info, '$[0]')) AS keys_list FROM test_json;

得到键名列表后，可以拼接动态sql语句，遍历所有键名生成对应的提取语句，不过这种方式在mysql中需要借助存储过程实现，示例存储过程如下：

-- 创建存储过程动态提取所有字段
DELIMITER //
CREATE PROCEDURE get_all_json_fields()
BEGIN
    DECLARE field_list TEXT;
    DECLARE sql_str TEXT;
    -- 获取所有键名并拼接成提取语句片段
    SELECT GROUP_CONCAT(CONCAT('json_extract(info, ''$[*].', k.column_value, ''') AS ', k.column_value)) 
    INTO field_list
    FROM test_json,
        JSON_TABLE(json_keys(json_extract(info, '$[0]')), '$[*]' COLUMNS (column_value VARCHAR(20) PATH '$')) AS k
    LIMIT 1;
    
    -- 拼接完整查询语句
    SET sql_str = CONCAT('SELECT ', field_list, ' FROM test_json');
    -- 执行动态sql
    PREPARE stmt FROM sql_str;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END //
DELIMITER ;

-- 调用存储过程
CALL get_all_json_fields();

不同版本mysql的适配建议

如果使用的是mysql 5.7版本，不支持JSON_TABLE函数，可以通过json_extract配合SUBSTRING_INDEX等字符串函数拆分json数组，再逐个提取字段，不过这种方式逻辑更复杂，效率也更低，建议优先升级到mysql 8.0版本使用JSON_TABLE函数处理。

另外需要注意，存储json数据的字段建议设置为JSON类型，而不是VARCHAR类型，这样mysql会自动校验json格式的正确性，也能更好地支持各类json解析函数的使用。

mysqljson_extractJSON_TABLEjson数据解析数据组字段获取修改时间：2026-07-14 00:27:22