PostgreSQL中移除手机号与邮箱提取纯用户ID的优化SQL方案

核心思路 #

先拆分逗号分隔的字符串，通过正则匹配排除含@的邮箱和带+前缀的手机号，仅保留纯数字格式的用户ID，最后重新聚合为逗号分隔的字符串。同时利用数据库特性优化性能，避免拖累复杂主查询。

方法一：拆分过滤后聚合（精准易维护） #

这种方法逻辑清晰，能精准过滤目标内容，适合对数据准确性要求高的场景：

SELECT
    id,
    string_agg(filtered_id, ',' ORDER BY ordinality) AS pure_user_ids
FROM
    your_table,
    regexp_split_to_table(userid, ',') WITH ORDINALITY AS split_ids(filtered_id, pos)
WHERE
    filtered_id !~ '@' -- 排除含@的邮箱
    AND filtered_id !~ '^\+\d+$' -- 排除+开头的手机号
    AND filtered_id ~ '^\d+$' -- 确保仅保留纯数字用户ID
GROUP BY
    id;

• WITH ORDINALITY 用于保留原字符串中ID的顺序，避免聚合后顺序混乱
• 正则匹配规则可根据实际数据格式调整（比如手机号有其他前缀规则，可修改^\+\d+$）

方法二：正则直接替换（性能更优，适合规范数据） #

如果数据格式固定（无其他杂项），可以用两次正则替换直接移除邮箱和手机号，再清理残留的逗号：

SELECT
    id,
    trim(both ',' FROM regexp_replace(
        regexp_replace(userid, '[^,]+@[^,]+', '', 'g'), -- 移除所有邮箱
        '\+\d+', '', 'g' -- 移除所有+开头的手机号
    )) AS pure_user_ids
FROM your_table;

注意：如果存在非数字、非邮箱、非手机号的杂项，这种方法会保留它们，仅适合数据格式规范的场景。

性能优化方案（避免影响主查询） #

如果需要在复杂主查询中频繁使用该逻辑，推荐以下两种优化方式：

1. 封装为Immutable函数

将过滤逻辑封装成不可变函数，PostgreSQL会缓存函数结果，重复调用时无需重复计算：

CREATE OR REPLACE FUNCTION extract_pure_user_ids(userid_str text)
RETURNS text AS $$
BEGIN
    RETURN (
        SELECT string_agg(filtered_id, ',' ORDER BY pos)
        FROM regexp_split_to_table(userid_str, ',') WITH ORDINALITY AS split_ids(filtered_id, pos)
        WHERE filtered_id !~ '@'
          AND filtered_id !~ '^\+\d+$'
          AND filtered_id ~ '^\d+$'
    );
END;
$$ LANGUAGE plpgsql IMMUTABLE;

主查询中直接调用：

SELECT id, extract_pure_user_ids(userid) AS pure_user_ids
FROM your_table;

2. 创建生成列（最优性能）

如果数据更新不频繁，可创建存储型生成列，数据库会自动维护该字段的值，查询时直接读取，完全不影响主查询性能：

ALTER TABLE your_table ADD COLUMN pure_user_ids text GENERATED ALWAYS AS (
    (SELECT string_agg(filtered_id, ',' ORDER BY pos)
     FROM regexp_split_to_table(userid, ',') WITH ORDINALITY AS split_ids(filtered_id, pos)
     WHERE filtered_id !~ '@'
       AND filtered_id !~ '^\+\d+$'
       AND filtered_id ~ '^\d+$')
) STORED;

之后主查询直接使用pure_user_ids字段即可，无需任何计算。

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