如何在PostgreSQL中基于另一张表替换列中的子字符串

问题背景 #

在PostgreSQL中，table1的自由文本评论列需脱敏table2中存储的所有姓名，现有递归CTE仅能替换部分姓名，多不同姓名共存时无法全部替换。table1全量约80万行，table2约1.4万行，全量执行30分钟可接受，日常增量约千行。

测试表结构 #

CREATE TABLE table1 (
    id INT PRIMARY KEY,
    comment_text TEXT NOT NULL
);

CREATE TABLE table2 (
    original_name TEXT PRIMARY KEY,
    replace_with TEXT NOT NULL -- 固定字符串或自定义替换值
);

现有递归CTE的问题 #

递归逻辑通常仅单次替换单个姓名，未遍历所有待替换条目，导致多姓名场景下漏替换。以下是两种可行的修复/替代方案：

方案1：自定义PL/pgSQL函数（推荐） #

通过循环遍历table2所有姓名，对每条评论执行全局替换，确保所有匹配项都被处理。

函数实现 #

CREATE OR REPLACE FUNCTION desensitize_names(input_text TEXT) RETURNS TEXT AS $$
DECLARE
    processed_text TEXT := input_text;
    name_rec RECORD;
BEGIN
    -- 遍历所有待替换姓名，逐个全局替换（\y 匹配单词边界，避免部分匹配）
    FOR name_rec IN SELECT original_name, replace_with FROM table2 LOOP
        processed_text := regexp_replace(processed_text, '\y' || name_rec.original_name || '\y', name_rec.replace_with, 'g');
    END LOOP;
    RETURN processed_text;
END;
$$ LANGUAGE plpgsql STABLE;

全量更新 #

-- 新增脱敏列（建议保留原数据，避免直接修改comment_text）
ALTER TABLE table1 ADD COLUMN IF NOT EXISTS desensitized_comment TEXT;

-- 全量更新脱敏内容
UPDATE table1
SET desensitized_comment = desensitize_names(comment_text);

增量更新 #

针对日常千行增量，新增updated_at字段实现增量处理：

-- 新增更新时间字段
ALTER TABLE table1 ADD COLUMN IF NOT EXISTS updated_at TIMESTAMP DEFAULT NOW();

-- 仅处理最近未脱敏的行
UPDATE table1
SET desensitized_comment = desensitize_names(comment_text),
    updated_at = NOW()
WHERE desensitized_comment IS NULL OR updated_at < NOW() - INTERVAL '1 hour'; -- 按需调整时间范围

方案2：修复递归CTE逻辑 #

通过递归遍历所有待替换姓名，逐次完成替换，确保所有姓名都被处理。

修复后的递归CTE #

WITH RECURSIVE name_replacement AS (
    -- 初始数据集：原始评论+待替换姓名的索引
    SELECT
        t1.id,
        t1.comment_text AS original_text,
        t1.comment_text AS current_text,
        1 AS name_index,
        (SELECT COUNT(*) FROM table2) AS total_names
    FROM table1 t1
    UNION ALL
    -- 递归步骤：按索引依次替换每个姓名
    SELECT
        nr.id,
        nr.original_text,
        regexp_replace(nr.current_text, '\y' || t2.original_name || '\y', t2.replace_with, 'g'),
        nr.name_index + 1,
        nr.total_names
    FROM name_replacement nr
    -- 通过索引关联table2的姓名，确保遍历所有条目
    JOIN table2 t2 ON t2.original_name = (SELECT original_name FROM table2 ORDER BY original_name OFFSET nr.name_index - 1 LIMIT 1)
    WHERE nr.name_index <= nr.total_names
)
-- 取最后一次递归的结果（所有姓名替换完成）
UPDATE table1 t1
SET desensitized_comment = nr.current_text
FROM name_replacement nr
WHERE t1.id = nr.id AND nr.name_index = nr.total_names;

性能优化 #

1. 索引优化：给table2.original_name建索引，加速遍历匹配：

CREATE INDEX idx_table2_original_name ON table2(original_name);

2. 去重处理：如果table2存在重复姓名，先去重避免重复替换：

CREATE TABLE table2_clean AS SELECT DISTINCT original_name, replace_with FROM table2;
DROP TABLE table2;
ALTER TABLE table2_clean RENAME TO table2;

3. 批量提交：全量更新时可分批处理，避免长事务：

-- 每1000行提交一次
DO $$
DECLARE
    batch_size INT := 1000;
    total_rows INT;
    current_offset INT := 0;
BEGIN
    SELECT COUNT(*) INTO total_rows FROM table1 WHERE desensitized_comment IS NULL;
    WHILE current_offset < total_rows LOOP
        UPDATE table1
        SET desensitized_comment = desensitize_names(comment_text)
        WHERE id IN (SELECT id FROM table1 WHERE desensitized_comment IS NULL LIMIT batch_size OFFSET current_offset);
        current_offset := current_offset + batch_size;
        COMMIT;
    END LOOP;
END $$;

测试验证 #

测试数据 #

INSERT INTO table1 VALUES
(1, '张三和李四一起参加了会议，王五也来了'),
(2, '赵六负责对接，张三跟进后续');

INSERT INTO table2 VALUES
('张三', '[姓名1]'),
('李四', '[姓名2]'),
('王五', '[姓名3]'),
('赵六', '[姓名4]');

预期结果 #

desensitized_comment列内容：

• ID=1: [姓名1]和[姓名2]一起参加了会议，[姓名3]也来了
• ID=2: [姓名4]负责对接，[姓名1]跟进后续

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