MySQL存储过程中如何逐个读取JSON对象并获取JSON数据长度？

首先得明确你的存储过程输出的是逗号分隔的多个独立JSON对象（而非标准JSON数组），所以核心思路是先把这个字符串转换成合法的JSON数组，再利用MySQL的原生JSON函数来完成遍历和长度统计。

具体实现步骤 #

1. 转换为合法JSON数组
   原始输出格式是{"1":"data1"},{"2":"data2"},{"3":"data3"}，我们只需要在字符串首尾加上[和]，就能把它变成标准的JSON数组：[{"1":"data1"},{"2":"data2"},{"3":"data3"}]。

2. 获取JSON数据的长度（即对象个数）
   用MySQL的JSON_LENGTH()函数直接处理转换后的数组，就能得到对象的总数。

3. 逐个取出JSON对象并处理
   利用JSON_TABLE()函数将JSON数组拆分为行记录，这样就能遍历每个对象，后续可以直接用于插入其他表或者做其他业务操作。

代码示例 #

假设你的存储过程输出的JSON字符串存在变量@raw_json_str中，我们可以这样处理：

-- 1. 模拟存储过程输出的原始JSON字符串
SET @raw_json_str = '{"1":"data1"},{"2":"data2"},{"3":"data3"}';

-- 2. 转换为合法JSON数组
SET @json_array = CONCAT('[', @raw_json_str, ']');

-- 3. 获取JSON对象的总个数（长度）
SELECT JSON_LENGTH(@json_array) AS total_objects;

-- 4. 逐个取出每个JSON对象，生成行记录
SELECT jt.obj
FROM JSON_TABLE(
    @json_array,
    '$[*]' COLUMNS (obj JSON PATH '$')
) AS jt;

-- 5. 示例：将每个对象插入到目标表（假设目标表target_table有json_data列）
INSERT INTO target_table (json_data)
SELECT jt.obj
FROM JSON_TABLE(
    @json_array,
    '$[*]' COLUMNS (obj JSON PATH '$')
) AS jt;

特殊情况处理 #

如果你的原始字符串可能存在末尾多余的逗号（比如{"1":"data1"},{"2":"data2"},），可以先用TRIM()函数去掉末尾的逗号再转换：

SET @raw_json_str = TRIM(TRAILING ',' FROM @raw_json_str);
SET @json_array = CONCAT('[', @raw_json_str, ']');

如果JSON对象内部包含逗号（比如{"1":"data,1"}），不用担心，因为我们是基于完整的JSON对象分隔，只要原始的每个JSON对象是合法的，转换后的数组就不会有问题。

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