如何用单条MySQL语句查询另一表中逗号分隔元素的关联数据？

问题描述 #

现有两张表A和B：

• 表A包含project_name、model_types字段，其中model_types为逗号分隔的字符串，示例数据：project_animals对应的model_types值为'detection,segmentation,detection,classification'；
• 表B包含model、labels、image_types字段，存储各模型类型的对应信息。

要求无需在MySQL外部拆分model_types字段，使用单条SQL语句获取表A中每个模型类型对应的labels和image_types，得到如下格式的结果：

Model_types    |  labels  | image_types 
detection         cat,dog   jpg,png 
segmentation      rat,dog   jpg,tif 
detection         cat,dog   jpg,png 
classification    cow,cat   bmp,png

是否可以实现该需求？

解决方案 #

可以实现，在MySQL 8.0及以上版本中，可通过**递归CTE（公共表表达式）**拆分逗号分隔字段，再关联表B获取对应数据，单条SQL即可完成。

具体语句如下：

WITH RECURSIVE split_model AS (
    -- 初始查询：提取第一个模型类型，保留剩余待拆分内容
    SELECT 
        project_name,
        SUBSTRING_INDEX(model_types, ',', 1) AS model_type,
        SUBSTRING(model_types, LENGTH(SUBSTRING_INDEX(model_types, ',', 1)) + 2) AS remaining_types
    FROM table_a
    WHERE model_types IS NOT NULL AND model_types != ''
    UNION ALL
    -- 递归拆分剩余的模型类型，直到剩余内容为空
    SELECT 
        project_name,
        SUBSTRING_INDEX(remaining_types, ',', 1) AS model_type,
        SUBSTRING(remaining_types, LENGTH(SUBSTRING_INDEX(remaining_types, ',', 1)) + 2) AS remaining_types
    FROM split_model
    WHERE remaining_types IS NOT NULL AND remaining_types != ''
)
-- 关联表B匹配模型类型，输出目标结果
SELECT 
    sm.model_type AS Model_types,
    b.labels,
    b.image_types
FROM split_model sm
JOIN table_b b ON sm.model_type = b.model;

补充说明 #

1. 递归CTE的split_model负责拆分字段：初始查询提取第一个元素，递归查询循环处理剩余内容，直到无剩余字符串；
2. 若使用MySQL 5.x版本（不支持CTE），可借助数字辅助表实现拆分，但写法相对繁琐，上述方案仅适用于MySQL 8.0及以上版本。

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