MySQL 5.7如何将ids字段的数组值展平拆分为多行数据

MySQL 5.7 版本没有内置JSON_TABLE函数，无法直接对JSON数组列进行展平操作，你可以通过临时生成连续下标序列的方式实现需求，具体操作如下：

• 核心思路：生成一组连续的数字下标，和数组的长度做关联匹配，逐个提取数组对应下标的元素生成新行。

完整SQL示例 #

假设你的原始表名为raw_data，ids列为JSON类型，最多不超过10个元素，SQL如下：

SELECT 
    t.data0,
    t.data1,
    t.data2,
    JSON_UNQUOTE(JSON_EXTRACT(t.ids, CONCAT('$[', n.idx, ']'))) AS ids
FROM 
    raw_data t
-- 生成0~9的下标序列，数组最长支持10个元素，元素更多可继续追加UNION ALL行
INNER JOIN (
    SELECT 0 AS idx 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
) n
-- 关联条件：下标小于数组长度，保证不会越界
ON n.idx < JSON_LENGTH(t.ids)
-- 可根据需求排序
ORDER BY t.data0, ids;

特殊调整说明 #

你给出的预期结果中跳过了第一个数组元素（原始数组[101,102,103]的101未出现在结果中），如果需要实现该效果，只需修改关联条件即可：

ON n.idx > 0 AND n.idx < JSON_LENGTH(t.ids)

如果你的ids列是字符串格式存储的数组，不是JSON类型，将SQL中所有t.ids替换为CAST(t.ids AS JSON)即可正常使用。

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