MySQL 5.5.5存储过程构建多维JSON的技术实现求助

我拥有一个结构为name | age | date | value的数据表，具体数据如下：
John | 20 | 2020-01-01 | 5
Peter| 21 | 2020-01-02 | 6.5
John | 26 | 2019-02-26 | 1.8
John | 20 | 2029-10-13 | 0.7
Peter| 47 | 2020-01-18 | 11.3
Peter| 21 | 2020-02-01 | 41.7
John | 20 | 2020-01-22 | 4

我需要仅通过MySQL 5.5.5存储过程（不使用JSON聚合函数）生成如下格式的多维JSON：

{ 
"John (20)" : [["2020-01-01", 5], ["2029-10-13", 0.7], ["2020-01-22", 4]], 
"John (26)" : [["2019-02-26", 1.8]], 
"Peter (21)" : [["2020-01-02", 6.5], ["2020-02-01", 41.7]], 
"Peter (47)" : [["2020-01-18", 11.3]] 
}

目前我仅能生成扁平JSON，对应的SQL语句为：

select concat('[', group_concat( '{"name":"',`name`,'",', '"age":"',`age`,'",', '"date":"',`date`,'",', '"value":',`value`, '}' separator ','), ']') from `data`

因为MySQL 5.5没有原生JSON函数，我们只能通过嵌套分组+字符串拼接的方式来实现目标结构，核心思路是先按name+age分组生成内层的数组，再把这些分组结果拼接成外层的JSON对象。下面是完整的存储过程实现：

完整存储过程代码 #

DELIMITER //
CREATE PROCEDURE GenerateMultiDimensionalJSON()
BEGIN
    -- 临时调整group_concat的最大长度，避免数据过多时被截断
    SET SESSION group_concat_max_len = 1000000;
    
    -- 生成最终的多维JSON
    SELECT CONCAT('{ ', GROUP_CONCAT(CONCAT('"', key_name, '" : ', value_array) SEPARATOR ', '), ' }') AS final_json
    FROM (
        -- 内层分组：按name和age聚合，生成每个分组对应的二维数组字符串
        SELECT 
            CONCAT(`name`, ' (', `age`, ')') AS key_name,
            CONCAT('[', GROUP_CONCAT(CONCAT('["', `date`, '", ', `value`, ']') SEPARATOR ', '), ']') AS value_array
        FROM `data`
        GROUP BY `name`, `age`
    ) AS grouped_data;
END //
DELIMITER ;

代码解释 #

1. 调整group_concat长度：MySQL默认的group_concat_max_len比较小（比如1024），如果你的数据量较大，拼接的字符串会被截断，所以先临时调整会话级别的长度。
2. 内层子查询：
   • 按name和age分组，把每个分组的date和value拼接成["日期", 数值]的格式
   • 用GROUP_CONCAT把这些单个数组元素拼接成完整的二维数组字符串，比如[["2020-01-01", 5], ["2029-10-13", 0.7]]
3. 外层拼接：把每个分组的key_name（比如John (20)）和对应的value_array拼接成"key": 数组的键值对，再用GROUP_CONCAT把所有键值对拼接成最终的JSON对象。

使用方法 #

调用存储过程即可得到目标JSON：

CALL GenerateMultiDimensionalJSON();

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