MySQL同一列混合存储普通数据与JSON数组时如何编写SELECT语句取值

MySQL 5.7及以上版本提供了完整的原生JSON处理函数，可以直接通过函数判定值类型后做归一化查询，不需要在业务代码层做额外拆分。
#

场景1：需要把所有值打平为逐行单值返回（最常用） #

不管列里存的是普通字符串/数字，还是JSON数组内的元素，最终每一行只返回一个独立值，JSON数组会被拆分为多行和原表字段关联。

MySQL 8.0+ 推荐写法（用JSON_TABLE，性能最优） #

SELECT
  t.id, -- 替换为你表中的主键、其他需要查询的业务字段
  j.item AS target_value
FROM your_table t -- 替换为你的真实表名
LEFT JOIN JSON_TABLE(
  -- 先把非数组的普通值包装为单元素JSON数组，统一数据结构
  CASE
    WHEN JSON_VALID(t.mixed_col) AND JSON_TYPE(t.mixed_col) = 'ARRAY' THEN t.mixed_col
    ELSE JSON_ARRAY(t.mixed_col)
  END,
  '$[*]' COLUMNS (item PATH '$')
) AS j ON 1 = 1;

MySQL 5.7 兼容写法 #

5.7版本没有内置JSON_TABLE函数，可以提前建一张存储连续正整数的辅助表seq（字段n存1~N的整数，N大于你JSON数组的最大可能长度即可），通过下标遍历拆分数组：

SELECT
  t.id,
  -- 数组按位取值，普通值直接返回
  CASE
    WHEN JSON_VALID(t.mixed_col) AND JSON_TYPE(t.mixed_col) = 'ARRAY'
      THEN JSON_UNQUOTE(JSON_EXTRACT(t.mixed_col, CONCAT('$[', s.n - 1, ']')))
    ELSE t.mixed_col
  END AS target_value
FROM your_table t
LEFT JOIN seq s
  ON s.n <= CASE
    WHEN JSON_VALID(t.mixed_col) AND JSON_TYPE(t.mixed_col) = 'ARRAY' THEN JSON_LENGTH(t.mixed_col)
    ELSE 1
  END;

场景2：不需要拆行，统一返回格式给程序端处理 #

直接把所有普通值包装为单元素JSON数组，程序端统一按数组结构解析即可，逻辑最简单：

SELECT
  id,
  CASE
    WHEN JSON_VALID(mixed_col) AND JSON_TYPE(mixed_col) = 'ARRAY' THEN mixed_col
    ELSE JSON_ARRAY(mixed_col)
  END AS normalized_array
FROM your_table;

注意事项 #

• 所有JSON函数调用前必须先用JSON_VALID()做校验，避免非JSON格式的普通值传入JSON函数触发报错
• 读取JSON内的字符串值时，用JSON_UNQUOTE()去掉值外层包裹的双引号，也可以用简写运算符->>替代JSON_UNQUOTE(JSON_EXTRACT(字段, 路径))
• 同列混存多结构数据属于反范式设计，查询、索引维护成本远高于规范结构，如果业务允许，优先将JSON数组拆分为独立的关联子表存储，长期收益更高

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