MySQL解析类JSON格式数据：寻求替代JSON_EXTRACT的方案

方法1：修复格式为标准JSON后解析 #

你的数据核心问题是缺少JSON要求的键名双引号，先通过字符串替换补全格式，就能用原生JSON函数处理，比SUBSTRING_INDEX更灵活，适配1-3组数据的场景。

操作步骤： #

1. 替换无引号的键名：把quantity:替换为"quantity":，cost:替换为"cost":
2. 给整串数据套数组括号[]，转换成标准JSON数组
3. 用JSON_TABLE（MySQL 8.0+支持）批量提取每组数据

示例SQL（MySQL）： #

假设原表为source_table，目标表为target_table（含quantity、cost字段）：

INSERT INTO target_table (quantity, cost)
SELECT 
    jt.quantity,
    jt.cost
FROM source_table,
JSON_TABLE(
    -- 修复格式：补键名引号+包裹成数组
    CONCAT('[', REPLACE(REPLACE(data_str, 'quantity:', '"quantity":'), 'cost:', '"cost":'), ']'),
    '$[*]' COLUMNS (
        quantity INT PATH '$.quantity',
        cost DECIMAL(10,3) PATH '$.cost'
    )
) AS jt;

该方法会自动拆分每组数据，生成对应行插入目标表，无需针对组数写分支逻辑。

方法2：正则表达式提取（适配低版本MySQL） #

如果你的MySQL版本不支持JSON_TABLE，可以用正则匹配直接提取每组的字段值：

INSERT INTO target_table (quantity, cost)
-- 提取第1组
SELECT
    REGEXP_SUBSTR(data_str, 'quantity: ([0-9]+)', 1, 1, '', 1) AS quantity,
    REGEXP_SUBSTR(data_str, 'cost: ([0-9.]+)', 1, 1, '', 1) AS cost
FROM source_table
WHERE REGEXP_SUBSTR(data_str, 'quantity:', 1, 1) IS NOT NULL
UNION ALL
-- 提取第2组
SELECT
    REGEXP_SUBSTR(data_str, 'quantity: ([0-9]+)', 1, 2, '', 1) AS quantity,
    REGEXP_SUBSTR(data_str, 'cost: ([0-9.]+)', 1, 2, '', 1) AS cost
FROM source_table
WHERE REGEXP_SUBSTR(data_str, 'quantity:', 1, 2) IS NOT NULL
UNION ALL
-- 提取第3组
SELECT
    REGEXP_SUBSTR(data_str, 'quantity: ([0-9]+)', 1, 3, '', 1) AS quantity,
    REGEXP_SUBSTR(data_str, 'cost: ([0-9.]+)', 1, 3, '', 1) AS cost
FROM source_table
WHERE REGEXP_SUBSTR(data_str, 'quantity:', 1, 3) IS NOT NULL;

通过REGEXP_SUBSTR的第4个参数指定匹配第N组数据，配合UNION ALL合并1-3组的结果。

对比SUBSTRING_INDEX的优势 #

• 修复JSON格式的方法：逻辑清晰，扩展性强，后续新增字段只需调整替换规则和JSON_TABLE的列定义，不用嵌套多层截取逻辑。
• 正则方法：代码可读性更高，处理多组数据时，不会像嵌套SUBSTRING_INDEX那样导致代码臃肿难维护。

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