请求将MySQL表逗号分隔值拆分为多行并生成指定关联结果

嘿，这问题我熟！要把products表里用逗号串起来的分类ID拆成单独行，再关联categories表拿到对应的分类名称，我给你两种实用的方法，适配不同版本的MySQL：

方法一：递归CTE（MySQL 8.0及以上可用） #

如果你的MySQL是8.0或更新版本，用递归公共表表达式（CTE）是最优雅的方式，代码如下：

WITH RECURSIVE split_categories AS (
    -- 先取每个商品的第一个分类ID，剩下的ID留待递归拆分
    SELECT 
        product_id,
        SUBSTRING_INDEX(categorie_ids, ',', 1) AS category_id,
        SUBSTRING(categorie_ids, LENGTH(SUBSTRING_INDEX(categorie_ids, ',', 1)) + 2) AS remaining_ids
    FROM products
    WHERE categorie_ids IS NOT NULL AND categorie_ids != ''
    
    UNION ALL
    
    -- 递归处理剩下的ID，直到拆完为止
    SELECT 
        product_id,
        SUBSTRING_INDEX(remaining_ids, ',', 1) AS category_id,
        SUBSTRING(remaining_ids, LENGTH(SUBSTRING_INDEX(remaining_ids, ',', 1)) + 2) AS remaining_ids
    FROM split_categories
    WHERE remaining_ids IS NOT NULL AND remaining_ids != ''
)
-- 最后关联分类表，拿到分类名称
SELECT 
    sc.product_id,
    c.catname AS categories
FROM split_categories sc
JOIN categories c ON sc.category_id = c.c_category_id
ORDER BY sc.product_id, c.c_category_id;

这个逻辑很清晰：先把每个商品的分类ID拆成“第一个ID+剩余ID”的形式，然后递归把剩余ID继续拆分，直到所有ID都变成单独的行，最后和categories表关联就能得到你要的结果。

方法二：数字辅助表（兼容MySQL 5.x等旧版本） #

要是你用的是旧版本MySQL，不支持递归CTE，那就用数字辅助表来搞定：

-- 先建个临时数字表，生成1到10的数字（数量够覆盖你最多的分类ID个数就行）
CREATE TEMPORARY TABLE numbers (n INT);
INSERT INTO numbers VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);

-- 开始拆分并关联分类表
SELECT 
    p.product_id,
    c.catname AS categories
FROM products p
JOIN numbers n ON n.n <= LENGTH(p.categorie_ids) - LENGTH(REPLACE(p.categorie_ids, ',', '')) + 1
JOIN categories c ON SUBSTRING_INDEX(SUBSTRING_INDEX(p.categorie_ids, ',', n.n), ',', -1) = c.c_category_id
ORDER BY p.product_id, c.c_category_id;

这里的思路是：用数字表给每个分类ID的位置编号，通过两次SUBSTRING_INDEX截取对应位置的ID，再和分类表关联。数字表的行数只要比你商品最多的分类数多就行。

小提醒 #

虽然上面的方法能解决问题，但我还是得说一句：尽量不要用逗号分隔的方式存多值！最好改成多对多的关联结构——建一个product_category中间表，每行存一个product_id和对应的category_id，这样查询、维护和性能都会好很多，后续扩展也方便。

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