如何在MySQL 5.7.12中通过查询语句合并JSON数组中的字段为格式化字符串

刚好我之前也处理过MySQL 5.7版本下的JSON数组拼接需求，这个版本确实不支持JSON_TABLE，不过我们可以用两种可行的方法来实现你要的效果：

方法一：利用数字辅助表+GROUP_CONCAT #

这种方法不需要创建自定义函数，适合临时查询场景。核心思路是生成一组连续数字（对应JSON数组的索引），逐个提取数组元素中的motivoStr，再用GROUP_CONCAT拼接。

假设你的表名为your_table，JSON列名为json_col，SQL语句如下：

SELECT GROUP_CONCAT(
    -- 提取对应索引的motivoStr并去除引号
    json_col->>"$[", nums.i, "].motivoStr"
    SEPARATOR '<br>'
) AS concatenated_motivoStr
FROM your_table t
-- 生成0到9的数字序列（如果数组元素更多，可以继续添加UNION ALL SELECT n）
JOIN (
    SELECT 0 AS i UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
    UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
) nums 
-- 只取数组长度范围内的索引
ON nums.i < JSON_LENGTH(t.json_col);

说明： #

• JSON_LENGTH(t.json_col)用来获取JSON数组的元素个数，确保我们只处理存在的元素
• ->>运算符等价于JSON_UNQUOTE(JSON_EXTRACT(...))，可以直接得到不带引号的字符串
• 如果你的JSON数组元素数量超过10，只需要在nums子查询中继续添加UNION ALL SELECT n即可

方法二：创建自定义函数（更灵活） #

如果需要多次执行这个拼接操作，或者数组长度不确定，自定义函数会更方便。我们可以写一个函数遍历JSON数组，逐个拼接motivoStr：

DELIMITER //
CREATE FUNCTION concat_motivoStr(json_data JSON) RETURNS TEXT
DETERMINISTIC
BEGIN
    DECLARE result TEXT DEFAULT '';
    DECLARE array_len INT DEFAULT JSON_LENGTH(json_data);
    DECLARE idx INT DEFAULT 0;
    DECLARE current_str TEXT;
    
    -- 循环遍历数组每个元素
    WHILE idx < array_len DO
        -- 提取当前索引的motivoStr
        SET current_str = json_data->>"$[", idx, "].motivoStr";
        -- 拼接结果，第一个元素前不加<br>
        IF idx > 0 THEN
            SET result = CONCAT(result, '<br>', current_str);
        ELSE
            SET result = current_str;
        END IF;
        SET idx = idx + 1;
    END WHILE;
    
    RETURN result;
END //
DELIMITER ;

创建好函数后，调用就非常简单了：

SELECT concat_motivoStr(json_col) AS concatenated_motivoStr FROM your_table;

说明： #

• 函数会自动处理任意长度的JSON数组，不需要手动调整数字序列
• 需要确保你有创建函数的权限（如果没有的话，方法一更适合）

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