MySQL中使用逗号分隔的matrix字段做Left Join关联查询的问题

方案1：表结构改造（大数据量场景最优选择） #

当前主表table_a用逗号分隔存储多值编码违反数据库第一范式，是关联查询效率低的核心原因，改造后性能提升最明显：

• 新增中间表table_a_item，存储字段为Order、single_cod_item
• 将table_a每行的cod_item按逗号拆分，每个编码对应生成一行数据存入中间表，例如原行cod_item=1,2,3、Order=1拆分后生成3行数据：(1,1)、(1,2)、(1,3)
• 给中间表的single_cod_item字段加普通索引，table_b的cod_item字段设置为主键/唯一索引
  改造完成后查询SQL如下，关联可全走索引，亿级数据也可稳定运行：

SELECT 
  a.`Order`,
  GROUP_CONCAT(
    DISTINCT CONCAT(b.cod_item, ' - ', b.Name) 
    ORDER BY b.cod_item 
    SEPARATOR ', '
  ) AS CONTENT
FROM table_a_item a
INNER JOIN table_b b ON a.single_cod_item = b.cod_item
GROUP BY a.`Order`;

方案2：临时查询方案（不修改表结构场景） #

如果暂时无法改造表结构，MySQL 8.0及以上版本可使用JSON_TABLE函数拆分多值字段，比自定义拆分函数、FIND_IN_SET关联效率高3~5倍：

-- 先调整GROUP_CONCAT长度限制，避免内容被截断
SET SESSION group_concat_max_len = 1024000;

WITH split_cod AS (
  SELECT 
    a.`Order`,
    j.single_cod
  FROM table_a a,
       JSON_TABLE(
         -- 将逗号分隔的字符串转为JSON数组格式
         CONCAT('["', REPLACE(a.cod_item, ',', '","'), '"]'),
         '$[*]' COLUMNS (single_cod VARCHAR(255) PATH '$')
       ) j
)
SELECT 
  s.`Order`,
  GROUP_CONCAT(
    CONCAT(b.cod_item, ' - ', b.Name) 
    ORDER BY b.cod_item 
    SEPARATOR ', '
  ) AS CONTENT
FROM split_cod s
INNER JOIN table_b b ON s.single_cod = b.cod_item
GROUP BY s.`Order`;

性能优化注意事项 #

• 必须确保table_b的cod_item字段有唯一索引，关联时可直接走索引匹配，避免全表扫描
• 若table_a数据量超过千万，建议按Order范围分批查询，避免一次性聚合占用过多内存导致OOM
• 低于8.0的MySQL版本不支持JSON_TABLE，可提前用脚本将table_a的多值字段拆分导入临时表，给临时表加索引后再关联，性能远高于实时用函数拆分查询

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