MySQL 5.6自定义json_extract_c函数处理多值JSON失效求助

你的问题根源在于原自定义函数是基于字符串单次截取实现的，只能匹配到JSON数组中第一个元素的目标字段，而且MySQL函数本身只能返回单个值，无法直接生成多行结果。针对MySQL 5.6没有原生JSON函数的限制，我们可以通过「数字辅助表+拆分数组+字段提取」的组合方案来解决。

步骤1：创建数字辅助表 #

首先需要一个包含连续整数的表，用来遍历JSON数组的索引（JSON数组从0开始计数）：

CREATE TABLE numbers (n INT PRIMARY KEY);
-- 插入足够多的数字，覆盖你业务中JSON数组的最大长度
INSERT INTO numbers VALUES (0), (1), (2), (3), (4), (5);

步骤2：实现JSON数组长度计算函数 #

这个函数通过统计数组元素的分隔符},{数量来计算数组长度：

DELIMITER $$
DROP FUNCTION IF EXISTS json_array_length_c$$
CREATE DEFINER=`root`@`%` FUNCTION `json_array_length_c`(json_arr TEXT)
RETURNS INT
BEGIN
    -- 空数组直接返回0，否则统计分隔符数量+1得到元素个数
    RETURN IF(json_arr = '[]', 0, LENGTH(json_arr) - LENGTH(REPLACE(json_arr, '},{', '}~{')) + 1);
END$$
DELIMITER ;

步骤3：实现数组元素提取函数 #

根据索引提取JSON数组中的单个元素，并处理前后的括号：

DELIMITER $$
DROP FUNCTION IF EXISTS json_array_element_c$$
CREATE DEFINER=`root`@`%` FUNCTION `json_array_element_c`(json_arr TEXT, idx INT)
RETURNS TEXT CHARSET latin1
BEGIN
    DECLARE elem TEXT;
    -- 通过两次SUBSTRING_INDEX截取对应索引的元素
    SET elem = SUBSTRING_INDEX(SUBSTRING_INDEX(json_arr, '},{', idx + 1), '},{', -1);
    -- 处理第一个元素的前括号和最后一个元素的后括号
    IF idx = 0 THEN
        SET elem = TRIM(LEADING '[' FROM elem);
    END IF;
    IF idx = json_array_length_c(json_arr) - 1 THEN
        SET elem = TRIM(TRAILING ']' FROM elem);
    END IF;
    RETURN elem;
END$$
DELIMITER ;

步骤4：修正原字段提取函数 #

调整后的函数专注于从单个JSON对象中提取指定字段：

DELIMITER $$
DROP FUNCTION IF EXISTS `json_extract_c`$$
CREATE DEFINER=`root`@`%` FUNCTION `json_extract_c`(json_obj TEXT, required_field VARCHAR(255))
RETURNS TEXT CHARSET latin1
BEGIN
    DECLARE field_name VARCHAR(255);
    -- 处理带$.的字段名，提取实际字段
    SET field_name = SUBSTRING_INDEX(required_field, '$.', -1);
    -- 截取字段值并去除前后引号
    RETURN TRIM(BOTH '"' FROM SUBSTRING_INDEX(SUBSTRING_INDEX(json_obj, CONCAT('"', field_name, '"'), -1), '",', 1));
END$$
DELIMITER ;

步骤5：查询拆分多行结果 #

通过关联数字表，遍历数组索引，逐个提取元素和字段：

SELECT 
    t.user_id,
    json_extract_c(json_array_element_c(t.cb_contactgroup, n.n), '$.cb_mobile') AS cb_mobile,
    json_extract_c(json_array_element_c(t.cb_contactgroup, n.n), '$.cb_phonefixedline') AS cb_phonefixedline
FROM 
    your_table t  -- 替换成你的实际表名
JOIN 
    numbers n ON n.n < json_array_length_c(t.cb_contactgroup)
WHERE 
    t.user_id = 291;

执行这个查询后，就能得到你预期的两行结果：

注意事项 #

1. 确保数字表numbers中的整数数量足够覆盖你业务中JSON数组的最大长度，如果数组元素更多，需要插入更多数字。
2. 该方案依赖JSON格式的规范性，如果字段值中包含},{这类特殊字符，可能会导致统计或截取错误（毕竟MySQL 5.6没有原生JSON解析能力）。

内容的提问来源于stack exchange，提问作者Dhananjay V.