如何用SQL按照配置表规则批量替换目标表字段中的多个指定子串

你描述的逻辑完全可以在SQL中实现，不同数据库有不同的实现方式，以下是常见数据库的可行方案： #

方案1：循环迭代更新（最贴合你写的伪代码逻辑，适配绝大多数数据库） #

MySQL 实现示例： #

-- 定义变量存储待移除的值
DECLARE val_to_remove VARCHAR(255);
-- 定义游标遍历所有需要移除的value
DECLARE cur CURSOR FOR SELECT value FROM table2 WHERE remove = 'true';
-- 遍历结束标记
DECLARE CONTINUE HANDLER FOR NOT FOUND SET @done = 1;

SET @done = 0;
OPEN cur;

read_loop: LOOP
  FETCH cur INTO val_to_remove;
  IF @done = 1 THEN
    LEAVE read_loop;
  END IF;
  -- 执行替换，每遍历一个待删除值就批量更新一次table1
  UPDATE table1 SET value = REPLACE(value, val_to_remove, '');
END LOOP;

CLOSE cur;

SQL Server 实现示例： #

DECLARE @val_to_remove VARCHAR(255);
DECLARE cur CURSOR FOR SELECT value FROM table2 WHERE remove = 'true';

OPEN cur;
FETCH NEXT FROM cur INTO @val_to_remove;

WHILE @@FETCH_STATUS = 0
BEGIN
  UPDATE table1 SET value = REPLACE(value, @val_to_remove, '');
  FETCH NEXT FROM cur INTO @val_to_remove;
END

CLOSE cur;
DEALLOCATE cur;

方案2：非循环的批量处理方案（无需游标，性能更优适合大量待替换项） #

如果你使用支持字符串拆分、聚合函数的数据库（比如PostgreSQL、MySQL 8.0+、SQL Server 2016+），可以用拆分后过滤再拼接的方式一次性完成更新，避免多次扫描table1：

PostgreSQL 示例： #

UPDATE table1 t1
SET value = t.new_value
FROM (
  SELECT 
    t1.name,
    STRING_AGG(CASE WHEN t2.remove = 'true' THEN '' ELSE split_val END, ',' ORDER BY ordinality) AS new_value
  FROM table1 t1,
       UNNEST(STRING_TO_ARRAY(t1.value, ',')) WITH ORDINALITY AS s(split_val, ordinality)
  LEFT JOIN table2 t2 ON s.split_val = t2.value
  GROUP BY t1.name
) t
WHERE t1.name = t.name;

MySQL 8.0+ 示例： #

UPDATE table1 t1
JOIN (
  SELECT 
    t1.name,
    GROUP_CONCAT(IF(t2.remove = 'true', '', s.split_val) ORDER BY s.idx SEPARATOR ',') AS new_value
  FROM table1 t1
  JOIN JSON_TABLE(
    CONCAT('["', REPLACE(t1.value, ',', '","'), '"]'),
    '$[*]' COLUMNS (
      idx FOR ORDINALITY,
      split_val VARCHAR(255) PATH '$'
    )
  ) s
  LEFT JOIN table2 t2 ON s.split_val = t2.value
  GROUP BY t1.name
) t ON t1.name = t.name
SET t1.value = t.new_value;

注意事项 #

• 两种方案都完全保留你需要的空值占位（即逗号不会被移除，只替换目标字符），和你给出的预期结果完全一致
• 如果待替换的value存在包含关系（比如同时要替换a和ab），注意调整游标遍历的顺序，优先替换长字符串即可
• 操作前建议先备份数据，或者先跑SELECT语句验证替换结果符合预期再执行UPDATE

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