義符詳解：從通配符陷阱到性能優(yōu)化)
有一回做投訴工單報表要從一張幾萬行的表里篩出“產(chǎn)品名稱中帶 5% 字樣”的記錄。我順手寫了WHERE product_name LIKE %5%%執(zhí)行完當場愣住——全表幾乎全中。第一反應(yīng)是數(shù)據(jù)臟排查了大半天最后才發(fā)現(xiàn)鍋根本不在數(shù)據(jù)而是這條 SQL 里的%太“自作聰明”了。像“字段中含有轉(zhuǎn)義符”這類坑凡是寫過模糊匹配查詢的人基本都會踩一次區(qū)別只是有人半小時就繞出來了有人會為此懷疑人生。這篇就專門聊透這個事LIKE 里的%和_到底在干嘛、轉(zhuǎn)義符是怎么回事、不同數(shù)據(jù)庫怎么處理、工程代碼里怎么防御以及這類查詢的性能和索引陷阱。內(nèi)容以 MySQL 為主線兼顧 Oracle、SQL Server、PostgreSQL最后附上踩坑記錄和排查速查表。適合正在做數(shù)據(jù)查詢、報表統(tǒng)計、接口開發(fā)的朋友尤其是被模糊查詢結(jié)果弄到一頭霧水的那幾位。1. 模糊匹配為什么會“栽”在轉(zhuǎn)義符上1.1 LIKE 里 % 和 _ 的真實身份先明確一個基本概念。LIKE做模糊匹配時模式串里有兩個特殊字符是通配符%匹配任意長度的任意字符包括空字符串。_匹配且僅匹配任意單個字符。生活化類比%相當于你搜索文件時輸入的*只要文件名里有一段對得上整串都能匹配_則更像下棋時的“一個空格”必須剛好有一個字符占住這個位置多一個少一個都不行。問題在于如果業(yè)務(wù)字段的數(shù)據(jù)本身包含%或_比如“洗面奶 100% 正品”或者編碼規(guī)范里常見的KPI_2024_考核表那么這些字符在 LIKE 模式里會被當成通配符來解釋查出來的結(jié)果自然不是字面含義。這是“字段中含有轉(zhuǎn)義符”的核心矛盾數(shù)據(jù)里的特殊字符和查詢語法的特殊字符撞車了。解決思路只有一個——告訴數(shù)據(jù)庫這里出現(xiàn)的%或_不是通配符是普通字符。這個動作就叫“轉(zhuǎn)義”。1.2 一個典型的誤匹配現(xiàn)場用數(shù)據(jù)說話。假設(shè)有一張投訴記錄表結(jié)構(gòu)很簡單CREATE TABLE complaint_log ( id INT PRIMARY KEY, product_name VARCHAR(100), deal_rate VARCHAR(20) ); INSERT INTO complaint_log VALUES (1, 洗面奶促銷裝100%正品, 15%), (2, 洗發(fā)水500ml, 5%), (3, KPI_2024_考核表, NULL), (4, KPX2024考核表, NULL);業(yè)務(wù)需求查出所有商品名稱里帶KPI_2024的記錄。直覺寫法SELECT * FROM complaint_log WHERE product_name LIKE %KPI_2024%;這條 SQL 不會報錯但結(jié)果里第 3 條和第 4 條都會出來。第 4 條KPX2024考核表并沒有_但因為_被當成“任意單個字符”KPI_2024中的_恰好匹配了X整條就命中了。再看%的例子。需求是查“包含 5% 這個字面值”的記錄SELECT * FROM complaint_log WHERE product_name LIKE %5%%;這個寫法更離譜第二個%是通配符等價于%5%于是只要字段里出現(xiàn)過數(shù)字 5就會全部命中。表里第 1、2 條都會查出來甚至某條記錄里帶個“5 元優(yōu)惠券”也會被撈上來。這兩段代碼看起來人畜無害實際跑起來全是坑。根本原因就是沒做轉(zhuǎn)義。1.3 不同數(shù)據(jù)庫的默認轉(zhuǎn)義行為這里有個特別重要的點不同數(shù)據(jù)庫對 LIKE 中轉(zhuǎn)義符的默認處理是不一樣的。我整理了一個對照表方便你排查時對號入座。數(shù)據(jù)庫默認轉(zhuǎn)義字符需要顯式 ESCAPE 嗎補充說明MySQL\是默認轉(zhuǎn)義符可以不寫但受NO_BACKSLASH_ESCAPES模式影響模式不統(tǒng)一時容易出隱性 bugSQL Server無默認轉(zhuǎn)義符必須寫ESCAPE否則%和_永遠是被通配還支持[]做單字符匹配容易混Oracle無默認轉(zhuǎn)義符必須寫ESCAPE字符串字面量里的\處理也容易混亂PostgreSQL\是默認轉(zhuǎn)義符建議顯式寫ESCAPE受standard_conforming_strings影響字面量寫法易混淆所以“為什么我在 MySQL 里寫了%\_%有效在 SQL Server 里卻什么都不匹配”這類問題答案往往就在這個表里。SQL Server 不認反斜杠轉(zhuǎn)義你必須顯式給出ESCAPE比如WHERE product_name LIKE %KPI\_2024\_% ESCAPE \;MySQL 里默認能直接用反斜杠但如果數(shù)據(jù)庫處于NO_BACKSLASH_ESCAPES模式反斜杠也會失效。因此最穩(wěn)妥的做法是不要依賴數(shù)據(jù)庫的默認行為每條 LIKE 都顯式聲明轉(zhuǎn)義符。2. 正確姿勢ESCAPE 子句與等價替代方案2.1 ESCAPE 子句的完整寫法SQL 標準提供了一套通用的解決方案在 LIKE 模式末尾加ESCAPE子句自定義一個轉(zhuǎn)義字符。語法格式WHERE 字段 LIKE 模式 ESCAPE 轉(zhuǎn)義字符;轉(zhuǎn)義字符的作用是當它出現(xiàn)在%或_前面時后面的通配符就被“降級”為普通字符。用前面的5%場景舉例SELECT * FROM complaint_log WHERE product_name LIKE %5/%% ESCAPE /;拆開看這段模式第一個%是通配符中間的/是自定義轉(zhuǎn)義符/%表示“字面意義上的百分號”最后的%又是通配符。整條表達的意思是字段里只要包含字符串5%無論前后有沒有其他內(nèi)容都算命中。匹配下劃線的寫法同理SELECT * FROM complaint_log WHERE product_name LIKE %KPI/_2024/_% ESCAPE /;這里要注意三點。第一轉(zhuǎn)義符與后面的%或_之間不能有空格必須連寫。因為空格本身是普通字符一旦插入空格轉(zhuǎn)義邏輯就斷了。第二轉(zhuǎn)義符只對它緊跟著的一個字符生效。如果要匹配兩個連續(xù)的特殊字符比如字符串100%%模式就要寫成%100/%%/%一個字符一個轉(zhuǎn)義位。第三如果字段本身包含反斜杠比如 Windows 路徑C:\Users\test推薦用/這類非歧義字符做轉(zhuǎn)義符避免和字符串字面量級別的反斜杠轉(zhuǎn)義疊加否則寫兩層轉(zhuǎn)義很容易數(shù)錯斜杠。2.2 用字符串函數(shù)代替 LIKE繞開通配符問題如果不需要用%做真正的模糊匹配只需要“判斷字段中是否包含某個固定文本”那更干凈的做法是用字符串定位函數(shù)。這些函數(shù)搜索的是普通字符串不存在通配符語義也就不需要轉(zhuǎn)義。四個主流數(shù)據(jù)庫的寫法-- MySQL SELECT * FROM complaint_log WHERE LOCATE(5%, product_name) 0; -- Oracle SELECT * FROM complaint_log WHERE INSTR(product_name, 5%) 0; -- SQL Server SELECT * FROM complaint_log WHERE CHARINDEX(5%, product_name) 0; -- PostgreSQL SELECT * FROM complaint_log WHERE POSITION(5% IN product_name) 0;這套方案的優(yōu)點非常直觀不用管%、_、\搜索什么就是什么。適合“完全匹配子串但不關(guān)心前后綴”的場景比如在黑名單詞表里查包含違禁%的數(shù)據(jù)。缺點也有不像LIKE那樣能表達復雜的模糊模式比如“以 A 開頭、中間包含 B、以 C 結(jié)尾”這種組合用函數(shù)就不好寫還得回到 LIKE 加正則那套。2.3 正則表達式方案適合復雜場景的兜底既然 LIKE 的通配符和轉(zhuǎn)義符容易出錯另一個思路是換成正則表達式。在正則的世界里%和_是普通字符沒有通配符的語義所以不需要對它們轉(zhuǎn)義。以 MySQL 8 和 Oracle 的REGEXP_LIKE為例想查包含字面100%正品的記錄SELECT * FROM complaint_log WHERE REGEXP_LIKE(product_name, 100%正品);這里%在正則里就是百分號本身不需要像 LIKE 那樣加轉(zhuǎn)義。但別高興得太早正則有另一套自成體系的元字符.、*、[]、()、|等它們才是需要轉(zhuǎn)義的對象。比如要搜KPI_2024_考核表這個需求用正則是正常查但若要搜KPI.2024中的字面點號正則里就得寫成KPI\.2024。簡單總結(jié)只搜固定子串優(yōu)先用字面函數(shù)LOCATE/INSTR/CHARINDEX。需要復雜模式、且字段里%、_出現(xiàn)頻繁時用正則表達式反而省事。LIKE 加ESCAPE則適合需求簡單但就是想用 LIKE、團隊習慣統(tǒng)一的場景。3. 工程代碼里如何穩(wěn)妥處理“轉(zhuǎn)義符”查詢3.1 參數(shù)化查詢不等于自動轉(zhuǎn)義很多開發(fā)者在代碼里用參數(shù)化查詢以為把用戶輸入原樣塞進%...%就萬事大吉。這是個很致命的誤解。參數(shù)化查詢解決的是 SQL 注入問題它確保用戶輸入被當成“值”而不是“SQL 片段”來解析。但在LIKE內(nèi)部%和_依然會被數(shù)據(jù)庫解釋為通配符。舉個 Python 操作 MySQL 的例子keyword 5% sql SELECT * FROM complaint_log WHERE product_name LIKE %s params (f%{keyword}%,) # 錯誤的做法% 還是通配符這樣做執(zhí)行后5%中的%依然通配一切結(jié)果還是會把所有含 5 的記錄全部撈出來。正確做法是在拼接 LIKE 模式之前先對關(guān)鍵詞做一次轉(zhuǎn)義處理def escape_like(keyword: str) - str: # 轉(zhuǎn)義順序有講究先反斜杠再 %再 _ return keyword.replace(\\, \\\\).replace(%, \\%).replace(_, \\_) keyword 5% escaped escape_like(keyword) # 得到 5\% sql SELECT * FROM complaint_log WHERE product_name LIKE %s ESCAPE \\\\ params (f%{escaped}%,)這里ESCAPE \\\\對應(yīng)數(shù)據(jù)庫里的反斜杠轉(zhuǎn)義符代碼里寫多少個斜杠經(jīng)常把人繞暈。所以我在工程里更推薦自定義一個不容易出錯的轉(zhuǎn)義符def escape_like(keyword: str) - str: return keyword.replace(/, //).replace(%, /%).replace(_, /_) sql SELECT * FROM complaint_log WHERE product_name LIKE %s ESCAPE / params (f%{escape_like(keyword)}%,)這個方案一眼就能看明白用戶輸入里原來的/被寫成//%寫成/%_寫成/_。查詢時使用ESCAPE /可讀性比數(shù)反斜杠好太多。3.2 ORM 框架里同樣要預(yù)先處理ORM 并不負責幫你轉(zhuǎn)義 LIKE 的特殊字符。比如 Django 的 ORM# 看起來沒問題實際生成 SQL 是 LIKE %5%%照樣全表撈 ComplaintLog.objects.filter(product_name__contains5%)__contains只是幫你拼了個%...%內(nèi)部的%該通配還通配。正確的做法是用extra或RawSQL把轉(zhuǎn)義邏輯顯式寫進 SQL 片段from django.db.models.expressions import RawSQL escaped escape_like(5%) qs ComplaintLog.objects.extra( where[product_name LIKE %s ESCAPE /], params[f%{escaped}%], )MyBatis 場景要區(qū)分#{}和${}。#{}是參數(shù)綁定安全但不能自動處理 LIKE 通配符${}是文本拼接有注入風險不適合直接拼用戶輸入。穩(wěn)妥的寫法是在 XML 中用CONCAT并且提前在 Java 服務(wù)層完成轉(zhuǎn)義select idsearch resultType... SELECT * FROM complaint_log WHERE product_name LIKE CONCAT(%, #{escapedKeyword}, %) ESCAPE / /selectescapedKeyword在 Service 層已經(jīng)調(diào)用escape_like()處理過。這么做既避免注入又防住了通配符穿透。JPA 的Query同理LIKE :keyword里的keyword必須攜帶轉(zhuǎn)移后的模式。3.3 動態(tài)拼 SQL 時的防守底線如果團隊里還有人習慣用字符串拼接的方式生成查詢 SQL一定要立幾條規(guī)矩所有進入 LIKE 模式的用戶輸入必須先走統(tǒng)一的轉(zhuǎn)義函數(shù)。轉(zhuǎn)義函數(shù)里必須包含對轉(zhuǎn)義符本身、%、_三者的處理缺一不可。拼接時LIKE必須顯式帶ESCAPE不允許依賴數(shù)據(jù)庫默認行為。盡量限制輸入長度比如 50 個字符以內(nèi)防止有人把超長正則或通配符變體塞進來。另外一個時常被忽略的坑是字段名本身帶特殊字符或數(shù)據(jù)庫保留字。比如熱詞里提到的“mysql 表中字段為關(guān)鍵字”如果字段命名是order、group、desc這類保留字查詢時必須用反引號包裹SELECT * FROM complaint_log WHERE order 1;這類問題和轉(zhuǎn)義符屬于同一大類的“特殊字符處理”在開發(fā)規(guī)范里值得專門加一條字段命名盡量避開保留字和特殊符號實在避不開要統(tǒng)一標識符的引用方式。4. 含有轉(zhuǎn)義符的模糊查詢性能和索引怎么兼顧4.1 前導通配符會讓索引失效聊完正確性必須說說性能。很多人在排查轉(zhuǎn)義符問題時往往忽略了 LIKE 查詢本身就存在索引陷阱。WHERE product_name LIKE %xxx%這種前后都帶通配符的寫法數(shù)據(jù)庫無法利用普通 B 樹索引只能走全表掃描。原因很簡單優(yōu)化器沒法確定匹配的起點不知道應(yīng)該從索引樹的哪個位置開始掃描。即使你加了ESCAPE把特殊字符轉(zhuǎn)義成了字面量這個性能問題也不會消失。ESCAPE解決的是匹配結(jié)果的正確性索引失效的根源是前導%本身。如果你的查詢模式是固定的前綴比如LIKE abc%那普通索引還能用上。但“字段中間含有特殊字符”這類需求往往逃不掉全表掃描。這時就需要靠下面兩種手段兜底。4.2 用生成列或函數(shù)索引優(yōu)化MySQL 8.0 以上支持生成列?？梢蕴崆鞍炎侄沃械奶厥庾址麆冸x出來存成獨立列再對這個列建索引查詢時直接走索引ALTER TABLE complaint_log ADD COLUMN product_name_clean VARCHAR(100) GENERATED ALWAYS AS (REPLACE(REPLACE(product_name, %, ), _, )) STORED; ALTER TABLE complaint_log ADD INDEX idx_clean (product_name_clean);這樣業(yè)務(wù)代碼里查“包含 5% 的記錄”時可以先按干凈列過濾或者直接對干凈列做常規(guī)查詢效率和正確性都得到了保障缺點是占存儲空間。Oracle 支持函數(shù)索引可以針對INSTR這類函數(shù)建索引CREATE INDEX idx_instr_rate ON complaint_log (INSTR(deal_rate, %));查詢時用WHERE INSTR(deal_rate, %) 0優(yōu)化器就有機會走函數(shù)索引。PostgreSQL 的表達式索引類似直接在查詢列上建立表達式CREATE INDEX idx_instr ON complaint_log ((POSITION(% IN deal_rate)));4.3 視圖解決不了性能問題熱詞里有個高頻問題“視圖可以加快查詢速度嗎”這個誤解在模糊查詢場景里特別常見。有人把WHERE product_name LIKE %5%%封裝成一個視圖指望查詢視圖能變快?？梢灾苯咏o你結(jié)論視圖只是把一段 SQL 保存成了一個命名對象它不存儲數(shù)據(jù)也沒有自己的索引。查詢視圖等價于執(zhí)行視圖中嵌套的那條 SQL掃描成本和直接寫 LIKE 沒有任何區(qū)別。真正能提速的是合理的前綴匹配、覆蓋索引、全文索引或者干脆把特殊字符剝離后建生成列。想靠視圖解決模糊查詢性能問題方向就錯了。5. 常見問題與實戰(zhàn)排查小抄5.1 快速對照表為了讓你遇到問題時能快速定位我整理了下面這張表。問題現(xiàn)象可能原因處理辦法查詢結(jié)果明顯過多字段里的%被當成通配符使用ESCAPE轉(zhuǎn)義為字面量明明字段含_條件就是匹配不上_被當成單字符占位符對_轉(zhuǎn)義或改用LOCATE等函數(shù)MySQL 里寫了\%無效數(shù)據(jù)庫處于NO_BACKSLASH_ESCAPES模式改用顯式ESCAPE /SQL Server 里\%無效SQL Server 無默認轉(zhuǎn)義符必須寫ESCAPE \或用[]反斜杠路徑字段查詢不到字符串字面量轉(zhuǎn)義與 LIKE 轉(zhuǎn)義疊加換用非\的轉(zhuǎn)義符或參數(shù)綁定用戶輸入%導致全表匹配參數(shù)化查詢未處理 LIKE 通配符入?yún)⑶敖y(tǒng)一調(diào)用escape_like模糊查詢很慢前導%導致索引失效生成列/函數(shù)索引/全文索引把模糊查詢封裝成視圖想提速視圖不存儲數(shù)據(jù)不能加速改為對索引列做前綴查詢5.2 三個真實踩坑現(xiàn)場第一個坑業(yè)務(wù)編碼里大量使用下劃線。商品編碼規(guī)范是SZ_2024_001這類格式需求是“查詢 2024 年所有深圳商品”。同事寫的是LIKE %SZ_2024%結(jié)果把SZ02024、SZ12024都撈出來了因為這些_都可以被當成任意單字符。排查時如果不先用一個小數(shù)據(jù)集驗證 LIKE 語義很容易誤判為數(shù)據(jù)質(zhì)量問題。第二個坑JSON 字段里做模糊匹配。有人把整個 JSON 字符串存到字段里查詢時直接LIKE %type:A%。雖然 JSON 里的雙引號和冒號不是通配符但如果 JSON 數(shù)據(jù)里恰好含%或_一樣中招。更麻煩的是 JSON 字符串中的反斜杠轉(zhuǎn)義會疊加。這種情況不要用 LIKE直接用數(shù)據(jù)庫的 JSON 函數(shù)比如 MySQL 的JSON_EXTRACT既安全又高效。第三個坑SQL Server 的方括號。SQL Server 的 LIKE 支持[]做字符集匹配比如LIKE sales[_]2024可以匹配sales_2024。但如果你想匹配字面方括號就又得引入一層轉(zhuǎn)義。類似這種“一個數(shù)據(jù)庫一套規(guī)則”的細節(jié)跨數(shù)據(jù)庫移植時最容易翻車。建議所有涉及LIKE的 SQL 都寫清楚ESCAPE不要裸奔。5.3 特殊字段名和 IN 查詢的補充注意雖然本文主角是轉(zhuǎn)義符但“特殊字段處理”還有兩個經(jīng)常一起出現(xiàn)的坑值得多說兩句。一是熱詞里提到的字段名是數(shù)據(jù)庫保留字。MySQL 用反引號Oracle 和 PostgreSQL 用雙引號SQL Server 用方括號。各數(shù)據(jù)庫的標識符引用規(guī)則不一樣遷移 SQL 時很容易忽略。最省心的辦法是建表時就避免使用保留字命名。二是IN查詢報錯或結(jié)果異常。常見原因包括列表里混入了 NULL導致結(jié)果缺少記錄字段類型和列表元素類型不一致列表過長超過數(shù)據(jù)庫限制。排查順序應(yīng)該是先打印實際執(zhí)行的 SQL確認列表內(nèi)容有沒有特殊字符再用COALESCE或IFNULL把 NULL 處理掉最后檢查字符集和排序規(guī)則是否統(tǒng)一。最后說點實際的我在實際項目里被這種問題折騰過好幾回之后總結(jié)出一條經(jīng)驗凡是寫 LIKE 相關(guān)的需求第一件事就是問清楚字段內(nèi)容里會不會出現(xiàn)%、_、\這些特殊字符。會就統(tǒng)一在代碼里做一次escape_like()數(shù)據(jù)庫層一律顯式ESCAPE /絕不依賴默認行為。這個習慣養(yǎng)成了后面能少踩很多坑。再分享一個調(diào)試小技巧遇到“模糊查詢結(jié)果不對”時別急著改 SQL 來回試。先用一條SELECT把轉(zhuǎn)義后的模式串打出來看一眼往往能立刻發(fā)現(xiàn)%和_的位置錯了。比如你原本以為模式是%5/%%打印出來發(fā)現(xiàn)成了%5%%/%那問題就一目了然了。這種問題最耗時間的環(huán)節(jié)從來不是寫修復語句而是確認數(shù)據(jù)里的特殊字符到底是什么。把排查順序理順效率能提高不少。 SQL模糊匹配LIKE轉(zhuǎn)義符詳解：從通配符陷阱到性能優(yōu)化
有一回做投訴工單報表要從一張幾萬行的表里篩出“產(chǎn)品名稱中帶 5% 字樣”的記錄。我順手寫了WHERE product_name LIKE %5%%執(zhí)行完當場愣住——全表幾乎全中。第一反應(yīng)是數(shù)據(jù)臟排查了大半天最后才發(fā)現(xiàn)鍋根本不在數(shù)據(jù)而是這條 SQL 里的%太“自作聰明”了。像“字段中含有轉(zhuǎn)義符”這類坑凡是寫過模糊匹配查詢的人基本都會踩一次區(qū)別只是有人半小時就繞出來了有人會為此懷疑人生。這篇就專門聊透這個事LIKE 里的%和_到底在干嘛、轉(zhuǎn)義符是怎么回事、不同數(shù)據(jù)庫怎么處理、工程代碼里怎么防御以及這類查詢的性能和索引陷阱。內(nèi)容以 MySQL 為主線兼顧 Oracle、SQL Server、PostgreSQL最后附上踩坑記錄和排查速查表。適合正在做數(shù)據(jù)查詢、報表統(tǒng)計、接口開發(fā)的朋友尤其是被模糊查詢結(jié)果弄到一頭霧水的那幾位。1. 模糊匹配為什么會“栽”在轉(zhuǎn)義符上1.1 LIKE 里 % 和 _ 的真實身份先明確一個基本概念。LIKE做模糊匹配時模式串里有兩個特殊字符是通配符%匹配任意長度的任意字符包括空字符串。_匹配且僅匹配任意單個字符。生活化類比%相當于你搜索文件時輸入的*只要文件名里有一段對得上整串都能匹配_則更像下棋時的“一個空格”必須剛好有一個字符占住這個位置多一個少一個都不行。問題在于如果業(yè)務(wù)字段的數(shù)據(jù)本身包含%或_比如“洗面奶 100% 正品”或者編碼規(guī)范里常見的KPI_2024_考核表那么這些字符在 LIKE 模式里會被當成通配符來解釋查出來的結(jié)果自然不是字面含義。這是“字段中含有轉(zhuǎn)義符”的核心矛盾數(shù)據(jù)里的特殊字符和查詢語法的特殊字符撞車了。解決思路只有一個——告訴數(shù)據(jù)庫這里出現(xiàn)的%或_不是通配符是普通字符。這個動作就叫“轉(zhuǎn)義”。1.2 一個典型的誤匹配現(xiàn)場用數(shù)據(jù)說話。假設(shè)有一張投訴記錄表結(jié)構(gòu)很簡單CREATE TABLE complaint_log ( id INT PRIMARY KEY, product_name VARCHAR(100), deal_rate VARCHAR(20) ); INSERT INTO complaint_log VALUES (1, 洗面奶促銷裝100%正品, 15%), (2, 洗發(fā)水500ml, 5%), (3, KPI_2024_考核表, NULL), (4, KPX2024考核表, NULL);業(yè)務(wù)需求查出所有商品名稱里帶KPI_2024的記錄。直覺寫法SELECT * FROM complaint_log WHERE product_name LIKE %KPI_2024%;這條 SQL 不會報錯但結(jié)果里第 3 條和第 4 條都會出來。第 4 條KPX2024考核表并沒有_但因為_被當成“任意單個字符”KPI_2024中的_恰好匹配了X整條就命中了。再看%的例子。需求是查“包含 5% 這個字面值”的記錄SELECT * FROM complaint_log WHERE product_name LIKE %5%%;這個寫法更離譜第二個%是通配符等價于%5%于是只要字段里出現(xiàn)過數(shù)字 5就會全部命中。表里第 1、2 條都會查出來甚至某條記錄里帶個“5 元優(yōu)惠券”也會被撈上來。這兩段代碼看起來人畜無害實際跑起來全是坑。根本原因就是沒做轉(zhuǎn)義。1.3 不同數(shù)據(jù)庫的默認轉(zhuǎn)義行為這里有個特別重要的點不同數(shù)據(jù)庫對 LIKE 中轉(zhuǎn)義符的默認處理是不一樣的。我整理了一個對照表方便你排查時對號入座。數(shù)據(jù)庫默認轉(zhuǎn)義字符需要顯式 ESCAPE 嗎補充說明MySQL\是默認轉(zhuǎn)義符可以不寫但受NO_BACKSLASH_ESCAPES模式影響模式不統(tǒng)一時容易出隱性 bugSQL Server無默認轉(zhuǎn)義符必須寫ESCAPE否則%和_永遠是被通配還支持[]做單字符匹配容易混Oracle無默認轉(zhuǎn)義符必須寫ESCAPE字符串字面量里的\處理也容易混亂PostgreSQL\是默認轉(zhuǎn)義符建議顯式寫ESCAPE受standard_conforming_strings影響字面量寫法易混淆所以“為什么我在 MySQL 里寫了%\_%有效在 SQL Server 里卻什么都不匹配”這類問題答案往往就在這個表里。SQL Server 不認反斜杠轉(zhuǎn)義你必須顯式給出ESCAPE比如WHERE product_name LIKE %KPI\_2024\_% ESCAPE \;MySQL 里默認能直接用反斜杠但如果數(shù)據(jù)庫處于NO_BACKSLASH_ESCAPES模式反斜杠也會失效。因此最穩(wěn)妥的做法是不要依賴數(shù)據(jù)庫的默認行為每條 LIKE 都顯式聲明轉(zhuǎn)義符。2. 正確姿勢ESCAPE 子句與等價替代方案2.1 ESCAPE 子句的完整寫法SQL 標準提供了一套通用的解決方案在 LIKE 模式末尾加ESCAPE子句自定義一個轉(zhuǎn)義字符。語法格式WHERE 字段 LIKE 模式 ESCAPE 轉(zhuǎn)義字符;轉(zhuǎn)義字符的作用是當它出現(xiàn)在%或_前面時后面的通配符就被“降級”為普通字符。用前面的5%場景舉例SELECT * FROM complaint_log WHERE product_name LIKE %5/%% ESCAPE /;拆開看這段模式第一個%是通配符中間的/是自定義轉(zhuǎn)義符/%表示“字面意義上的百分號”最后的%又是通配符。整條表達的意思是字段里只要包含字符串5%無論前后有沒有其他內(nèi)容都算命中。匹配下劃線的寫法同理SELECT * FROM complaint_log WHERE product_name LIKE %KPI/_2024/_% ESCAPE /;這里要注意三點。第一轉(zhuǎn)義符與后面的%或_之間不能有空格必須連寫。因為空格本身是普通字符一旦插入空格轉(zhuǎn)義邏輯就斷了。第二轉(zhuǎn)義符只對它緊跟著的一個字符生效。如果要匹配兩個連續(xù)的特殊字符比如字符串100%%模式就要寫成%100/%%/%一個字符一個轉(zhuǎn)義位。第三如果字段本身包含反斜杠比如 Windows 路徑C:\Users\test推薦用/這類非歧義字符做轉(zhuǎn)義符避免和字符串字面量級別的反斜杠轉(zhuǎn)義疊加否則寫兩層轉(zhuǎn)義很容易數(shù)錯斜杠。2.2 用字符串函數(shù)代替 LIKE繞開通配符問題如果不需要用%做真正的模糊匹配只需要“判斷字段中是否包含某個固定文本”那更干凈的做法是用字符串定位函數(shù)。這些函數(shù)搜索的是普通字符串不存在通配符語義也就不需要轉(zhuǎn)義。四個主流數(shù)據(jù)庫的寫法-- MySQL SELECT * FROM complaint_log WHERE LOCATE(5%, product_name) 0; -- Oracle SELECT * FROM complaint_log WHERE INSTR(product_name, 5%) 0; -- SQL Server SELECT * FROM complaint_log WHERE CHARINDEX(5%, product_name) 0; -- PostgreSQL SELECT * FROM complaint_log WHERE POSITION(5% IN product_name) 0;這套方案的優(yōu)點非常直觀不用管%、_、\搜索什么就是什么。適合“完全匹配子串但不關(guān)心前后綴”的場景比如在黑名單詞表里查包含違禁%的數(shù)據(jù)。缺點也有不像LIKE那樣能表達復雜的模糊模式比如“以 A 開頭、中間包含 B、以 C 結(jié)尾”這種組合用函數(shù)就不好寫還得回到 LIKE 加正則那套。2.3 正則表達式方案適合復雜場景的兜底既然 LIKE 的通配符和轉(zhuǎn)義符容易出錯另一個思路是換成正則表達式。在正則的世界里%和_是普通字符沒有通配符的語義所以不需要對它們轉(zhuǎn)義。以 MySQL 8 和 Oracle 的REGEXP_LIKE為例想查包含字面100%正品的記錄SELECT * FROM complaint_log WHERE REGEXP_LIKE(product_name, 100%正品);這里%在正則里就是百分號本身不需要像 LIKE 那樣加轉(zhuǎn)義。但別高興得太早正則有另一套自成體系的元字符.、*、[]、()、|等它們才是需要轉(zhuǎn)義的對象。比如要搜KPI_2024_考核表這個需求用正則是正常查但若要搜KPI.2024中的字面點號正則里就得寫成KPI\.2024。簡單總結(jié)只搜固定子串優(yōu)先用字面函數(shù)LOCATE/INSTR/CHARINDEX。需要復雜模式、且字段里%、_出現(xiàn)頻繁時用正則表達式反而省事。LIKE 加ESCAPE則適合需求簡單但就是想用 LIKE、團隊習慣統(tǒng)一的場景。3. 工程代碼里如何穩(wěn)妥處理“轉(zhuǎn)義符”查詢3.1 參數(shù)化查詢不等于自動轉(zhuǎn)義很多開發(fā)者在代碼里用參數(shù)化查詢以為把用戶輸入原樣塞進%...%就萬事大吉。這是個很致命的誤解。參數(shù)化查詢解決的是 SQL 注入問題它確保用戶輸入被當成“值”而不是“SQL 片段”來解析。但在LIKE內(nèi)部%和_依然會被數(shù)據(jù)庫解釋為通配符。舉個 Python 操作 MySQL 的例子keyword 5% sql SELECT * FROM complaint_log WHERE product_name LIKE %s params (f%{keyword}%,) # 錯誤的做法% 還是通配符這樣做執(zhí)行后5%中的%依然通配一切結(jié)果還是會把所有含 5 的記錄全部撈出來。正確做法是在拼接 LIKE 模式之前先對關(guān)鍵詞做一次轉(zhuǎn)義處理def escape_like(keyword: str) - str: # 轉(zhuǎn)義順序有講究先反斜杠再 %再 _ return keyword.replace(\\, \\\\).replace(%, \\%).replace(_, \\_) keyword 5% escaped escape_like(keyword) # 得到 5\% sql SELECT * FROM complaint_log WHERE product_name LIKE %s ESCAPE \\\\ params (f%{escaped}%,)這里ESCAPE \\\\對應(yīng)數(shù)據(jù)庫里的反斜杠轉(zhuǎn)義符代碼里寫多少個斜杠經(jīng)常把人繞暈。所以我在工程里更推薦自定義一個不容易出錯的轉(zhuǎn)義符def escape_like(keyword: str) - str: return keyword.replace(/, //).replace(%, /%).replace(_, /_) sql SELECT * FROM complaint_log WHERE product_name LIKE %s ESCAPE / params (f%{escape_like(keyword)}%,)這個方案一眼就能看明白用戶輸入里原來的/被寫成//%寫成/%_寫成/_。查詢時使用ESCAPE /可讀性比數(shù)反斜杠好太多。3.2 ORM 框架里同樣要預(yù)先處理ORM 并不負責幫你轉(zhuǎn)義 LIKE 的特殊字符。比如 Django 的 ORM# 看起來沒問題實際生成 SQL 是 LIKE %5%%照樣全表撈 ComplaintLog.objects.filter(product_name__contains5%)__contains只是幫你拼了個%...%內(nèi)部的%該通配還通配。正確的做法是用extra或RawSQL把轉(zhuǎn)義邏輯顯式寫進 SQL 片段from django.db.models.expressions import RawSQL escaped escape_like(5%) qs ComplaintLog.objects.extra( where[product_name LIKE %s ESCAPE /], params[f%{escaped}%], )MyBatis 場景要區(qū)分#{}和${}。#{}是參數(shù)綁定安全但不能自動處理 LIKE 通配符${}是文本拼接有注入風險不適合直接拼用戶輸入。穩(wěn)妥的寫法是在 XML 中用CONCAT并且提前在 Java 服務(wù)層完成轉(zhuǎn)義select idsearch resultType... SELECT * FROM complaint_log WHERE product_name LIKE CONCAT(%, #{escapedKeyword}, %) ESCAPE / /selectescapedKeyword在 Service 層已經(jīng)調(diào)用escape_like()處理過。這么做既避免注入又防住了通配符穿透。JPA 的Query同理LIKE :keyword里的keyword必須攜帶轉(zhuǎn)移后的模式。3.3 動態(tài)拼 SQL 時的防守底線如果團隊里還有人習慣用字符串拼接的方式生成查詢 SQL一定要立幾條規(guī)矩所有進入 LIKE 模式的用戶輸入必須先走統(tǒng)一的轉(zhuǎn)義函數(shù)。轉(zhuǎn)義函數(shù)里必須包含對轉(zhuǎn)義符本身、%、_三者的處理缺一不可。拼接時LIKE必須顯式帶ESCAPE不允許依賴數(shù)據(jù)庫默認行為。盡量限制輸入長度比如 50 個字符以內(nèi)防止有人把超長正則或通配符變體塞進來。另外一個時常被忽略的坑是字段名本身帶特殊字符或數(shù)據(jù)庫保留字。比如熱詞里提到的“mysql 表中字段為關(guān)鍵字”如果字段命名是order、group、desc這類保留字查詢時必須用反引號包裹SELECT * FROM complaint_log WHERE order 1;這類問題和轉(zhuǎn)義符屬于同一大類的“特殊字符處理”在開發(fā)規(guī)范里值得專門加一條字段命名盡量避開保留字和特殊符號實在避不開要統(tǒng)一標識符的引用方式。4. 含有轉(zhuǎn)義符的模糊查詢性能和索引怎么兼顧4.1 前導通配符會讓索引失效聊完正確性必須說說性能。很多人在排查轉(zhuǎn)義符問題時往往忽略了 LIKE 查詢本身就存在索引陷阱。WHERE product_name LIKE %xxx%這種前后都帶通配符的寫法數(shù)據(jù)庫無法利用普通 B 樹索引只能走全表掃描。原因很簡單優(yōu)化器沒法確定匹配的起點不知道應(yīng)該從索引樹的哪個位置開始掃描。即使你加了ESCAPE把特殊字符轉(zhuǎn)義成了字面量這個性能問題也不會消失。ESCAPE解決的是匹配結(jié)果的正確性索引失效的根源是前導%本身。如果你的查詢模式是固定的前綴比如LIKE abc%那普通索引還能用上。但“字段中間含有特殊字符”這類需求往往逃不掉全表掃描。這時就需要靠下面兩種手段兜底。4.2 用生成列或函數(shù)索引優(yōu)化MySQL 8.0 以上支持生成列?？梢蕴崆鞍炎侄沃械奶厥庾址麆冸x出來存成獨立列再對這個列建索引查詢時直接走索引ALTER TABLE complaint_log ADD COLUMN product_name_clean VARCHAR(100) GENERATED ALWAYS AS (REPLACE(REPLACE(product_name, %, ), _, )) STORED; ALTER TABLE complaint_log ADD INDEX idx_clean (product_name_clean);這樣業(yè)務(wù)代碼里查“包含 5% 的記錄”時可以先按干凈列過濾或者直接對干凈列做常規(guī)查詢效率和正確性都得到了保障缺點是占存儲空間。Oracle 支持函數(shù)索引可以針對INSTR這類函數(shù)建索引CREATE INDEX idx_instr_rate ON complaint_log (INSTR(deal_rate, %));查詢時用WHERE INSTR(deal_rate, %) 0優(yōu)化器就有機會走函數(shù)索引。PostgreSQL 的表達式索引類似直接在查詢列上建立表達式CREATE INDEX idx_instr ON complaint_log ((POSITION(% IN deal_rate)));4.3 視圖解決不了性能問題熱詞里有個高頻問題“視圖可以加快查詢速度嗎”這個誤解在模糊查詢場景里特別常見。有人把WHERE product_name LIKE %5%%封裝成一個視圖指望查詢視圖能變快?？梢灾苯咏o你結(jié)論視圖只是把一段 SQL 保存成了一個命名對象它不存儲數(shù)據(jù)也沒有自己的索引。查詢視圖等價于執(zhí)行視圖中嵌套的那條 SQL掃描成本和直接寫 LIKE 沒有任何區(qū)別。真正能提速的是合理的前綴匹配、覆蓋索引、全文索引或者干脆把特殊字符剝離后建生成列。想靠視圖解決模糊查詢性能問題方向就錯了。5. 常見問題與實戰(zhàn)排查小抄5.1 快速對照表為了讓你遇到問題時能快速定位我整理了下面這張表。問題現(xiàn)象可能原因處理辦法查詢結(jié)果明顯過多字段里的%被當成通配符使用ESCAPE轉(zhuǎn)義為字面量明明字段含_條件就是匹配不上_被當成單字符占位符對_轉(zhuǎn)義或改用LOCATE等函數(shù)MySQL 里寫了\%無效數(shù)據(jù)庫處于NO_BACKSLASH_ESCAPES模式改用顯式ESCAPE /SQL Server 里\%無效SQL Server 無默認轉(zhuǎn)義符必須寫ESCAPE \或用[]反斜杠路徑字段查詢不到字符串字面量轉(zhuǎn)義與 LIKE 轉(zhuǎn)義疊加換用非\的轉(zhuǎn)義符或參數(shù)綁定用戶輸入%導致全表匹配參數(shù)化查詢未處理 LIKE 通配符入?yún)⑶敖y(tǒng)一調(diào)用escape_like模糊查詢很慢前導%導致索引失效生成列/函數(shù)索引/全文索引把模糊查詢封裝成視圖想提速視圖不存儲數(shù)據(jù)不能加速改為對索引列做前綴查詢5.2 三個真實踩坑現(xiàn)場第一個坑業(yè)務(wù)編碼里大量使用下劃線。商品編碼規(guī)范是SZ_2024_001這類格式需求是“查詢 2024 年所有深圳商品”。同事寫的是LIKE %SZ_2024%結(jié)果把SZ02024、SZ12024都撈出來了因為這些_都可以被當成任意單字符。排查時如果不先用一個小數(shù)據(jù)集驗證 LIKE 語義很容易誤判為數(shù)據(jù)質(zhì)量問題。第二個坑JSON 字段里做模糊匹配。有人把整個 JSON 字符串存到字段里查詢時直接LIKE %type:A%。雖然 JSON 里的雙引號和冒號不是通配符但如果 JSON 數(shù)據(jù)里恰好含%或_一樣中招。更麻煩的是 JSON 字符串中的反斜杠轉(zhuǎn)義會疊加。這種情況不要用 LIKE直接用數(shù)據(jù)庫的 JSON 函數(shù)比如 MySQL 的JSON_EXTRACT既安全又高效。第三個坑SQL Server 的方括號。SQL Server 的 LIKE 支持[]做字符集匹配比如LIKE sales[_]2024可以匹配sales_2024。但如果你想匹配字面方括號就又得引入一層轉(zhuǎn)義。類似這種“一個數(shù)據(jù)庫一套規(guī)則”的細節(jié)跨數(shù)據(jù)庫移植時最容易翻車。建議所有涉及LIKE的 SQL 都寫清楚ESCAPE不要裸奔。5.3 特殊字段名和 IN 查詢的補充注意雖然本文主角是轉(zhuǎn)義符但“特殊字段處理”還有兩個經(jīng)常一起出現(xiàn)的坑值得多說兩句。一是熱詞里提到的字段名是數(shù)據(jù)庫保留字。MySQL 用反引號Oracle 和 PostgreSQL 用雙引號SQL Server 用方括號。各數(shù)據(jù)庫的標識符引用規(guī)則不一樣遷移 SQL 時很容易忽略。最省心的辦法是建表時就避免使用保留字命名。二是IN查詢報錯或結(jié)果異常。常見原因包括列表里混入了 NULL導致結(jié)果缺少記錄字段類型和列表元素類型不一致列表過長超過數(shù)據(jù)庫限制。排查順序應(yīng)該是先打印實際執(zhí)行的 SQL確認列表內(nèi)容有沒有特殊字符再用COALESCE或IFNULL把 NULL 處理掉最后檢查字符集和排序規(guī)則是否統(tǒng)一。最后說點實際的我在實際項目里被這種問題折騰過好幾回之后總結(jié)出一條經(jīng)驗凡是寫 LIKE 相關(guān)的需求第一件事就是問清楚字段內(nèi)容里會不會出現(xiàn)%、_、\這些特殊字符。會就統(tǒng)一在代碼里做一次escape_like()數(shù)據(jù)庫層一律顯式ESCAPE /絕不依賴默認行為。這個習慣養(yǎng)成了后面能少踩很多坑。再分享一個調(diào)試小技巧遇到“模糊查詢結(jié)果不對”時別急著改 SQL 來回試。先用一條SELECT把轉(zhuǎn)義后的模式串打出來看一眼往往能立刻發(fā)現(xiàn)%和_的位置錯了。比如你原本以為模式是%5/%%打印出來發(fā)現(xiàn)成了%5%%/%那問題就一目了然了。這種問題最耗時間的環(huán)節(jié)從來不是寫修復語句而是確認數(shù)據(jù)里的特殊字符到底是什么。把排查順序理順效率能提高不少。