如何在MySQL中将tblregs表的逗号分隔数组字段LinkedIDs拆分为多行

方案1：MySQL 8.0+ 递归CTE实现（无需依赖额外辅助表） #

该方案无需提前创建辅助表，自动适配1个或多个逗号分隔值的拆分需求：

WITH RECURSIVE split_cte AS (
    SELECT 
        LinkedIDs AS original_ids,
        SUBSTRING_INDEX(LinkedIDs, ',', 1) AS LinkedIDs,
        Quan,
        1 AS pos
    FROM tblregs
    UNION ALL
    SELECT 
        original_ids,
        SUBSTRING_INDEX(SUBSTRING_INDEX(original_ids, ',', pos + 1), ',', -1) AS LinkedIDs,
        Quan,
        pos + 1 AS pos
    FROM split_cte
    WHERE pos < LENGTH(original_ids) - LENGTH(REPLACE(original_ids, ',', '')) + 1
)
SELECT LinkedIDs, Quan 
FROM split_cte
ORDER BY Quan DESC, LinkedIDs;

如果单个LinkedIDs字段内的分隔值超过1000个，可执行SET cte_max_recursion_depth = 10000;临时调整递归深度上限。

方案2：沿用原有numbers辅助表方案 #

你原有SQL逻辑正确，仅需修正字段别名，同时确保numbers表的n值覆盖单条LinkedIDs的最大分隔值数量即可，修正后代码如下：

SELECT
  SUBSTRING_INDEX(SUBSTRING_INDEX(t.LinkedIDs, ',', n.n), ',', -1) AS LinkedIDs,
  t.Quan
FROM
  numbers n
INNER JOIN tblregs t
  ON CHAR_LENGTH(t.LinkedIDs) - CHAR_LENGTH(REPLACE(t.LinkedIDs, ',', '')) >= n.n - 1
ORDER BY t.Quan DESC, n.n;

如果还未创建numbers表，可执行以下语句生成连续数字表：

-- 创建numbers表
CREATE TABLE numbers (n INT PRIMARY KEY);
-- 插入1-100的连续整数，可根据实际需要的最大分隔值数量调整范围
INSERT INTO numbers (n)
SELECT a.N + b.N * 10 + 1
FROM (SELECT 0 AS N 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) a,
     (SELECT 0 AS N 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) b
ORDER BY n;

内容的提问来源于stack exchange，提问作者M.J