PostgreSQL实现两列逗号分隔字符串相减生成新列方法

你之前用的REPLACE(col1, col2, '')是连续子串匹配逻辑，只有col2内的元素顺序、相邻关系和col1中对应片段完全一致时才能替换成功。这种写法本质是把逗号分隔的多值字符串当成了普通文本处理，没有识别每个独立的元素，自然无法处理col2元素顺序打乱的场景。

要可靠实现需求，核心是把两个字段的逗号分隔字符串拆解为独立元素，做集合差运算（保留col1中存在、col2中不存在的元素，且严格保留元素在col1中的原有排列顺序），再把筛选后的元素重新拼接为逗号分隔字符串即可。

MySQL 8.0+ 可直接执行方案 #

WITH RECURSIVE
-- 拆分col1为带位置序号的独立元素，保留原有排列顺序
col1_split AS (
    SELECT 
        id,
        1 AS elem_pos,
        SUBSTRING_INDEX(SUBSTRING_INDEX(col1, ',', 1), ',', -1) AS elem
    FROM myTable
    UNION ALL
    SELECT 
        id,
        elem_pos + 1,
        SUBSTRING_INDEX(SUBSTRING_INDEX(col1, ',', elem_pos + 1), ',', -1)
    FROM col1_split
    WHERE elem_pos < LENGTH(col1) - LENGTH(REPLACE(col1, ',', '')) + 1
),
-- 拆分col2为独立元素集合
col2_split AS (
    SELECT 
        id,
        1 AS elem_pos,
        SUBSTRING_INDEX(SUBSTRING_INDEX(col2, ',', 1), ',', -1) AS elem
    FROM myTable
    UNION ALL
    SELECT 
        id,
        elem_pos + 1,
        SUBSTRING_INDEX(SUBSTRING_INDEX(col2, ',', elem_pos + 1), ',', -1)
    FROM col2_split
    WHERE elem_pos < LENGTH(col2) - LENGTH(REPLACE(col2, ',', '')) + 1
)
-- 筛选元素后拼接，更新col3
UPDATE myTable t
JOIN (
    SELECT 
        cs1.id,
        GROUP_CONCAT(cs1.elem ORDER BY cs1.elem_pos SEPARATOR ',') AS col3_val
    FROM col1_split cs1
    LEFT JOIN col2_split cs2 
        ON cs1.id = cs2.id 
        AND cs1.elem = cs2.elem
    WHERE cs2.elem IS NULL
    GROUP BY cs1.id
) res ON t.id = res.id
SET t.col3 = res.col3_val;

注：代码中id字段请替换为你myTable表的实际主键/唯一行标识字段，用于准确定位每一行数据。

PostgreSQL 可直接执行方案 #

PG原生支持数组和UNNEST拆分数值的功能，逻辑更简洁，且能严格保留col1元素的原有顺序：

UPDATE myTable
SET col3 = array_to_string(
    ARRAY(
        SELECT elem
        FROM unnest(string_to_array(col1, ',')) WITH ORDINALITY AS c1(elem, pos)
        WHERE NOT EXISTS (
            SELECT 1 
            FROM unnest(string_to_array(col2, ',')) c2(elem)
            WHERE c1.elem = c2.elem
        )
        ORDER BY pos
    ),
    ','
);

• 执行UPDATE操作前，建议先把内层计算col3值的子查询单独作为SELECT语句执行，核对结果完全符合预期后再执行更新，防止误改数据。
• 如果业务中这类多值字段的过滤、计算需求很多，长期不建议用逗号分隔的方式存储多值，最好拆成关联子表按行存储单个元素，后续计算的效率、可靠性都会大幅提升。
• 如果你用的是SQL Server、Oracle、ClickHouse等其他数据库，核心实现逻辑完全一致，只需要把拆分字符串、拼接字符串的函数替换为对应数据库的原生函数即可。

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