MySQL存储过程内JSON_EXTRACT提取child_list字段始终返回null问题

问题原因 #

提取child_list返回NULL的核心原因是存储过程里声明的json变量长度不足：
你定义的json类型为VARCHAR(255)，但传入的JSON字符串实际长度远超255字符，赋值时会被自动截断，导致JSON结构损坏，JSON_EXTRACT无法正常解析就返回NULL。

修复方案 #

1. 调整变量类型 #

把存储过程里存储JSON内容的变量类型从VARCHAR(255)改成JSON类型，或者足够长度的TEXT类型，MySQL原生JSON类型会自动校验格式，更适合JSON操作场景。

2. 简化提取语法 #

可以用更简洁的->运算符代替JSON_EXTRACT，写法更直观：

-- 等价于JSON_EXTRACT(json, "$.child_list")
SELECT json->'$.child_list' AS questions_list;

3. 遍历JSON数组实现 #

如果需要遍历child_list里的每个元素执行业务逻辑，可以配合JSON_LENGTH获取数组长度，再用循环逐个读取元素，完整存储过程示例如下：

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `adocs_sp_web_questionnaire_has_child`(IN 
`var_has_child_text` JSON, IN `var_parent_id` INT, OUT `var_child_text` TEXT)
BEGIN 
    DECLARE counter INT DEFAULT 0;
    DECLARE count_max INT DEFAULT 0;
    DECLARE current_question JSON;

    -- 获取child_list数组的总长度
    SET count_max = JSON_LENGTH(var_has_child_text->'$.child_list');

    -- 循环遍历数组每个元素，索引从0开始
    WHILE counter < count_max DO
        -- 提取第counter个元素
        SET current_question = JSON_EXTRACT(var_has_child_text, CONCAT('$.child_list[', counter, ']'));
        -- 此处替换为你的业务逻辑，比如提取name字段：
        -- SELECT current_question->>'$.name' INTO @current_question_name;

        SET counter = counter + 1;
    END WHILE;
END$$
DELIMITER ;

如果需要递归处理深层的has_child嵌套数组，只需要在循环里判断当前元素是否存在has_child字段，再嵌套一层相同的遍历逻辑即可。

如果调整变量长度后还是返回NULL，可以先执行SELECT JSON_VALID(var_has_child_text);校验传入的JSON格式是否合法，避免隐形换行、特殊字符导致JSON解析失败。

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