式實(shí)戰(zhàn)：操作符、函數(shù)與性能優(yōu)化)
很多人在 PostgreSQL 里寫查詢一碰到字符串處理就頭疼。尤其是那種我就想從一段亂七八糟的文本里把想要的東西摳出來的需求比如從日志里抽時(shí)間戳、從 URL 里拆參數(shù)、從接口返回里剝掉 JSON 包裹。你會搜到一堆五花八門的答案有人讓你用substring有人甩給你split_part還有人直接說你上正則吧。但真到了正則這一步問題又來了PostgreSQL 里的正則跟 Python 的、跟 shell 的、跟 Perl 的長得有點(diǎn)像用起來卻不是一回事。~是什么~*又是什么regexp_matches和regexp_replace到底哪個(gè)該用更別說那個(gè)看起來人畜無害的SIMILAR TO其實(shí)是個(gè)大坑。這篇東西就把 PostgreSQL 正則這塊給你掰開揉碎講透。不聊那些網(wǎng)上一搜一大把的 API 羅列我直接按實(shí)戰(zhàn)場景來拆什么時(shí)候該用哪個(gè)操作符、哪個(gè)函數(shù)索引怎么配合性能怎么保命還有我在生產(chǎn)環(huán)境里踩過的一堆坑。適合正在寫 SQL 但被字符串處理折磨的人也適合那些剛接觸 PostgreSQL 想說正則我就用 LIKE 湊合一下的朋友——別湊合了看完這篇你能省下不止一下午。1. 從 LIKE 到正則把控匹配的三種姿勢1.1 LIKE 和 ILIKE 的邊界你得心里有數(shù)很多人最早接觸 PostgreSQL 字符串匹配都是從WHERE name LIKE %張三%這種寫法開始的。LIKE 本身沒有錯它簡單、快、有索引可用前綴匹配場景但它的問題在于表達(dá)能力太弱。你想表達(dá)字符串里包含一個(gè)數(shù)字LIKE 做不到你想表達(dá)以字母開頭、后面跟三個(gè)數(shù)字結(jié)尾LIKE 也很難受。PostgreSQL 為這個(gè)提供了SIMILAR TO語法試圖在 LIKE 和正則之間找一個(gè)中間態(tài)。但我要直接潑冷水SIMILAR TO在實(shí)際工作里能不用就不用。它的語法混合了 LIKE 的%和_又混進(jìn)了正則的*、、|寫出來既不如 LIKE 直觀又不如標(biāo)準(zhǔn)正則強(qiáng)大。而且它在EXPLAIN里優(yōu)化器基本沒法給出特別聰明的計(jì)劃性能表現(xiàn)也平平。很多時(shí)候你費(fèi)勁巴拉寫了一個(gè)SIMILAR TO表達(dá)式別的同事還得拿正則的書當(dāng)場翻譯。真正值得你花時(shí)間的是 PostgreSQL 里完整的正則支持。它默認(rèn)使用的是 AREAdvanced Regular Expression引擎也就是從 PostgreSQL 7.4 開始內(nèi)置的 Henry Spencer 那套正則庫能力上接近 POSIX ERE 的增強(qiáng)版支持前瞻、后顧、非貪婪匹配這些常見的高級特性。這套引擎就是你在~、~*、regexp_matches、regexp_replace等操作符和函數(shù)背后真正干活的家伙。1.2 操作符家族~、~、!~、!~PostgreSQL 提供了四個(gè)最基礎(chǔ)的正則操作符分別是匹配、不匹配、忽略大小寫匹配、忽略大小寫不匹配-- 匹配str 滿足正則 pattern SELECT abc123 ~ abc; -- true SELECT abc123 ~ ^abc\d$; -- true -- 不匹配 SELECT abc123 !~ ^xyz; -- true -- 忽略大小寫匹配 SELECT ABC123 ~* ^abc\d$; -- true -- 忽略大小寫不匹配 SELECT ABC123 !~* ^xyz; -- true這四個(gè)操作符幾乎覆蓋了日常 90% 的判斷某個(gè)字段是否滿足某種格式的需求。它們的執(zhí)行語義是返回布爾值所以最常出現(xiàn)在WHERE子句里做過濾。注意操作符的語義是部分匹配而不是整個(gè)字符串完全等于這個(gè)正則。什么意思abc123 ~ abc返回 true因?yàn)閍bc是abc123的一部分。如果你要表達(dá)整串必須完全匹配就得在正則兩端加上^和$或者用\A和\Z但簡單場景從^/$開始就夠了。這里藏著一個(gè)容易被忽略的細(xì)節(jié)~底層的比較邏輯其實(shí)是該字符串中是否存在某個(gè)子串滿足給定的正則。所以foobar ~ o*b是 true*在這里表示前面的字符重復(fù)零次或多次跟 LIKE 里的%完完全全是兩碼事。剛接觸的人最容易在這里被繞進(jìn)去把*當(dāng)成通配符亂用結(jié)果查出來的數(shù)據(jù)莫名其妙。1.3 正則怎么寫出能用的模式先過這三關(guān)第一關(guān)錨點(diǎn)。^匹配字符串開頭$匹配字符串結(jié)尾。PostgreSQL 默認(rèn)是逐行模式默認(rèn).不匹配換行符除非你用(?m)之類的標(biāo)志開啟多行模式所以^和$在有換行符的長文本里的行為需要想清楚。日常拿短字段做檢查基本是安全的。第二關(guān)字符類和分組。[a-zA-Z0-9_]這類寫法是正則基本功PostgreSQL 還額外支持\w、\d、\s這類簡寫也支持[[:alpha:]]這種 POSIX 字符類寫法。括號()用來分組分組的另一個(gè)作用是捕獲后面配合regexp_matches或regexp_replace的反向引用時(shí)會用上。第三關(guān)量詞。*、、?、{m,n}這些跟標(biāo)準(zhǔn)正則一致。但要注意 PostgreSQL 正則默認(rèn)是貪婪的也就是能多匹配就多匹配。比如對a123b456c用a.*b去匹配結(jié)果會是a123b456c中的a123b456c嗎不是a123b加上后面的456c都不是。.*會一直吞到最后一個(gè)b所以匹配到的是a123b456b不對原串里只有一個(gè)b。我換一個(gè)更清晰的例子對a1b2b用a.*b匹配貪婪模式下它會在最后一個(gè)b處停下來結(jié)果整體是a1b2b。如果寫a.*?b非貪婪模式會在第一個(gè)b處停結(jié)果是a1b。這個(gè)細(xì)節(jié)非常非常容易踩坑。尤其是你想從開頭一直匹配到第一次出現(xiàn)某個(gè)關(guān)鍵字的時(shí)候忘了加?就會吞掉一大片不該吞的內(nèi)容。我見過不止一個(gè)同事在提取日志內(nèi)容時(shí)因?yàn)樨澙妨吭~把兩三條日志全烙在一行里排查了半小時(shí)才發(fā)現(xiàn)是正則的鍋。2. 核心函數(shù)逐個(gè)拆matches、replace、split 一個(gè)都別放過2.1 regexp_matches別被多行返回坑了regexp_matches返回的是匹配到的部分并且按正則里的捕獲組拆開。這個(gè)函數(shù)的簽名是regexp_matches(string text, pattern text [, flags text]) - setof text[]注意返回類型是setof text[]。也就是說它在語義上是一個(gè)集合返回函數(shù)會為每個(gè)匹配位置返回一行每行是一個(gè)數(shù)組數(shù)組元素是每個(gè)捕獲組對應(yīng)的內(nèi)容。如果你只寫了零個(gè)捕獲組也沒關(guān)系它的數(shù)組里只有一個(gè)元素——整個(gè)匹配的文本。舉個(gè)典型例子SELECT regexp_matches( 訂單號A12345金額88.50元訂單號B67890金額12.00元, 訂單號([A-Z]\d)金額(\d\.\d)元, g );g標(biāo)志表示全局匹配。不加g時(shí)它只返回第一個(gè)匹配位置加了之后返回所有。由于它返回的是集合所以不能直接在SELECT列表里妄圖當(dāng)成單值用雖然 PostgreSQL 允許集合函數(shù)出現(xiàn)在列表里但結(jié)果行數(shù)會膨脹很容易出現(xiàn)莫名其妙多出來幾行的情況。更穩(wěn)妥的做法是配LIMIT 1或作為一個(gè)子查詢或者在FROM里對函數(shù)結(jié)果做連接。再強(qiáng)調(diào)一個(gè)容易炸的點(diǎn)如果正則里有捕獲組返回的數(shù)組元素個(gè)數(shù)由捕獲組個(gè)數(shù)決定不是由匹配到的整體決定的。你想拿整體就必須寫成(?:...)非捕獲組或者干脆不寫括號。2.2 regexp_replace最剛需的字符串清洗利器regexp_replace的用法跟其他語言的正則替換很像簽名如下regexp_replace(source text, pattern text, replacement text [, flags text])它最實(shí)用的地方在于你可以用\1、\2來引用正則里的捕獲組。比如你想把手機(jī)號中間四位打碼SELECT regexp_replace(13812345678, ^(\d{3})\d{4}(\d{4})$, \1****\2); -- 輸出138****5678這個(gè)函數(shù)我還常用在清理臟數(shù)據(jù)上。比如某個(gè)字段里混入了不可見字符、連續(xù)空格、換行符一條 SQL 就能規(guī)整掉-- 把連續(xù)空白字符空格、制表符、換行替換成單個(gè)空格 SELECT regexp_replace(raw_text, \s, , g) FROM some_table;還有個(gè)力氣活是去掉字符串里所有 HTML 標(biāo)簽。雖然我不建議讓數(shù)據(jù)庫干這種前端該干的活但偶爾數(shù)據(jù)修復(fù)場景可以頂一下SELECT regexp_replace(html_content, [^], , g);regexp_replace和regexp_matches有個(gè)性能上的差異regexp_replace是單趟掃描、逐處替換開銷集中在正則引擎的匹配過程返回的是單行單列不會產(chǎn)生行數(shù)膨脹。所以能用regexp_replace搞定的清洗工作絕對不要先regexp_matches拆成行再string_agg拼回去那是繞遠(yuǎn)路。2.3 regexp_split_to_table 和 regexp_split_to_array小心分隔符陷阱這兩個(gè)函數(shù)一個(gè)把字符串按正則拆成多行另一個(gè)拆成數(shù)組。-- 用逗號分隔但忽略括號里的逗號 SELECT regexp_split_to_table(a,b(c,d),e, ,(?![^()]*\)));這個(gè)例子就是典型的我要按分隔符拆但又不想在括號內(nèi)的分隔符處拆。正則領(lǐng)域的標(biāo)準(zhǔn)解法是用負(fù)向前瞻后面不能跟著直到遇到右括號之前都沒有左括號的情況。不過說實(shí)話這種復(fù)雜正則不是每次都能寫得對寫之前先考慮一下你的字符串格式到底穩(wěn)不穩(wěn)定如果連分隔符本身都不規(guī)范不如先做一步清洗再拆。regexp_split_to_array的用法類似只是返回?cái)?shù)組SELECT regexp_split_to_array(apple,banana;cherry|date, [,;|]); -- 結(jié)果{apple,banana,cherry,date}這里有個(gè)非常常見的坑如果輸入的字符串以分隔符開頭或結(jié)尾regexp_split_to_*會返回一個(gè)空字符串元素。比如regexp_split_to_array(a,b,, ,)的結(jié)果是{a,b,}末尾的空字符串會被保留。而如果字符串本身就是空的可能返回空數(shù)組或者包含空字符串具體行為在不同版本上還有過變化——這類邊緣 case最好在應(yīng)用層先做空值判斷別指望數(shù)據(jù)庫替你兜底。2.4 別忽略好用的小工具regexp_substr、regexp_instr、regexp_count這幾兄弟不是 PostgreSQL 原生的但 PostgreSQL 15 開始新引入的regexp_substr、regexp_instr、regexp_count真的是大大提升了日常效率。以前大家想取子串得regexp_matches配合下標(biāo)或者substring配合正則現(xiàn)在可以寫得更像 Oracle-- 從字符串中提取第一個(gè)數(shù)字序列 SELECT regexp_substr(訂單號A12345總價(jià)88.5, \d(\.\d)?); -- 返回12345 -- 找到第一個(gè)數(shù)字出現(xiàn)的位置 SELECT regexp_instr(訂單號A12345總價(jià)88.5, \d); -- 返回4這里是字符位置從1開始 -- 統(tǒng)計(jì)數(shù)字序列出現(xiàn)了幾次 SELECT regexp_count(A1 B22 C333, \d); -- 返回3雖然這三個(gè)函數(shù)在純函數(shù)式寫法上比早期的 PostgreSQL 版本更友好但如果你還在用 PostgreSQL 14 或更早版本就得老老實(shí)實(shí)回到regexp_matches和strpos的老路上去。要注意函數(shù)的具體可用版本是硬約束我這篇內(nèi)容默認(rèn)以 PostgreSQL 15/16 為主參考老版本用戶看到函數(shù)不存在別慌看自己服務(wù)器的version()先。2.5 substring 里的正則這是被忽視的第一入口substring(string from pattern)是 PostgreSQL 專門用來做提取第一個(gè)匹配片段的語法糖。日常提取 IP、時(shí)間戳、訂單號它比regexp_matches更簡潔因?yàn)樗菢?biāo)量返回不會給你搞出集合來SELECT substring(訪問IP: 192.168.1.1 時(shí)間: 2024-05-01 12:00:00 FROM (\d{1,3}(\.\d{1,3}){3})); -- 返回192.168.1.1注意substring只返回第一個(gè)捕獲組。如果你的正則里沒有捕獲組就返回整個(gè)匹配串。這種行為跟regexp_matches的數(shù)組元素邏輯類似但省去了集合提取的煩惱。基于這個(gè)特性它非常適合出現(xiàn)在SELECT列表里直接生成清洗后的列。3. 實(shí)戰(zhàn)從一段真實(shí)日志里把關(guān)鍵字段摳出來3.1 場景定義與數(shù)據(jù)準(zhǔn)備假設(shè)我們要處理一條線上服務(wù)日志格式長這樣[2024-05-01 23:45:04.213] INFO order-service - order created user9527 amount199.00 item無線鼠標(biāo) ip10.12.34.56目標(biāo)提取日志時(shí)間、日志級別、用戶ID、金額、IP 地址。這種東西你當(dāng)然可以用split_part一層一層剝但日志字段順序固定、長度不定的情況下split_part寫起來非常痛苦比如 item 里可能還有空格和引號直接按空格拆會全亂。正則在這里的優(yōu)勢是按模式定位而不是按位置拆分。建表模擬數(shù)據(jù)CREATE TABLE log_tmp (line text); INSERT INTO log_tmp VALUES ([2024-05-01 23:45:04.213] INFO order-service - order created user9527 amount199.00 item無線鼠標(biāo) ip10.12.34.56), ([2024-05-01 23:46:11.887] ERROR order-service - order failed user9527 amount199.00 item無線鍵盤 ip10.12.34.56);3.2 一條 SQL 完成多字段提取下面這段 SQL 是核心示例我建議你在自己的環(huán)境里跑一遍看效果SELECT substring(line FROM \[(\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}\.\d{3})\]) AS log_time, substring(line FROM \] (\w) ) AS log_level, substring(line FROM user(\d)) AS user_id, substring(line FROM amount(\d\.\d{2})) AS amount, substring(line FROM ip(\d{1,3}(\.\d{1,3}){3})) AS ip FROM log_tmp;重點(diǎn)講幾個(gè)細(xì)節(jié)。第一log_time里我用了\[和\]轉(zhuǎn)義括號因?yàn)榉嚼ㄌ栐谡齽t里有字符類語義不能直接裸寫。第二log_level的\] (\w)我在右括號后放了空格然后匹配單詞字符。日志里INFO和order-service之間的空格數(shù)可能不固定所以這個(gè)寫法定死了必須緊跟一個(gè)空格——如果空格數(shù)變了就會漏。更穩(wěn)妥的寫法是\]\s(\w)\s。第三IP 的正則(\d{1,3}(\.\d{1,3}){3})這里外層的括號是捕獲組用來提取整個(gè) IP內(nèi)層(\.\d{1,3}){3}是為了表達(dá)點(diǎn)加三位數(shù)字重復(fù)三次。但是請注意substring會優(yōu)先返回第一個(gè)捕獲組我們這里外層的(...)是第一個(gè)捕獲組所以能正確返回完整 IP。如果你不小心把內(nèi)層捕獲組寫到前面去了返回的就是最后一組三位數(shù)字了。3.3 提取結(jié)果為空先查正則的命中前提不少人寫完后發(fā)現(xiàn)某個(gè)字段一直返回NULL第一反應(yīng)是數(shù)據(jù)是不是不對。但更常見的原因是正則不夠?qū)捤伞１热缟厦嫣崛?IP 的正則我用了\d{1,3}(\.\d{1,3}){3}但如果日志里 IP 后面還跟著別的內(nèi)容比如ip10.12.34.56,這個(gè)正則其實(shí)也能匹配因?yàn)閧3}只約束了分組重復(fù)次數(shù)后面有沒有逗號不影響匹配成功。問題往往出在錨點(diǎn)缺失導(dǎo)致匹配到了錯誤的子串。比如你提取時(shí)間是\[(\d{4})而一行日志里可能在 message 部分又出現(xiàn)了一個(gè)年份——那substring會提取出第一個(gè)匹配也就是時(shí)間戳里的年份這沒問題但如果你實(shí)際想提取的是消息里埋的年份這個(gè)寫法就廢了。一條實(shí)用的排錯思路先用不帶捕獲組的寬正則看能不能匹配到目標(biāo)再用regexp_matches(..., g)把所有匹配結(jié)果拉出來看一遍確認(rèn)你的第一個(gè)匹配到底命中在哪個(gè)位置。3.4 數(shù)據(jù)清洗實(shí)戰(zhàn)一行命令去掉 JSON 轉(zhuǎn)義和 HTML 標(biāo)簽再分享一個(gè)我在實(shí)際項(xiàng)目里高頻使用的清洗場景修復(fù)從上游接口寫入的臟數(shù)據(jù)。比如字段里混入了\n、\t這種可見轉(zhuǎn)義反斜杠加字母而不是真正的換行符或者混進(jìn)了p、br這類 HTML 殘留。一條regexp_replace就能處理UPDATE articles SET body regexp_replace( regexp_replace(body, \\[ntr], , g), [^], , g ) WHERE body ~ \\[ntr]|[^];這里有個(gè)小細(xì)節(jié)我要匹配反斜杠本身所以在 SQL 字符串里要寫\\[ntr]。如果用標(biāo)準(zhǔn)字符串寫法\n會被 PostgreSQL 解析成真正的換行符那就完全不是這個(gè)意圖了。很多新手第一次寫正則替換都會被這個(gè)反斜杠轉(zhuǎn)義搞崩潰。記住一句口訣SQL 字符串里的兩個(gè)反斜杠在正則引擎眼里就是一個(gè)反斜杠如果你想匹配一個(gè)反斜杠加一個(gè)字母nSQL 里得寫三個(gè)甚至四個(gè)反斜杠。實(shí)際上在 SQL 中匹配單個(gè)反斜杠最穩(wěn)的寫法是\\\\因?yàn)?PostgreSQL 標(biāo)準(zhǔn)字符串里\\變成\正則引擎收到\后如果后面不接特殊字符有時(shí)會報(bào)錯或行為奇怪。所以我的習(xí)慣是能用[\\]就不要用\\。比如匹配 ASCII 轉(zhuǎn)義序列我會寫成-- 匹配一個(gè)反斜杠后面跟著 n/t/r regexp_replace(body, [\\][ntr], , g)這里[\\]在正則里是一個(gè)字符類表示字符集中的反斜杠干凈利落不折騰。4. 性能優(yōu)化別讓正則變成慢查詢元兇4.1 正則不用索引也不一定全對很多人一聽正則就條件反射式地說無法走索引。這個(gè)說法過于絕對。確實(shí)~ abc.*這種寫法在原理上很難利用普通 B-tree 索引因?yàn)閮?yōu)化器不知道匹配的字符串落在一個(gè)有序區(qū)間內(nèi)的哪個(gè)范圍。但如果你只要前綴匹配那么正則也能走索引。比如name ~ ^abc優(yōu)化器可以將其轉(zhuǎn)換成一個(gè)范圍條件從abc開始到abd結(jié)束從而利用 B-tree 索引。不過這種轉(zhuǎn)換并不是 PostgreSQL 的默認(rèn)操作實(shí)踐中你最好還是用name LIKE abc%這種寫法優(yōu)化器對這種寫法有明確的索引支持。另外如果正則匹配的格式是前綴固定 后綴可變且這個(gè)前綴有一定的區(qū)分度可以考慮表達(dá)式索引。比如CREATE INDEX idx_log_user_prefix ON log_tmp ((substring(line FROM user(\d))));但這種索引只能配合完全一致的表達(dá)式才能命中為了這一小撮查詢建索引性價(jià)比通常不高。更常見的方式是給待匹配的字段解構(gòu)出獨(dú)立列比如用戶ID、訂單號、狀態(tài)碼對這些離散列建普通 B-tree 索引查詢時(shí)直接等值匹配徹底繞開正則。這也是能拆列就別正則的核心理由——正則不是萬金油很多時(shí)候先做一次 ETL 把字段拆好后續(xù)查詢性能輕松漲一個(gè)量級。4.2 大表 LIKE 和正則的性能對比實(shí)驗(yàn)我自己測過一張百萬行級的表字段remark查詢WHERE remark ~ 訂單號A[0-9]{5}全表掃描耗時(shí)大概在 800ms 上下取決于平均字段長度。而同樣的查詢需求如果上游在寫入時(shí)已經(jīng)拆出了order_id列并建索引WHERE order_id LIKE A%或等值匹配耗時(shí)直接降到幾十毫秒。所以結(jié)論很清晰對大數(shù)據(jù)量的在線查詢正則過濾能避則避不能避也盡量把正則限定在全表掃描可控的小表上或者放到離線分析場景。當(dāng)然也有場景是正則跑得比你想象中快的。比如你給一個(gè)text字段建了 GIN 索引配合pg_trgm模塊這時(shí)候某些正則也能借助三元組索引加速。但這玩意兒配置起來麻煩且它只能加速部分模式比如模式里有固定字符串片段如果你的正則全是.*這種GIN 也救不了你。4.3 避免在 JOIN 條件或 GROUP BY 里無腦用正則這是我踩過最深的一個(gè)坑把正則寫進(jìn) JOIN 的ON條件里做模糊匹配。比如SELECT * FROM a JOIN b ON a.code ~ (^ || b.pattern || $);這種寫法的執(zhí)行計(jì)劃必然是先做嵌套循環(huán)然后對每一對組合跑一次正則。外層一百行、內(nèi)層一萬行就是一百萬次正則編譯加匹配查詢直接卡死?，F(xiàn)實(shí)里這么干的需求往往是想做規(guī)則表匹配但更好的做法是先判斷你的規(guī)則能不能拆成離散字段不能的話至少把規(guī)則表加個(gè)前綴索引用LIKE把候選集縮小到幾十行再對剩下的做正則精匹配。4.4 正則緩存和防呆寫法PostgreSQL 的正則引擎會緩存最近使用的正則模式允許的緩存大小是regex_cache相關(guān)參數(shù)控制的。但如果你一條 SQL 里動態(tài)生成了成千上萬個(gè)不同的正則比如上面 JOIN 場景緩存根本頂不住反而會因?yàn)轭l繁淘汰導(dǎo)致額外開銷。所以最佳實(shí)踐是同一個(gè)模式盡量全局復(fù)用不要拼接無謂的字符串。舉個(gè)反面教材-- 不要這樣 WHERE remark ~ (SELECT pattern FROM rule WHERE id 1)每行執(zhí)行時(shí)都去查規(guī)則表再重新編譯正則性能爛透。正確做法是先取出來變成參數(shù)或者拆成兩步-- 先拿到 pattern再作為綁定變量傳給查詢 PREPARE check_remark(text) AS SELECT * FROM t WHERE remark ~ $1; EXECUTE check_remark(訂單號A\d{5});PREPARE的好處是讓 PostgreSQL 把正則模式作為參數(shù)緩存下來重復(fù)執(zhí)行時(shí)不用每行都重新編譯正則省下的開銷在高頻查詢下非?？捎^。5. 常見問題與坑位速查5.1 不同版本正則函數(shù)有多大差異PostgreSQL 不同版本之間正則相關(guān)的能力差異還挺大的。版本變更重點(diǎn)PostgreSQL 10 及以前只有~系列操作符、substring、regexp_matches、regexp_replace、regexp_split_to_*PostgreSQL 11 / 12 / 13 / 14無大變化都是內(nèi)引擎增強(qiáng)PostgreSQL 15新增regexp_substr、regexp_instr、regexp_countPostgreSQL 16對regexp_*系列函數(shù)的行為做了細(xì)節(jié)修正并優(yōu)化了部分邊界情況如果你在 PostgreSQL 14 上跑regexp_count會直接報(bào)function regexp_count(text, text) does not exist。這不是你的 SQL 寫錯了是版本不支持。所以遇到函數(shù)不存在先查current_setting(server_version)別折騰半天正則表達(dá)式最后發(fā)現(xiàn)是版本問題。5.2 flags 參數(shù)到底怎么寫regexp_*系列函數(shù)的最后一個(gè)可選參數(shù)是 flags常用的有flag含義g全局匹配返回所有匹配位置i忽略大小寫n讓.也能匹配換行符新行m多行模式^和$匹配每行的開頭和結(jié)尾s讓空白字符按\s匹配非默認(rèn)行為視正則有差異最容易被忽略的是n和m的區(qū)別。m是讓^/$按行生效n是讓.能跨行。如果你處理的是多行日志文本想在每一行里提取某個(gè)字段就得用m。比如SELECT regexp_matches( E第一行 error1\n第二行 error2\n第三行 error3, ^.*error(\d)$, gm );注意這里g和m連寫在一起。如果沒有m^只會匹配整個(gè)字符串的開頭結(jié)果就只有一個(gè)1。5.3 正則里的括號、反斜杠、$ 符號的轉(zhuǎn)義迷局這是幾乎所有正則新手都會撞的墻。在 PostgreSQL 字符串里寫正則遇到的轉(zhuǎn)義層級是雙重的先是 SQL 字符串層再是正則層。舉例你要匹配字符串里的美元符號$在正則里$是錨點(diǎn)要匹配字面$需要轉(zhuǎn)義成\$在 SQL 標(biāo)準(zhǔn)字符串里反斜杠需要寫成\\所以你在 SQL 里要寫\\$看起來是不是頭大我這里給你一個(gè)治本的方法優(yōu)先使用 PostgreSQL 的 dollar-quoted 字符串常量來寫正則或者用E轉(zhuǎn)義字符串。比如SELECT $100 ~ E\\$\\d; -- true SELECT $100 ~ $\\d; -- 這個(gè)可能不會按你預(yù)期工作實(shí)際上更穩(wěn)的習(xí)慣是正則里盡量用字符類避免反斜杠轉(zhuǎn)義。匹配$可以寫成[$]匹配.可以寫成[.]匹配*可以寫成[*]。字符類內(nèi)部的這些特殊字符多數(shù)不需要轉(zhuǎn)義寫出來也更安全。5.4 常見問題速查表我把平時(shí)工作里被問得最多的場景整理成一張速查表遇到可以直接抄需求推薦寫法判斷字符串是否以數(shù)字開頭str ~ ^\d判斷字符串是否只包含字母str ~ ^[a-zA-Z]$判斷是否包含連續(xù)三個(gè)以上空格str ~ {3,}提取第一個(gè)時(shí)間戳標(biāo)準(zhǔn)格式substring(str FROM \d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2})提取所有郵箱regexp_matches(str, [a-zA-Z0-9._%-][a-zA-Z0-9.-]\.[a-zA-Z]{2,}, g)去掉字符串首尾空格regexp_replace(str, ^\s按逗號拆行但忽略括號內(nèi)逗號regexp_split_to_table(str, ,(?![^()]*\)))手機(jī)號中間四位打碼regexp_replace(phone, ^(\d{3})\d{4}(\d{4})$, \1****\2)需要特別提醒的是上面這些寫法的性能只適合中小數(shù)據(jù)量百萬行以內(nèi)的單表過濾。上了千萬行還這么查神仙也救不了。你得回到 4 節(jié)說的思路先拆列、再索引、最后才正則。5.5 定位問題的方法論從結(jié)果反推正則模式當(dāng)你遇到結(jié)果字段為 NULL或者匹配的行比預(yù)期多不要急著反復(fù)修改正則。我的建議是分三步走第一步只跑正則不帶業(yè)務(wù)條件。比如SELECT line, regexp_matches(line, 這里寫你的模式, g) FROM log_tmp LIMIT 20;先直接看它匹配出了什么。第二步逐段拆解。如果你的正則寫了 60 個(gè)字符把它拆成三個(gè)小片段分別測。比如先測\d{4}-\d{2}-\d{2}再測 \d{2}:\d{2}:\d{2}最后拼起來。這一步能快速定位是前半段錯了還是后半段錯了。第三步確認(rèn)邊界字符。看原始數(shù)據(jù)里是不是混入了不可見字符比如\r回車、零寬空格、全角空格等。用SELECT line, encode(line::bytea, escape) FROM log_tmp;可以直觀看到隱藏字符。很多時(shí)候字段顯示沒問題一查編碼發(fā)現(xiàn)里面藏著\r正則的$就匹配不上了。6. 經(jīng)驗(yàn)之談?wù)齽t不是銀彈但確實(shí)是瑞士軍刀最后講一點(diǎn)我在實(shí)戰(zhàn)里的體會。正則表達(dá)式在 PostgreSQL 里是極度好用的瑞士軍刀但它不是銀彈。日常開發(fā)里我給自己定了三條軍規(guī)。第一能拆列就不要繞正則。如果你的業(yè)務(wù)字段本身有結(jié)構(gòu)比如訂單號永遠(yuǎn)是前綴數(shù)字那就應(yīng)該在建表時(shí)拆成order_prefix和order_no兩列。維護(hù)成本其實(shí)很低但查詢性能和可讀性提升巨大。正則適合偶發(fā)清洗和復(fù)雜提取不適合放進(jìn)高頻查詢鏈路里做核心過濾。第二正則寫完一定要加注釋。SQL 里的正則本來就難讀沒人愿意半個(gè)月后回來猜你這串(?![^()]*\))到底想干嘛。我會在字段邊上留一行注釋寫明意圖和輸入樣例。有些公司還會把這些常用正則收集成一張正則模板表新同事直接復(fù)用避免重復(fù)踩坑。第三上線前用真實(shí)數(shù)據(jù)量做一次性能驗(yàn)證。在小表上這條 SQL 跑了 50ms 不叫快千萬行表上跑了 50ms 才是真快。正則引擎的開銷跟字符串長度和復(fù)雜度密切相關(guān)不可控因素很多。我的習(xí)慣是先把線上數(shù)據(jù)撈一小部分回來建表造個(gè)近似量級的數(shù)據(jù)壓測一下再放到生產(chǎn)環(huán)境跑。尤其是那種會自動拼正則的規(guī)則引擎上線前不驗(yàn)證等于埋雷。PostgreSQL 的正則能力比很多數(shù)據(jù)庫都要完整從基礎(chǔ)操作符到高級函數(shù)覆蓋了從判斷、提取、替換到拆分的所有場景。但它也有自己的語法邊界、版本邊界和性能邊界。把這些邊界摸透你才能在寫不出來和跑不動之間找到那條優(yōu)雅的平衡線。這篇內(nèi)容里提到的每種寫法、每個(gè)坑位都是我實(shí)際踩過或幫別人排查過的場景。你不需要一次性全部背下來但建議收藏起來等真遇到從一個(gè)長字符串里摳字段的需求時(shí)再來對照著用。 PostgreSQL正則表達(dá)式實(shí)戰(zhàn)：操作符、函數(shù)與性能優(yōu)化
很多人在 PostgreSQL 里寫查詢一碰到字符串處理就頭疼。尤其是那種我就想從一段亂七八糟的文本里把想要的東西摳出來的需求比如從日志里抽時(shí)間戳、從 URL 里拆參數(shù)、從接口返回里剝掉 JSON 包裹。你會搜到一堆五花八門的答案有人讓你用substring有人甩給你split_part還有人直接說你上正則吧。但真到了正則這一步問題又來了PostgreSQL 里的正則跟 Python 的、跟 shell 的、跟 Perl 的長得有點(diǎn)像用起來卻不是一回事。~是什么~*又是什么regexp_matches和regexp_replace到底哪個(gè)該用更別說那個(gè)看起來人畜無害的SIMILAR TO其實(shí)是個(gè)大坑。這篇東西就把 PostgreSQL 正則這塊給你掰開揉碎講透。不聊那些網(wǎng)上一搜一大把的 API 羅列我直接按實(shí)戰(zhàn)場景來拆什么時(shí)候該用哪個(gè)操作符、哪個(gè)函數(shù)索引怎么配合性能怎么保命還有我在生產(chǎn)環(huán)境里踩過的一堆坑。適合正在寫 SQL 但被字符串處理折磨的人也適合那些剛接觸 PostgreSQL 想說正則我就用 LIKE 湊合一下的朋友——別湊合了看完這篇你能省下不止一下午。1. 從 LIKE 到正則把控匹配的三種姿勢1.1 LIKE 和 ILIKE 的邊界你得心里有數(shù)很多人最早接觸 PostgreSQL 字符串匹配都是從WHERE name LIKE %張三%這種寫法開始的。LIKE 本身沒有錯它簡單、快、有索引可用前綴匹配場景但它的問題在于表達(dá)能力太弱。你想表達(dá)字符串里包含一個(gè)數(shù)字LIKE 做不到你想表達(dá)以字母開頭、后面跟三個(gè)數(shù)字結(jié)尾LIKE 也很難受。PostgreSQL 為這個(gè)提供了SIMILAR TO語法試圖在 LIKE 和正則之間找一個(gè)中間態(tài)。但我要直接潑冷水SIMILAR TO在實(shí)際工作里能不用就不用。它的語法混合了 LIKE 的%和_又混進(jìn)了正則的*、、|寫出來既不如 LIKE 直觀又不如標(biāo)準(zhǔn)正則強(qiáng)大。而且它在EXPLAIN里優(yōu)化器基本沒法給出特別聰明的計(jì)劃性能表現(xiàn)也平平。很多時(shí)候你費(fèi)勁巴拉寫了一個(gè)SIMILAR TO表達(dá)式別的同事還得拿正則的書當(dāng)場翻譯。真正值得你花時(shí)間的是 PostgreSQL 里完整的正則支持。它默認(rèn)使用的是 AREAdvanced Regular Expression引擎也就是從 PostgreSQL 7.4 開始內(nèi)置的 Henry Spencer 那套正則庫能力上接近 POSIX ERE 的增強(qiáng)版支持前瞻、后顧、非貪婪匹配這些常見的高級特性。這套引擎就是你在~、~*、regexp_matches、regexp_replace等操作符和函數(shù)背后真正干活的家伙。1.2 操作符家族~、~、!~、!~PostgreSQL 提供了四個(gè)最基礎(chǔ)的正則操作符分別是匹配、不匹配、忽略大小寫匹配、忽略大小寫不匹配-- 匹配str 滿足正則 pattern SELECT abc123 ~ abc; -- true SELECT abc123 ~ ^abc\d$; -- true -- 不匹配 SELECT abc123 !~ ^xyz; -- true -- 忽略大小寫匹配 SELECT ABC123 ~* ^abc\d$; -- true -- 忽略大小寫不匹配 SELECT ABC123 !~* ^xyz; -- true這四個(gè)操作符幾乎覆蓋了日常 90% 的判斷某個(gè)字段是否滿足某種格式的需求。它們的執(zhí)行語義是返回布爾值所以最常出現(xiàn)在WHERE子句里做過濾。注意操作符的語義是部分匹配而不是整個(gè)字符串完全等于這個(gè)正則。什么意思abc123 ~ abc返回 true因?yàn)閍bc是abc123的一部分。如果你要表達(dá)整串必須完全匹配就得在正則兩端加上^和$或者用\A和\Z但簡單場景從^/$開始就夠了。這里藏著一個(gè)容易被忽略的細(xì)節(jié)~底層的比較邏輯其實(shí)是該字符串中是否存在某個(gè)子串滿足給定的正則。所以foobar ~ o*b是 true*在這里表示前面的字符重復(fù)零次或多次跟 LIKE 里的%完完全全是兩碼事。剛接觸的人最容易在這里被繞進(jìn)去把*當(dāng)成通配符亂用結(jié)果查出來的數(shù)據(jù)莫名其妙。1.3 正則怎么寫出能用的模式先過這三關(guān)第一關(guān)錨點(diǎn)。^匹配字符串開頭$匹配字符串結(jié)尾。PostgreSQL 默認(rèn)是逐行模式默認(rèn).不匹配換行符除非你用(?m)之類的標(biāo)志開啟多行模式所以^和$在有換行符的長文本里的行為需要想清楚。日常拿短字段做檢查基本是安全的。第二關(guān)字符類和分組。[a-zA-Z0-9_]這類寫法是正則基本功PostgreSQL 還額外支持\w、\d、\s這類簡寫也支持[[:alpha:]]這種 POSIX 字符類寫法。括號()用來分組分組的另一個(gè)作用是捕獲后面配合regexp_matches或regexp_replace的反向引用時(shí)會用上。第三關(guān)量詞。*、、?、{m,n}這些跟標(biāo)準(zhǔn)正則一致。但要注意 PostgreSQL 正則默認(rèn)是貪婪的也就是能多匹配就多匹配。比如對a123b456c用a.*b去匹配結(jié)果會是a123b456c中的a123b456c嗎不是a123b加上后面的456c都不是。.*會一直吞到最后一個(gè)b所以匹配到的是a123b456b不對原串里只有一個(gè)b。我換一個(gè)更清晰的例子對a1b2b用a.*b匹配貪婪模式下它會在最后一個(gè)b處停下來結(jié)果整體是a1b2b。如果寫a.*?b非貪婪模式會在第一個(gè)b處停結(jié)果是a1b。這個(gè)細(xì)節(jié)非常非常容易踩坑。尤其是你想從開頭一直匹配到第一次出現(xiàn)某個(gè)關(guān)鍵字的時(shí)候忘了加?就會吞掉一大片不該吞的內(nèi)容。我見過不止一個(gè)同事在提取日志內(nèi)容時(shí)因?yàn)樨澙妨吭~把兩三條日志全烙在一行里排查了半小時(shí)才發(fā)現(xiàn)是正則的鍋。2. 核心函數(shù)逐個(gè)拆matches、replace、split 一個(gè)都別放過2.1 regexp_matches別被多行返回坑了regexp_matches返回的是匹配到的部分并且按正則里的捕獲組拆開。這個(gè)函數(shù)的簽名是regexp_matches(string text, pattern text [, flags text]) - setof text[]注意返回類型是setof text[]。也就是說它在語義上是一個(gè)集合返回函數(shù)會為每個(gè)匹配位置返回一行每行是一個(gè)數(shù)組數(shù)組元素是每個(gè)捕獲組對應(yīng)的內(nèi)容。如果你只寫了零個(gè)捕獲組也沒關(guān)系它的數(shù)組里只有一個(gè)元素——整個(gè)匹配的文本。舉個(gè)典型例子SELECT regexp_matches( 訂單號A12345金額88.50元訂單號B67890金額12.00元, 訂單號([A-Z]\d)金額(\d\.\d)元, g );g標(biāo)志表示全局匹配。不加g時(shí)它只返回第一個(gè)匹配位置加了之后返回所有。由于它返回的是集合所以不能直接在SELECT列表里妄圖當(dāng)成單值用雖然 PostgreSQL 允許集合函數(shù)出現(xiàn)在列表里但結(jié)果行數(shù)會膨脹很容易出現(xiàn)莫名其妙多出來幾行的情況。更穩(wěn)妥的做法是配LIMIT 1或作為一個(gè)子查詢或者在FROM里對函數(shù)結(jié)果做連接。再強(qiáng)調(diào)一個(gè)容易炸的點(diǎn)如果正則里有捕獲組返回的數(shù)組元素個(gè)數(shù)由捕獲組個(gè)數(shù)決定不是由匹配到的整體決定的。你想拿整體就必須寫成(?:...)非捕獲組或者干脆不寫括號。2.2 regexp_replace最剛需的字符串清洗利器regexp_replace的用法跟其他語言的正則替換很像簽名如下regexp_replace(source text, pattern text, replacement text [, flags text])它最實(shí)用的地方在于你可以用\1、\2來引用正則里的捕獲組。比如你想把手機(jī)號中間四位打碼SELECT regexp_replace(13812345678, ^(\d{3})\d{4}(\d{4})$, \1****\2); -- 輸出138****5678這個(gè)函數(shù)我還常用在清理臟數(shù)據(jù)上。比如某個(gè)字段里混入了不可見字符、連續(xù)空格、換行符一條 SQL 就能規(guī)整掉-- 把連續(xù)空白字符空格、制表符、換行替換成單個(gè)空格 SELECT regexp_replace(raw_text, \s, , g) FROM some_table;還有個(gè)力氣活是去掉字符串里所有 HTML 標(biāo)簽。雖然我不建議讓數(shù)據(jù)庫干這種前端該干的活但偶爾數(shù)據(jù)修復(fù)場景可以頂一下SELECT regexp_replace(html_content, [^], , g);regexp_replace和regexp_matches有個(gè)性能上的差異regexp_replace是單趟掃描、逐處替換開銷集中在正則引擎的匹配過程返回的是單行單列不會產(chǎn)生行數(shù)膨脹。所以能用regexp_replace搞定的清洗工作絕對不要先regexp_matches拆成行再string_agg拼回去那是繞遠(yuǎn)路。2.3 regexp_split_to_table 和 regexp_split_to_array小心分隔符陷阱這兩個(gè)函數(shù)一個(gè)把字符串按正則拆成多行另一個(gè)拆成數(shù)組。-- 用逗號分隔但忽略括號里的逗號 SELECT regexp_split_to_table(a,b(c,d),e, ,(?![^()]*\)));這個(gè)例子就是典型的我要按分隔符拆但又不想在括號內(nèi)的分隔符處拆。正則領(lǐng)域的標(biāo)準(zhǔn)解法是用負(fù)向前瞻后面不能跟著直到遇到右括號之前都沒有左括號的情況。不過說實(shí)話這種復(fù)雜正則不是每次都能寫得對寫之前先考慮一下你的字符串格式到底穩(wěn)不穩(wěn)定如果連分隔符本身都不規(guī)范不如先做一步清洗再拆。regexp_split_to_array的用法類似只是返回?cái)?shù)組SELECT regexp_split_to_array(apple,banana;cherry|date, [,;|]); -- 結(jié)果{apple,banana,cherry,date}這里有個(gè)非常常見的坑如果輸入的字符串以分隔符開頭或結(jié)尾regexp_split_to_*會返回一個(gè)空字符串元素。比如regexp_split_to_array(a,b,, ,)的結(jié)果是{a,b,}末尾的空字符串會被保留。而如果字符串本身就是空的可能返回空數(shù)組或者包含空字符串具體行為在不同版本上還有過變化——這類邊緣 case最好在應(yīng)用層先做空值判斷別指望數(shù)據(jù)庫替你兜底。2.4 別忽略好用的小工具regexp_substr、regexp_instr、regexp_count這幾兄弟不是 PostgreSQL 原生的但 PostgreSQL 15 開始新引入的regexp_substr、regexp_instr、regexp_count真的是大大提升了日常效率。以前大家想取子串得regexp_matches配合下標(biāo)或者substring配合正則現(xiàn)在可以寫得更像 Oracle-- 從字符串中提取第一個(gè)數(shù)字序列 SELECT regexp_substr(訂單號A12345總價(jià)88.5, \d(\.\d)?); -- 返回12345 -- 找到第一個(gè)數(shù)字出現(xiàn)的位置 SELECT regexp_instr(訂單號A12345總價(jià)88.5, \d); -- 返回4這里是字符位置從1開始 -- 統(tǒng)計(jì)數(shù)字序列出現(xiàn)了幾次 SELECT regexp_count(A1 B22 C333, \d); -- 返回3雖然這三個(gè)函數(shù)在純函數(shù)式寫法上比早期的 PostgreSQL 版本更友好但如果你還在用 PostgreSQL 14 或更早版本就得老老實(shí)實(shí)回到regexp_matches和strpos的老路上去。要注意函數(shù)的具體可用版本是硬約束我這篇內(nèi)容默認(rèn)以 PostgreSQL 15/16 為主參考老版本用戶看到函數(shù)不存在別慌看自己服務(wù)器的version()先。2.5 substring 里的正則這是被忽視的第一入口substring(string from pattern)是 PostgreSQL 專門用來做提取第一個(gè)匹配片段的語法糖。日常提取 IP、時(shí)間戳、訂單號它比regexp_matches更簡潔因?yàn)樗菢?biāo)量返回不會給你搞出集合來SELECT substring(訪問IP: 192.168.1.1 時(shí)間: 2024-05-01 12:00:00 FROM (\d{1,3}(\.\d{1,3}){3})); -- 返回192.168.1.1注意substring只返回第一個(gè)捕獲組。如果你的正則里沒有捕獲組就返回整個(gè)匹配串。這種行為跟regexp_matches的數(shù)組元素邏輯類似但省去了集合提取的煩惱。基于這個(gè)特性它非常適合出現(xiàn)在SELECT列表里直接生成清洗后的列。3. 實(shí)戰(zhàn)從一段真實(shí)日志里把關(guān)鍵字段摳出來3.1 場景定義與數(shù)據(jù)準(zhǔn)備假設(shè)我們要處理一條線上服務(wù)日志格式長這樣[2024-05-01 23:45:04.213] INFO order-service - order created user9527 amount199.00 item無線鼠標(biāo) ip10.12.34.56目標(biāo)提取日志時(shí)間、日志級別、用戶ID、金額、IP 地址。這種東西你當(dāng)然可以用split_part一層一層剝但日志字段順序固定、長度不定的情況下split_part寫起來非常痛苦比如 item 里可能還有空格和引號直接按空格拆會全亂。正則在這里的優(yōu)勢是按模式定位而不是按位置拆分。建表模擬數(shù)據(jù)CREATE TABLE log_tmp (line text); INSERT INTO log_tmp VALUES ([2024-05-01 23:45:04.213] INFO order-service - order created user9527 amount199.00 item無線鼠標(biāo) ip10.12.34.56), ([2024-05-01 23:46:11.887] ERROR order-service - order failed user9527 amount199.00 item無線鍵盤 ip10.12.34.56);3.2 一條 SQL 完成多字段提取下面這段 SQL 是核心示例我建議你在自己的環(huán)境里跑一遍看效果SELECT substring(line FROM \[(\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}\.\d{3})\]) AS log_time, substring(line FROM \] (\w) ) AS log_level, substring(line FROM user(\d)) AS user_id, substring(line FROM amount(\d\.\d{2})) AS amount, substring(line FROM ip(\d{1,3}(\.\d{1,3}){3})) AS ip FROM log_tmp;重點(diǎn)講幾個(gè)細(xì)節(jié)。第一log_time里我用了\[和\]轉(zhuǎn)義括號因?yàn)榉嚼ㄌ栐谡齽t里有字符類語義不能直接裸寫。第二log_level的\] (\w)我在右括號后放了空格然后匹配單詞字符。日志里INFO和order-service之間的空格數(shù)可能不固定所以這個(gè)寫法定死了必須緊跟一個(gè)空格——如果空格數(shù)變了就會漏。更穩(wěn)妥的寫法是\]\s(\w)\s。第三IP 的正則(\d{1,3}(\.\d{1,3}){3})這里外層的括號是捕獲組用來提取整個(gè) IP內(nèi)層(\.\d{1,3}){3}是為了表達(dá)點(diǎn)加三位數(shù)字重復(fù)三次。但是請注意substring會優(yōu)先返回第一個(gè)捕獲組我們這里外層的(...)是第一個(gè)捕獲組所以能正確返回完整 IP。如果你不小心把內(nèi)層捕獲組寫到前面去了返回的就是最后一組三位數(shù)字了。3.3 提取結(jié)果為空先查正則的命中前提不少人寫完后發(fā)現(xiàn)某個(gè)字段一直返回NULL第一反應(yīng)是數(shù)據(jù)是不是不對。但更常見的原因是正則不夠?qū)捤伞１热缟厦嫣崛?IP 的正則我用了\d{1,3}(\.\d{1,3}){3}但如果日志里 IP 后面還跟著別的內(nèi)容比如ip10.12.34.56,這個(gè)正則其實(shí)也能匹配因?yàn)閧3}只約束了分組重復(fù)次數(shù)后面有沒有逗號不影響匹配成功。問題往往出在錨點(diǎn)缺失導(dǎo)致匹配到了錯誤的子串。比如你提取時(shí)間是\[(\d{4})而一行日志里可能在 message 部分又出現(xiàn)了一個(gè)年份——那substring會提取出第一個(gè)匹配也就是時(shí)間戳里的年份這沒問題但如果你實(shí)際想提取的是消息里埋的年份這個(gè)寫法就廢了。一條實(shí)用的排錯思路先用不帶捕獲組的寬正則看能不能匹配到目標(biāo)再用regexp_matches(..., g)把所有匹配結(jié)果拉出來看一遍確認(rèn)你的第一個(gè)匹配到底命中在哪個(gè)位置。3.4 數(shù)據(jù)清洗實(shí)戰(zhàn)一行命令去掉 JSON 轉(zhuǎn)義和 HTML 標(biāo)簽再分享一個(gè)我在實(shí)際項(xiàng)目里高頻使用的清洗場景修復(fù)從上游接口寫入的臟數(shù)據(jù)。比如字段里混入了\n、\t這種可見轉(zhuǎn)義反斜杠加字母而不是真正的換行符或者混進(jìn)了p、br這類 HTML 殘留。一條regexp_replace就能處理UPDATE articles SET body regexp_replace( regexp_replace(body, \\[ntr], , g), [^], , g ) WHERE body ~ \\[ntr]|[^];這里有個(gè)小細(xì)節(jié)我要匹配反斜杠本身所以在 SQL 字符串里要寫\\[ntr]。如果用標(biāo)準(zhǔn)字符串寫法\n會被 PostgreSQL 解析成真正的換行符那就完全不是這個(gè)意圖了。很多新手第一次寫正則替換都會被這個(gè)反斜杠轉(zhuǎn)義搞崩潰。記住一句口訣SQL 字符串里的兩個(gè)反斜杠在正則引擎眼里就是一個(gè)反斜杠如果你想匹配一個(gè)反斜杠加一個(gè)字母nSQL 里得寫三個(gè)甚至四個(gè)反斜杠。實(shí)際上在 SQL 中匹配單個(gè)反斜杠最穩(wěn)的寫法是\\\\因?yàn)?PostgreSQL 標(biāo)準(zhǔn)字符串里\\變成\正則引擎收到\后如果后面不接特殊字符有時(shí)會報(bào)錯或行為奇怪。所以我的習(xí)慣是能用[\\]就不要用\\。比如匹配 ASCII 轉(zhuǎn)義序列我會寫成-- 匹配一個(gè)反斜杠后面跟著 n/t/r regexp_replace(body, [\\][ntr], , g)這里[\\]在正則里是一個(gè)字符類表示字符集中的反斜杠干凈利落不折騰。4. 性能優(yōu)化別讓正則變成慢查詢元兇4.1 正則不用索引也不一定全對很多人一聽正則就條件反射式地說無法走索引。這個(gè)說法過于絕對。確實(shí)~ abc.*這種寫法在原理上很難利用普通 B-tree 索引因?yàn)閮?yōu)化器不知道匹配的字符串落在一個(gè)有序區(qū)間內(nèi)的哪個(gè)范圍。但如果你只要前綴匹配那么正則也能走索引。比如name ~ ^abc優(yōu)化器可以將其轉(zhuǎn)換成一個(gè)范圍條件從abc開始到abd結(jié)束從而利用 B-tree 索引。不過這種轉(zhuǎn)換并不是 PostgreSQL 的默認(rèn)操作實(shí)踐中你最好還是用name LIKE abc%這種寫法優(yōu)化器對這種寫法有明確的索引支持。另外如果正則匹配的格式是前綴固定 后綴可變且這個(gè)前綴有一定的區(qū)分度可以考慮表達(dá)式索引。比如CREATE INDEX idx_log_user_prefix ON log_tmp ((substring(line FROM user(\d))));但這種索引只能配合完全一致的表達(dá)式才能命中為了這一小撮查詢建索引性價(jià)比通常不高。更常見的方式是給待匹配的字段解構(gòu)出獨(dú)立列比如用戶ID、訂單號、狀態(tài)碼對這些離散列建普通 B-tree 索引查詢時(shí)直接等值匹配徹底繞開正則。這也是能拆列就別正則的核心理由——正則不是萬金油很多時(shí)候先做一次 ETL 把字段拆好后續(xù)查詢性能輕松漲一個(gè)量級。4.2 大表 LIKE 和正則的性能對比實(shí)驗(yàn)我自己測過一張百萬行級的表字段remark查詢WHERE remark ~ 訂單號A[0-9]{5}全表掃描耗時(shí)大概在 800ms 上下取決于平均字段長度。而同樣的查詢需求如果上游在寫入時(shí)已經(jīng)拆出了order_id列并建索引WHERE order_id LIKE A%或等值匹配耗時(shí)直接降到幾十毫秒。所以結(jié)論很清晰對大數(shù)據(jù)量的在線查詢正則過濾能避則避不能避也盡量把正則限定在全表掃描可控的小表上或者放到離線分析場景。當(dāng)然也有場景是正則跑得比你想象中快的。比如你給一個(gè)text字段建了 GIN 索引配合pg_trgm模塊這時(shí)候某些正則也能借助三元組索引加速。但這玩意兒配置起來麻煩且它只能加速部分模式比如模式里有固定字符串片段如果你的正則全是.*這種GIN 也救不了你。4.3 避免在 JOIN 條件或 GROUP BY 里無腦用正則這是我踩過最深的一個(gè)坑把正則寫進(jìn) JOIN 的ON條件里做模糊匹配。比如SELECT * FROM a JOIN b ON a.code ~ (^ || b.pattern || $);這種寫法的執(zhí)行計(jì)劃必然是先做嵌套循環(huán)然后對每一對組合跑一次正則。外層一百行、內(nèi)層一萬行就是一百萬次正則編譯加匹配查詢直接卡死?，F(xiàn)實(shí)里這么干的需求往往是想做規(guī)則表匹配但更好的做法是先判斷你的規(guī)則能不能拆成離散字段不能的話至少把規(guī)則表加個(gè)前綴索引用LIKE把候選集縮小到幾十行再對剩下的做正則精匹配。4.4 正則緩存和防呆寫法PostgreSQL 的正則引擎會緩存最近使用的正則模式允許的緩存大小是regex_cache相關(guān)參數(shù)控制的。但如果你一條 SQL 里動態(tài)生成了成千上萬個(gè)不同的正則比如上面 JOIN 場景緩存根本頂不住反而會因?yàn)轭l繁淘汰導(dǎo)致額外開銷。所以最佳實(shí)踐是同一個(gè)模式盡量全局復(fù)用不要拼接無謂的字符串。舉個(gè)反面教材-- 不要這樣 WHERE remark ~ (SELECT pattern FROM rule WHERE id 1)每行執(zhí)行時(shí)都去查規(guī)則表再重新編譯正則性能爛透。正確做法是先取出來變成參數(shù)或者拆成兩步-- 先拿到 pattern再作為綁定變量傳給查詢 PREPARE check_remark(text) AS SELECT * FROM t WHERE remark ~ $1; EXECUTE check_remark(訂單號A\d{5});PREPARE的好處是讓 PostgreSQL 把正則模式作為參數(shù)緩存下來重復(fù)執(zhí)行時(shí)不用每行都重新編譯正則省下的開銷在高頻查詢下非?？捎^。5. 常見問題與坑位速查5.1 不同版本正則函數(shù)有多大差異PostgreSQL 不同版本之間正則相關(guān)的能力差異還挺大的。版本變更重點(diǎn)PostgreSQL 10 及以前只有~系列操作符、substring、regexp_matches、regexp_replace、regexp_split_to_*PostgreSQL 11 / 12 / 13 / 14無大變化都是內(nèi)引擎增強(qiáng)PostgreSQL 15新增regexp_substr、regexp_instr、regexp_countPostgreSQL 16對regexp_*系列函數(shù)的行為做了細(xì)節(jié)修正并優(yōu)化了部分邊界情況如果你在 PostgreSQL 14 上跑regexp_count會直接報(bào)function regexp_count(text, text) does not exist。這不是你的 SQL 寫錯了是版本不支持。所以遇到函數(shù)不存在先查current_setting(server_version)別折騰半天正則表達(dá)式最后發(fā)現(xiàn)是版本問題。5.2 flags 參數(shù)到底怎么寫regexp_*系列函數(shù)的最后一個(gè)可選參數(shù)是 flags常用的有flag含義g全局匹配返回所有匹配位置i忽略大小寫n讓.也能匹配換行符新行m多行模式^和$匹配每行的開頭和結(jié)尾s讓空白字符按\s匹配非默認(rèn)行為視正則有差異最容易被忽略的是n和m的區(qū)別。m是讓^/$按行生效n是讓.能跨行。如果你處理的是多行日志文本想在每一行里提取某個(gè)字段就得用m。比如SELECT regexp_matches( E第一行 error1\n第二行 error2\n第三行 error3, ^.*error(\d)$, gm );注意這里g和m連寫在一起。如果沒有m^只會匹配整個(gè)字符串的開頭結(jié)果就只有一個(gè)1。5.3 正則里的括號、反斜杠、$ 符號的轉(zhuǎn)義迷局這是幾乎所有正則新手都會撞的墻。在 PostgreSQL 字符串里寫正則遇到的轉(zhuǎn)義層級是雙重的先是 SQL 字符串層再是正則層。舉例你要匹配字符串里的美元符號$在正則里$是錨點(diǎn)要匹配字面$需要轉(zhuǎn)義成\$在 SQL 標(biāo)準(zhǔn)字符串里反斜杠需要寫成\\所以你在 SQL 里要寫\\$看起來是不是頭大我這里給你一個(gè)治本的方法優(yōu)先使用 PostgreSQL 的 dollar-quoted 字符串常量來寫正則或者用E轉(zhuǎn)義字符串。比如SELECT $100 ~ E\\$\\d; -- true SELECT $100 ~ $\\d; -- 這個(gè)可能不會按你預(yù)期工作實(shí)際上更穩(wěn)的習(xí)慣是正則里盡量用字符類避免反斜杠轉(zhuǎn)義。匹配$可以寫成[$]匹配.可以寫成[.]匹配*可以寫成[*]。字符類內(nèi)部的這些特殊字符多數(shù)不需要轉(zhuǎn)義寫出來也更安全。5.4 常見問題速查表我把平時(shí)工作里被問得最多的場景整理成一張速查表遇到可以直接抄需求推薦寫法判斷字符串是否以數(shù)字開頭str ~ ^\d判斷字符串是否只包含字母str ~ ^[a-zA-Z]$判斷是否包含連續(xù)三個(gè)以上空格str ~ {3,}提取第一個(gè)時(shí)間戳標(biāo)準(zhǔn)格式substring(str FROM \d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2})提取所有郵箱regexp_matches(str, [a-zA-Z0-9._%-][a-zA-Z0-9.-]\.[a-zA-Z]{2,}, g)去掉字符串首尾空格regexp_replace(str, ^\s按逗號拆行但忽略括號內(nèi)逗號regexp_split_to_table(str, ,(?![^()]*\)))手機(jī)號中間四位打碼regexp_replace(phone, ^(\d{3})\d{4}(\d{4})$, \1****\2)需要特別提醒的是上面這些寫法的性能只適合中小數(shù)據(jù)量百萬行以內(nèi)的單表過濾。上了千萬行還這么查神仙也救不了。你得回到 4 節(jié)說的思路先拆列、再索引、最后才正則。5.5 定位問題的方法論從結(jié)果反推正則模式當(dāng)你遇到結(jié)果字段為 NULL或者匹配的行比預(yù)期多不要急著反復(fù)修改正則。我的建議是分三步走第一步只跑正則不帶業(yè)務(wù)條件。比如SELECT line, regexp_matches(line, 這里寫你的模式, g) FROM log_tmp LIMIT 20;先直接看它匹配出了什么。第二步逐段拆解。如果你的正則寫了 60 個(gè)字符把它拆成三個(gè)小片段分別測。比如先測\d{4}-\d{2}-\d{2}再測 \d{2}:\d{2}:\d{2}最后拼起來。這一步能快速定位是前半段錯了還是后半段錯了。第三步確認(rèn)邊界字符。看原始數(shù)據(jù)里是不是混入了不可見字符比如\r回車、零寬空格、全角空格等。用SELECT line, encode(line::bytea, escape) FROM log_tmp;可以直觀看到隱藏字符。很多時(shí)候字段顯示沒問題一查編碼發(fā)現(xiàn)里面藏著\r正則的$就匹配不上了。6. 經(jīng)驗(yàn)之談?wù)齽t不是銀彈但確實(shí)是瑞士軍刀最后講一點(diǎn)我在實(shí)戰(zhàn)里的體會。正則表達(dá)式在 PostgreSQL 里是極度好用的瑞士軍刀但它不是銀彈。日常開發(fā)里我給自己定了三條軍規(guī)。第一能拆列就不要繞正則。如果你的業(yè)務(wù)字段本身有結(jié)構(gòu)比如訂單號永遠(yuǎn)是前綴數(shù)字那就應(yīng)該在建表時(shí)拆成order_prefix和order_no兩列。維護(hù)成本其實(shí)很低但查詢性能和可讀性提升巨大。正則適合偶發(fā)清洗和復(fù)雜提取不適合放進(jìn)高頻查詢鏈路里做核心過濾。第二正則寫完一定要加注釋。SQL 里的正則本來就難讀沒人愿意半個(gè)月后回來猜你這串(?![^()]*\))到底想干嘛。我會在字段邊上留一行注釋寫明意圖和輸入樣例。有些公司還會把這些常用正則收集成一張正則模板表新同事直接復(fù)用避免重復(fù)踩坑。第三上線前用真實(shí)數(shù)據(jù)量做一次性能驗(yàn)證。在小表上這條 SQL 跑了 50ms 不叫快千萬行表上跑了 50ms 才是真快。正則引擎的開銷跟字符串長度和復(fù)雜度密切相關(guān)不可控因素很多。我的習(xí)慣是先把線上數(shù)據(jù)撈一小部分回來建表造個(gè)近似量級的數(shù)據(jù)壓測一下再放到生產(chǎn)環(huán)境跑。尤其是那種會自動拼正則的規(guī)則引擎上線前不驗(yàn)證等于埋雷。PostgreSQL 的正則能力比很多數(shù)據(jù)庫都要完整從基礎(chǔ)操作符到高級函數(shù)覆蓋了從判斷、提取、替換到拆分的所有場景。但它也有自己的語法邊界、版本邊界和性能邊界。把這些邊界摸透你才能在寫不出來和跑不動之間找到那條優(yōu)雅的平衡線。這篇內(nèi)容里提到的每種寫法、每個(gè)坑位都是我實(shí)際踩過或幫別人排查過的場景。你不需要一次性全部背下來但建議收藏起來等真遇到從一個(gè)長字符串里摳字段的需求時(shí)再來對照著用。