正则表达式在 PostgreSQL 里平时大家用得少可真到要用的时候全是问题。我见过不少人把在 Python 或 Perl 里跑得好好的正则原封不动搬到 PostgreSQL 里就报错或者匹配结果完全对不上也见过有人在一张存了几十万条日志的表上直接跑正则筛选一条 SQL 把数据库拖到半死。PostgreSQL 内置了非常完整的正则支持从匹配、提取、替换到拆分都有对应函数Python 里能做的操作这里基本都能做。这篇东西不打算讲教科书式的语法清单我把这几年在 PostgreSQL 里用正则处理线上数据时踩过的坑、沉淀下来的写法按场景整理出来。内容偏实战适合正在用 PostgreSQL 处理日志、清洗文本、做报表字段提取的工程师也适合刚装上 PostgreSQL 16 想系统了解正则能力的同学。版本方面我下面所有 SQL 都以 PostgreSQL 16 为准部分新函数 16 之前没有我会单独标注。1. 先搞清楚 PostgreSQL 里的“正则”到底有几种姿势PostgreSQL 给我的第一感觉是正则能力强但入口太多。同样是模糊匹配你可以写LIKE、写SIMILAR TO、写~还可以写regexp_like。新手很容易满脑子问号这些到底有什么区别我平时应该用哪个1.1 三种匹配工具定位完全不同先说LIKE。它其实不算是正则表达式只是简单模式匹配只有两个通配符%匹配任意长度字符串_匹配单个字符。好处是简单、直观、快坏处是表达力弱比如想匹配“张”或“李”开头的人名LIKE就得写两个条件拼OR。SIMILAR TO是 SQL 标准里的折中方案语法上混合了LIKE的通配符和正则的分支、字符类。比如SIMILAR TO (张|李)%能匹配张或李开头。但这个语法有点“四不像”用的人很少我在实际项目中基本没见过谁靠它做主逻辑。真正强大的是第三类POSIX 正则表达式用~、~*这类操作符以及配套函数实现。这也是 PostgreSQL 区别于 MySQL 的一大优势。比如想匹配“张”或“李”开头并且名字是两个字直接写SELECT * FROM users WHERE name ~ ^[张李].;从维护角度讲团队里只要有人熟悉常见正则写法~一段式就能解决复杂匹配不需要东拼西凑多个LIKE。工具类型典型写法表达力适用场景LIKEname LIKE 张%只有 % 和 _前缀、后缀、包含等简单查询SIMILAR TOname SIMILAR TO (张|李)%混合通配符与正则需要 SQL 标准兼容的存量系统POSIX 操作符name ~ ^[张李]完整正则能力复杂匹配、提取、替换、拆分1.2 为什么我默认用 POSIX 操作符理由很简单PostgreSQL 内置的正则引擎是高级正则表达式ARE它支持分支、分组、后向引用、非贪婪匹配、字符类这些现代正则基本功已经覆盖了绝大多数业务场景。它和常见编程语言里的正则习惯非常接近迁移成本低。比如你有一条日志想找出所有“ERROR 开头的编号”Python 或者 Perl 里可能是ERROR ([A-Z0-9_])在 PostgreSQL 里几乎不用改SELECT substring(log_line from ERROR ([A-Z0-9_])) AS error_code FROM app_logs;我见过很多项目因为不知道 PostgreSQL 支持完整正则硬生生用一串LIKE加OR实现SQL 又臭又长还容易漏匹配。规范做法就是简单模糊查询继续用LIKE复杂文本处理一律走 POSIX 正则。 如果你也像很多团队一样用 Docker 拉一个 PostgreSQL 实例做本地开发下载哪个版本其实不用纠结我建议直接上 16 或更新版本旧库迁移也就一次pg_dump的事换来的是更完整的正则函数族。2. 匹配判断用 ~、~*、!~ 批量清洗数据正则最基础的使用场景就是判断某列是否符合某种格式比如手机号、邮箱、身份证号。在 PostgreSQL 里这类判断不需要专门写函数直接用操作符就行。2.1 四类操作符怎么选PostgreSQL 提供了四个正则操作符操作符含义示例~匹配区分大小写email ~ test.com$~*匹配不区分大小写name ~* ^zhang!~不匹配区分大小写status !~ ^completed!~*不匹配不区分大小写city !~* ^bei[jq]ing注意~和LIKE的区别~是完整的正则匹配^和$这种锚点才起作用。LIKE里的%在~里可不会自动变成“任意长度”的意思正则里写法是.*。举个例子我想找出所有包含连续六位以上数字的日志行SELECT log_line FROM app_logs WHERE log_line ~ [0-9]{6,};再比如从用户表里筛出邮箱明显不合规的记录SELECT user_id, email FROM users WHERE email !~ ^[A-Za-z0-9._%-][A-Za-z0-9.-]\.[A-Za-z]{2,}$;这条语句能直接当作数据质量检查脚本用跑一遍把不合格的邮箱列表拉出来再交给业务方核对。2.2 现实场景中的校验写法实际工作中我常用“正则 开关字段”的方式做标记而不是频繁更新主数据。比如在订单表里售后备注有很多乱填的内容想标记出那些可能包含电话号码的订单UPDATE orders SET needs_review true WHERE remark ~ 1[3-9][0-9]{9} AND needs_review false;加上AND needs_review false的目的是避免每次跑重复更新。正则匹配本身消耗不小能通过普通索引条件缩小范围就先缩小范围等会讲性能时细说。这里有个小技巧如果你只是想判断“是否存在匹配”用~操作符就够了它返回布尔值。不需要把regexp_matches拉出来那是提取场景才用的。很多人一上来就用regexp_matches结果发现返回的是数组还得取值白白增加复杂度。3. 提取内容regexp_matches 和 regexp_substr 的正确打开方式匹配是“判断有没有”提取是“把想要的部分拿出来”。PostgreSQL 里提取相关的函数有substring、regexp_matches、regexp_substr。用的场景不同踩的坑也不一样。3.1 踩过的坑regexp_matches 的数组返回regexp_matches是 PostgreSQL 一直就有的函数但它有个非常容易坑人的地方默认情况下如果没有加g标志它只返回第一个匹配结果如果加了g它返回所有匹配。但不管哪种情况它返回的类型都是text[]也就是一个数组。看个例子我想把product123 price456里所有数字都提出来SELECT regexp_matches(product123 price456, [0-9], g);输出是两行每行一个数组{123} {456}注意不是直接输出123和456而是{123}这种数组形式。如果你想要行内单个值要用手去取数组第一项SELECT (regexp_matches(product123 price456, [0-9], g))[1];这在处理日志里的错误码时很实用。例如SELECT log_id, (regexp_matches(log_line, ERROR: ([A-Z0-9_]), g))[1] AS error_code FROM app_logs WHERE log_line ~ ERROR:;用g标志时一行日志可能出现多个错误码这时会输出多行。如果你的业务逻辑只需要第一个就别加g然后在外层加LIMIT 1或者用array_agg合并。3.2 PG16 新函数让提取更直观从 PostgreSQL 16 开始官方加入了一批更接近 Oracle 风格的正则函数包括regexp_like、regexp_count、regexp_instr、regexp_substr。这几个函数对提取场景非常友好最大的变化是返回值不再那么别扭。比如提取日志里第一个错误码旧写法是substringPG16 可以直接用regexp_substrSELECT log_id, regexp_substr(log_line, ERROR: ([A-Z0-9_])) AS error_code FROM app_logs;regexp_substr的重载参数还支持指定从第几个字符开始搜索、提取第几次出现的匹配。比如想提取第二次出现的数字SELECT regexp_substr(温度25℃湿度16℃, [0-9], 1, 2);第二个参数是模式第三个是起始位置第四个是第几次出现。这个功能以前在 PostgreSQL 里写起来很痛苦现在一句话搞定强烈建议还在用老版本的朋友认真评估升级。配合regexp_count还能快速统计一段文本里某个模式出现的次数SELECT description, regexp_count(description, [0-9]{4}-[0-9]{2}-[0-9]{2}) AS date_count FROM product_specs;这个函数对做文本质量统计特别有用以前要用regexp_matches加COUNT还得处理数组现在直接出数。4. 替换与重排regexp_replace 的高阶用法如果说提取是“读”那替换就是“写”。regexp_replace是 PostgreSQL 里最常用的文本改写函数比各种手动concat拼接字符串要优雅得多。4.1 反向引用的威力regexp_replace的强大之处在于替换串里可以引用匹配到的分组。比如把日期格式从2024-03-15改成15/03/2024SELECT regexp_replace( 2024-03-15, ([0-9]{4})-([0-9]{2})-([0-9]{2}), \3/\2/\1 );这里\1、\2、\3分别对应模式里的三个括号分组。输出是15/03/2024。这种写法在数据迁移、日志格式统一时特别常见。再举个例子手机号脱敏。假设日志里有完整手机号13812345678想只保留前三位和后四位SELECT regexp_replace( 13812345678, (1[3-9][0-9])([0-9]{4})([0-9]{4}), \1****\3 );用分组把中间四位单独抓出来替换串里不引用它换成星号实现比substring拼字符串更直观。如果想把一段文本里所有匹配都替换掉记得加g标志。不加g的话默认只替换第一个匹配这个新手最容易踩-- 只替换第一个数字 SELECT regexp_replace(a1b2c3, [0-9], #); -- 替换所有数字 SELECT regexp_replace(a1b2c3, [0-9], #, g);第一条返回a#b2c3第二条返回a#b#c#。一个是坑一个是需求别用混了。4.2 用正则做条件替换regexp_replace不只能改格式还能结合CASE WHEN做业务规则重写。比如把一批脏文本里的地址后缀统一成标准说法SELECT raw_address, CASE WHEN raw_address ~ 省$ THEN regexp_replace(raw_address, 省$, 省) WHEN raw_address ~ 市$ THEN raw_address WHEN raw_address ~ 自治州$ THEN regexp_replace(raw_address, 自治州$, 州) ELSE raw_address END AS normalized_address FROM customer_addresses;如果把“自治州”直接改成“州”省去括号听起来有点粗暴但实际项目中订单地区归类经常就是这样“暴力归一”的。正则在这里的价值是一个模式覆盖多种书写变体不用写十几个LIKE条件。我还常用regexp_replace做连续空白的压缩处理从网页或 Word 里复制过来的文本时特别有效SELECT regexp_replace(content, [[:space:]], , g) FROM articles;这里用[[:space:]]而不是\s是因为 POSIX 字符类在 PostgreSQL 里更稳后面讲转义时细说。5. 拆分与定位regexp_split 系列的正确姿势有些场景不是“从一段文本里提取内容”而是“把一段文本按多个分隔符切开”。如果只用split_part这种函数遇到不规整的分隔符会非常痛苦。PostgreSQL 的regexp_split_to_table和regexp_split_to_array就是干这个的。5.1 处理脏分隔符先说最常见的需求一个字段里存了多个标签分隔符有时是逗号有时是分号有时是全角逗号甚至混着空格。普通split_part只能按单个固定分隔符切根本无能为力。正则可以这样写SELECT regexp_split_to_table(前端, 后端中台 运维, [,;\\s ]);正则模式里把半角逗号、分号、全角分号、空格、全角空格全部放进字符类一次切干净。输出是四行前端 后端 中台 运维如果想把结果直接变成数组存到text[]列里用regexp_split_to_arraySELECT regexp_split_to_array(a,b;c, [,;]);输出{a,b,c}这里有个细节一定要记住当正则匹配到空字符串时拆分结果可能会带上空字符串。比如regexp_split_to_table(abc, )会把字符串按字符拆开但某些模式下可能多出空行。如果业务上不允许空值建议外面包一层WHERE过滤SELECT trim(tag) AS tag FROM items, LATERAL regexp_split_to_table(tags, [,;]) AS tag WHERE trim(tag) ;LATERAL在 PostgreSQL 里很常用相当于对每一行执行一次拆分再把结果展开。这个写法比string_agg拼来拼去干净得多。5.2 定位第 N 个匹配位置另一个“让人抓狂”的需求是我想知道某个正则模式第一次或第二次出现在字段的哪个位置。以前版本里strpos不支持正则我只能绕着写。从 PostgreSQL 16 开始regexp_instr直接解决SELECT log_line, regexp_instr(log_line, [0-9], 1, 2) AS second_num_pos FROM app_logs;第三个参数1表示从第一个字符开始第四个参数2表示第几次出现。如果找不到返回 0。在旧版本里我一般这样模拟先取出第 N 次匹配的片段再用position定位。比如第 2 个数字的位置可以用SELECT strpos( log_line, (regexp_matches(log_line, [0-9], g))[2]::text ) AS second_num_pos FROM app_logs;注意regexp_matches返回数组取[2]表示第二个匹配到的数字。这种写法能对付大多数情况但还是不如 PG16 的regexp_instr直观。所以“现在到底下载哪个版本、要不要升级”这类问题我个人的答案是没有太强的历史包袱就升正则函数族是真的好用。6. 性能优化让正则查询不那么慢正则虽好性能问题不能忽视。我见过挺多人写完一个看似优雅的正则 SQL一执行就发现全表扫描数据量一上来直接悲剧。正则本身不是不能用而是要用得有策略。6.1 正则为什么没法直接走 B-tree 索引B-tree 索引适合等值、范围、前缀匹配这类查询但正则的语义是“从字符串任意位置做模式匹配”数据库无法直接把col ~ abc.*def转换成 B-tree 的范围扫描。所以大表上频繁跑复杂正则代价确实高。最简单的优化思路是先用普通条件把数据量切小再在上面做正则过滤。比如查最近一天日志里的错误码SELECT * FROM app_logs WHERE created_at now() - interval 1 day AND log_line ~ ERROR [A-Z0-9_];这样created_at能走普通索引正则只在小集合里跑速度会快好几个数量级。这条建议听着很基础但我见过太多人忽略一上来就是把整个历史表扫一遍。还有一个策略是给高频查询的字段建 trigram 索引。PostgreSQL 自带的pg_trgm扩展可以加速LIKE和正则匹配方式是CREATE EXTENSION IF NOT EXISTS pg_trgm; CREATE INDEX idx_app_logs_line_trgm ON app_logs USING gin (log_line gin_trgm_ops);这种索引的核心思想是把字符串拆成连续的三字符片段用 GIN 索引快速定位可能匹配的行。需要注意不是所有正则都能走 trigram 索引模式里至少要包含足够多的普通字符^、$这类锚点或纯数字字符集的匹配可能无法利用索引。所以建完之后一定要用EXPLAIN ANALYZE看执行计划别想当然认为建了索引就一定用得上。6.2 表达式索引、生成列和分批更新另一种策略是把正则计算的结果保存下来再用普通索引加速。例如业务上经常按“去掉分隔符后的手机号”查用户SELECT * FROM customers WHERE regexp_replace(phone, [^0-9], , g) 13812345678;如果你在 PostgreSQL 12 及以上版本可以加一个生成列把手机号规范化的值直接存下来ALTER TABLE customers ADD COLUMN phone_digits text GENERATED ALWAYS AS (regexp_replace(phone, [^0-9], , g)) STORED; CREATE INDEX idx_customers_phone_digits ON customers(phone_digits);之后查询直接查phone_digits就好。这个思路本质是“把复杂消耗前置”写入时算好查询时走普通索引。同理如果你每次都要从日志里抠错误码那就应该在写入或导入时把它拆成独立列而不是每次查询都跑一遍正则提取。对高频查询路径来说宁可多存一列也不要牺牲响应速度。最后说一个非常实在的教训在大表上做UPDATE ... SET ... regexp_replace(...)这种批量改写一定要分批。比如一张千万级表一次性更新会把表锁很久还可能把连接池打满。我常用的做法是UPDATE big_table SET content regexp_replace(content, [[:space:]], , g) WHERE id IN ( SELECT id FROM big_table WHERE content ~ [[:space:]]{2,} ORDER BY id LIMIT 10000 );循环执行直到UPDATE返回 0 行为止。这样单批影响行数可控锁时间短对线上影响小。正则替换看着很酷但大规模执行前一定要想清楚“这锅饭是闷一大锅还是一锅一锅炒”。7. 常见问题与排查技巧实录最后把我在实际使用中遇到的高频问题集中梳理一下基本每个都让人头疼过。7.1 转义与反斜杠PostgreSQL 的字符串转义规则改过好几次现在默认standard_conforming_strings是开启的意思是普通字符串里的反斜杠不会再被吃掉。对正则来说这带来一个常见的混乱。比如我想匹配一个数字模式是\d-- 这种写法在 16 里能工作 SELECT abc123 ~ \d; -- 如果你用 E 前缀双反斜杠是给正则引擎的 SELECT abc123 ~ E\\d;两种写法殊途同归都是正则引擎收到两个字符\d。但如果你写成\\d普通字符串会把两个反斜杠原样传给正则引擎正则引擎看到的可能是“转义反斜杠 字母 d”结果匹配的不是数字。这类问题排查起来特别隐蔽看起来明明是同一个模式结果就是没匹配上。我的经验是项目里统一一种写法推荐用 E 前缀WHERE phone ~ E\\d{11};或者更稳一点干脆用 POSIX 字符类彻底避开反斜杠WHERE phone ~ [0-9]{11};后者虽然写得长一点但不会因为字符串转义配置变化而出错对后来维护的人来说也更好理解。7.2 贪婪与非贪婪、换行正则默认是贪婪匹配。比如一行字符串里有多处 HTML 标签你想把b.../b之间的内容去掉如果写成SELECT regexp_replace( b第一段/b 和 b第二段/b, b(.*)/b, 内容已移除 );因为.*贪婪它会从第一个b一直匹配到最后一个/b把整段“第一段和 第二段”全删掉只留下“ 和 ”两边的内容。如果你只想去掉每一对标签要写非贪婪量词SELECT regexp_replace( b第一段/b 和 b第二段/b, b(.*?)/b, 内容已移除, g );.*?表示尽量少匹配遇到第一个/b就停。这个区别在解析 HTML、JSON 半成品、日志片段时特别常见不细心真的容易把正确数据一起干掉。再说换行问题。默认情况下正则里的.不匹配换行符。如果你要匹配跨行的文本块有两种办法一是显式写[\\s\\S]表示“任意字符包括换行”二是用内联标志(?s)让点号匹配换行SELECT regexp_replace(log_line, (?s)START.(.*)END, 中间内容已捕获);内联标志(?s)其实就是“单行模式”在 PostgreSQL 里是支持的。处理多行日志时非常好用。7.3 常见问题速查表下面这个表是我自己平时速查用的你要抄作业可以直接拿去我想做什么推荐写法备注判断字段是否包含数字col ~ [0-9]至少一个数字忽略大小写匹配 abc 开头col ~* ^abc等价于col ILIKE abc%提取第一个日期substring(col from [0-9]{4}-[0-9]{2}-[0-9]{2})返回文本不用管数组提取所有手机号regexp_matches(col, 1[3-9][0-9]{9}, g)返回多行数组把连续空白压缩成单空格regexp_replace(col, [[:space:]], , g)比用\s稳按逗号/分号/全角符号拆分regexp_split_to_table(col, [,;])注意过滤空串统计模式出现次数regexp_count(col, ERROR [A-Z])PG16 开始支持定位第 2 次匹配的位置regexp_instr(col, [0-9], 1, 2)PG16 开始支持判断是否匹配且忽略大小写regexp_like(col, pattern, i)PG16 开始支持我自己的体会是PostgreSQL 的表达式能力已经非常接近脚本语言正则只是其中最顺手的一个代表。关键不在于背多少个函数而是要养成“先缩范围、再正则”、 “能字符类就少用反斜杠”、 “高频查询用生成列缓存结果”这几个习惯。我第一次在线上环境大规模写正则更新时也以为 SQL 写得漂亮就够了结果 2000 万行的表差点被一条正则替换拖垮。从那以后凡是批量正则处理我必先看执行计划、再加批处理这句话送给所有准备在 PostgreSQL 里“优雅”一把的同行。