MySQL如何按斜杠拆分列内容并生成对应拆分值的多行结果

适用场景 #

针对table1表中QA_CONTACT_NAME字段按/分隔拆分、生成对应QA_EMAIL列值的需求，以下方案可直接产出你期望的结果格式。 #

MySQL 8.0+ 版本实现（推荐） #

利用递归CTE实现动态拆分，不需要提前准备辅助表，自动适配任意拆分段数，查询去重后拆分结果的SQL如下：

WITH RECURSIVE split_temp AS (
    -- 初始化：取所有去重的联系人值，定位第一个分隔符位置
    SELECT
        QA_CONTACT_NAME,
        1 AS start_idx,
        LOCATE('/', QA_CONTACT_NAME) AS split_pos
    FROM (SELECT DISTINCT QA_CONTACT_NAME FROM table1) t
    UNION ALL
    -- 递归：逐次向后定位下一个分隔符，直到无分隔符为止
    SELECT
        QA_CONTACT_NAME,
        split_pos + 1 AS start_idx,
        LOCATE('/', QA_CONTACT_NAME, split_pos + 1) AS split_pos
    FROM split_temp
    WHERE split_pos > 0
)
SELECT
    QA_CONTACT_NAME,
    -- 按当前定位位置截取对应段作为QA_EMAIL
    CASE
        WHEN split_pos = 0 THEN SUBSTRING(QA_CONTACT_NAME, start_idx)
        ELSE SUBSTRING(QA_CONTACT_NAME, start_idx, split_pos - start_idx)
    END AS QA_EMAIL
FROM split_temp
ORDER BY QA_CONTACT_NAME, start_idx;

逻辑说明 #

• 无/的字段值不会进入递归逻辑，只会返回1行，直接把原值作为QA_EMAIL
• 含/的字段值会逐次定位分隔符位置，每找到一个分隔符就生成一行对应拆分后的值
• 执行结果和你给出的期望输出完全一致。

落地到表操作 #

如果需要把拆分结果持久化到表中，可按需求选择对应操作：

1. 给原表新增QA_EMAIL列

ALTER TABLE table1 ADD COLUMN QA_EMAIL VARCHAR(255) COMMENT '拆分后QA联系人邮箱';

2. 直接生成拆分后的独立结果表

CREATE TABLE table1_qa_email_mapping AS
WITH RECURSIVE split_temp AS (
    SELECT
        QA_CONTACT_NAME,
        1 AS start_idx,
        LOCATE('/', QA_CONTACT_NAME) AS split_pos
    FROM table1
    UNION ALL
    SELECT
        QA_CONTACT_NAME,
        split_pos + 1 AS start_idx,
        LOCATE('/', QA_CONTACT_NAME, split_pos + 1) AS split_pos
    FROM split_temp
    WHERE split_pos > 0
)
SELECT
    QA_CONTACT_NAME,
    CASE
        WHEN split_pos = 0 THEN SUBSTRING(QA_CONTACT_NAME, start_idx)
        ELSE SUBSTRING(QA_CONTACT_NAME, start_idx, split_pos - start_idx)
    END AS QA_EMAIL
FROM split_temp;

MySQL 5.x 版本适配方案 #

5.x版本不支持递归CTE，可通过关联连续序号表的方式实现拆分，只要序号数量覆盖单个字段最多拆分的段数即可：

SELECT
    t.QA_CONTACT_NAME,
    SUBSTRING_INDEX(SUBSTRING_INDEX(t.QA_CONTACT_NAME, '/', n.num), '/', -1) AS QA_EMAIL
FROM (SELECT DISTINCT QA_CONTACT_NAME FROM table1) t
JOIN (
    -- 按需补充序号，最多支持拆成5段，需要更多就继续加UNION ALL
    SELECT 1 AS num UNION ALL SELECT 2 UNION ALL SELECT 3
    UNION ALL SELECT 4 UNION ALL SELECT 5
) n
ON n.num <= LENGTH(t.QA_CONTACT_NAME) - LENGTH(REPLACE(t.QA_CONTACT_NAME, '/', '')) + 1
ORDER BY t.QA_CONTACT_NAME, n.num;

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