如何处理含不定数量排除项的SQL Update操作？

问题背景 #

我正在更新一个大型SQL数据库，需要处理exclude_fkidval列中以逗号分隔的列表所指定的动态排除项。

初始表结构 #

当前查询 #

UPDATE tab
SET bal = (SELECT credit - debit
           FROM f_tab
           WHERE f_tab.fkval LIKE fkidval || '%' 
             AND f_tab.fkval <> exclude_fkidval)

现在exclude_fkidval中的排除项可能包含多个值（以逗号分隔，部分值带前置空格），需要调整SQL以处理动态数量的排除项，更新后的表结构如下：

更新后表结构（含多排除项） #

高效解决方案 #

针对大型数据集，核心思路是避免逐行字符串拆分的性能损耗，同时确保排除逻辑准确。以下分主流数据库给出实现方案：

1. PostgreSQL #

利用string_to_array拆分逗号分隔字符串，结合<> ALL判断，同时先去除字符串中的空格：

UPDATE tab
SET bal = (
    SELECT credit - debit
    FROM f_tab
    WHERE f_tab.fkval LIKE tab.fkidval || '%'
      AND f_tab.fkval <> ALL(string_to_array(replace(tab.exclude_fkidval, ' ', ''), ','))
);

若exclude_fkidval可能为空，需额外处理避免空数组报错：

UPDATE tab
SET bal = (
    SELECT credit - debit
    FROM f_tab
    WHERE f_tab.fkval LIKE tab.fkidval || '%'
      AND (
          tab.exclude_fkidval IS NULL 
          OR f_tab.fkval <> ALL(string_to_array(replace(tab.exclude_fkidval, ' ', ''), ','))
      )
);

2. MySQL/MariaDB #

使用FIND_IN_SET函数，注意先去除字符串中的空格（该函数不忽略逗号后的空格）：

UPDATE tab
SET bal = (
    SELECT credit - debit
    FROM f_tab
    WHERE f_tab.fkval LIKE CONCAT(tab.fkidval, '%')
      AND FIND_IN_SET(f_tab.fkval, REPLACE(tab.exclude_fkidval, ' ', '')) = 0
);

性能优化：为f_tab.fkval创建前缀索引，加速LIKE 'xxx%'的匹配逻辑。

3. SQL Server #

利用STRING_SPLIT拆分字符串，结合NOT EXISTS关联判断排除项：

UPDATE tab
SET bal = (
    SELECT credit - debit
    FROM f_tab
    WHERE f_tab.fkval LIKE tab.fkidval + '%'
      AND NOT EXISTS (
          SELECT 1
          FROM STRING_SPLIT(REPLACE(tab.exclude_fkidval, ' ', ''), ',') AS excluded
          WHERE excluded.value = f_tab.fkval
      )
);

若使用SQL Server 2016之前版本，可自定义字符串拆分函数，但性能会有所下降，建议优先升级版本或使用CLR函数。

性能优化补充建议 #

• 索引优化：为f_tab.fkval创建前缀索引（如CREATE INDEX idx_fkval_prefix ON f_tab (fkval(10));），大幅提升前缀匹配的查询速度。
• 批量更新：针对超大型数据集，按id或其他字段分批次更新，避免长时间锁表影响业务。
• 数据结构重构：长期来看，建议将exclude_fkidval拆分为独立的关联表（如tab_exclusions，包含tab_id和exclude_fkidval字段），彻底消除逗号分隔的存储方式，这是最利于性能维护的方案。

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