1. 模糊查询操作符全景从 LIKE 到 ANY再到日常必备的“第三梯队”很多刚接触数据库查询的朋友一提到“模糊查询”脑子里第一个蹦出来的就是LIKE稍微进阶一点的可能知道IN、ANY这类集合匹配操作符但真要落到业务里比如查个“名字里带‘张’但不确定是两个字还是三个字”的客户或者“某个字段存了一串标签、想匹配其中任意一个”时LIKE和ANY各自都有明显短板。这篇文章就围绕“除了 LIKE 和 ANY还有哪些常用的模糊查询操作符”这个实际问题把 SQL 里真正高频、能直接抄进项目的模糊匹配手段捋一遍——包括按模式匹配的LIKE与通配符组合、正则表达式家族REGEXP、RLIKE、SIMILAR TO、全文检索MATCH...AGAINST、以及把ANY和LIKE配合起来做“多条件模糊命中”的写法。目标很直接让你看完之后在三种典型场景里能立刻选对工具——前缀定位用LIKE中段未知用正则文本量大用全文索引顺便把ANY该用在哪、不该用在哪也讲透。这个主题适合三类读者一是业务开发同学写报表、做筛选功能时不想被LIKE %xx%的性能坑拖死二是数据分析师处理脏数据、字段取值不规范时需要更灵活的匹配语法三是准备面试的候选人这类操作符对比几乎是数据库基础题里的常客。2. 基础回顾LIKE 和 ANY 各自解决了什么问题2.1 LIKE 的匹配逻辑与常见通配符LIKE的核心是“模式匹配”不是“正则匹配”。它只支持两个通配符%表示任意长度的任意字符包括零字符_表示任意单个字符。很多人习惯把LIKE当正则用写LIKE [0-9]%想匹配以数字开头的字符串这在 MySQL 里根本不生效——[...]是正则的字符类LIKE不认。业务里最典型的LIKE用法就三种前缀匹配WHERE name LIKE 张%走索引比较友好前提是字段上有普通索引且排序规则支持。后缀匹配WHERE email LIKE %qq.com这种写法在大多数数据库里很难走索引。中间包含WHERE content LIKE %关键词%全表扫描概率极高数据量大时必须换思路。这里要单独提醒一句LIKE的转义问题经常被忽略。如果你要匹配的文本里本身就含%或_需要用ESCAPE指定转义符。比如要查包含50%的记录SELECT * FROM product WHERE discount LIKE 50!% ESCAPE !;如果不做转义50%会被理解成“以 50 开头、后面接任意内容”查出来的数据全错。这个坑我见过不止一次尤其是在处理折扣、进度条、百分比这类存储为字符串的字段时几乎必然中招。2.2 ANY 的语义边界它到底在匹配什么ANY在 SQL 里是一个“量词操作符”常用形态是列 比较操作符 ANY (子查询或数组)。它表达的逻辑是只要左边这一列的值与右边集合里的任意一个值满足指定比较关系条件即成立。举个例子SELECT * FROM student WHERE score ANY (SELECT pass_score FROM class_config);这条语句的意思是只要学生的分数大于等于班级配置表里任意一条及格线就命中。ANY强调的是“集合中存在一个即可”它和IN的关系很微妙——在等值比较场景下 ANY (...)等价于IN (...)但ANY能配合、、LIKE这类操作符使用而IN只能做等值判断。很多人在LIKE上面踩过类似的空子想写“名字匹配多个模糊模式中的任意一个”时会下意识写成WHERE name LIKE %张% OR name LIKE %李% OR name LIKE %王%;当模式有十几个时整条 SQL 会变得非常冗长维护起来很痛苦。这时候就可以用ANY配合数组或子查询来收拢WHERE name LIKE ANY (ARRAY[%张%, %李%, %王%]);注意这个写法是 PostgreSQL 风格MySQL 8.0 以上不支持LIKE ANY (数组)需要改写成REGEXP或JSON_TABLE。版本差异永远是模糊查询选型里最容易被忽略的变量。3. 第三梯队操作符逐个拆解REGEXP、SIMILAR TO、POSITION、FULLTEXT3.1 REGEXP / RLIKE模式表达能力最强的“万能刀”REGEXP在 MySQL 中别名RLIKE支持完整正则语法能力远强于LIKE。它解决的核心问题就是“我不知道确切字符串但我知道它的结构特征”。典型场景手机号格式校验WHERE phone REGEXP ^1[3-9][0-9]{9}$匹配以字母开头、后面跟数字的编码WHERE sku_code REGEXP ^[A-Za-z][0-9]匹配多个备选词WHERE title REGEXP 苹果|香蕉|橙子这比写多个OR LIKE干净得多。正则虽然强但有三个问题必须提前知道性能REGEXP基本无法利用普通索引数据量大时会全表扫描属于“功能正确、性能待定”的方案不适合直接挂在千万级表的高频查询上。语法差异MySQL 用的是 POSIX 风格正则\d这种 Perl 风格语法在某些版本里不识别要写[0-9]才稳。PostgreSQL 的~操作符则支持更丰富的写法。转义正则里的\本身也有转义逻辑在 SQL 字符串里写\\d还是\\d取决于数据库对反斜杠的处理方式MySQL 默认不开启NO_BACKSLASH_ESCAPES时\d会被吃掉直接导致匹配失效。我实际处理过的一个案例某订单表里order_no字段混入了空格、全角字符和大小写不统一的编号用LIKE %ABC123%死活查不全改成WHERE order_no REGEXP [[:space:]]*ABC[[:space:]]*123[[:space:]]*后一次捞干净。3.2 SIMILAR TOSQL 标准里的“折中方案”SIMILAR TO是 PostgreSQL 特有的操作符定位是“介于 LIKE 和正则之间”。它既支持%和_又支持正则里的|、*、、括号分组等结构。举个例子匹配“张”或“李”开头的名字SELECT * FROM users WHERE name SIMILAR TO (张|李)%;用LIKE要写name LIKE 张% OR name LIKE 李%用SIMILAR TO一行搞定。它比REGEXP稍微弱一些但胜在查询意图更接近“模式匹配”在 PG 生态里很顺手。不过要注意SIMILAR TO同样不走索引而且它不是标准 SQL 语法换库就要重写。如果项目一开始就定了 PG它是个不错的中间选项如果有多数据库兼容需求就别在这上面押注。3.3 POSITION / LOCATE / INSTR定位子串位置的“轻量派”很多模糊查询需求本质上是“我要判断某个子串在不在字符串里”这种场景不一定需要LIKE用子串定位函数更快更直观。MySQL 里常见三兄弟POSITION(substr IN str)返回子串首次出现的位置从 1 开始找不到返回 0。LOCATE(substr, str[, start_pos])支持从指定位置开始找。INSTR(str, substr)参数顺序和LOCATE相反。实际用法SELECT * FROM article WHERE POSITION(数据库 IN content) 0; SELECT * FROM article WHERE LOCATE(数据库, content) 0; SELECT * FROM article WHERE INSTR(content, 数据库) 0;这三条语句的语义和content LIKE %数据库%等价但好处是它们不会把%当通配符文本里即使有特殊字符也不会误伤。更重要的是当你想统计子串出现次数时LIKE完全帮不上忙而LOCATE配合循环或递归查询可以做到。对于“包含即命中”这种最简单、最高频的模糊查询场景如果字段长度不大、并发不高子串定位函数的可读性其实优于LIKE因为通配符在代码 review 时很容易被忽略而函数一眼就知道意图。3.4 FULLTEXT 全文索引大数据量下的“性能救星”当数据量来到百万级且你的查询是“从文章正文里搜关键词”LIKE %xxx%基本是灾难一次查询可能扫几十万行。这时候应该上全文索引。MySQL 的全文搜索语法SELECT * FROM article WHERE MATCH(title, content) AGAINST (数据库 索引 IN NATURAL LANGUAGE MODE);PostgreSQL 的全文搜索用法SELECT * FROM article WHERE to_tsvector(chinese, title || content) to_tsquery(数据库 索引);全文索引的核心思路是“分词 倒排索引”它和LIKE/REGEXP有本质区别不是逐行扫描字符串而是先建立词到文档的映射查询时直接定位包含目标的文档集合。所以它在“包含关键词”这个场景下性能远超LIKE但在“包含某个特殊符号”“精确匹配某段连续字符”这类场景下反而受限。选全文索引前必须想清楚三件事要支持中文分词MySQL 默认分词器对中文支持比较弱通常需要 ngram 插件PG 需要zhparser或pg_jieba。全文索引查询的不是“原始字符串包含关系”而是“分词后包含关系”比如搜“数据库”不一定能命中“数据库系统”中的“数据库”取决于分词粒度。布尔模式IN BOOLEAN MODE支持、-、*等修饰符可以做必须包含、排除、前缀匹配但它和LIKE的语义不等价测试用例要单独设计。4. 混合使用实战LIKE ANY 与多条件模糊查询的设计模式4.1 LIKE ANY 写法详解与版本限制前面提到LIKE ANY在 PostgreSQL 里可以直接用MySQL 不支持。这里把两种库的完整写法都列出来方便对号入座。PostgreSQL 写法SELECT * FROM users WHERE name LIKE ANY (ARRAY[张%, 李%, 王%]);MySQL 8.0 的等效写法可以借助JSON_TABLE把 JSON 数组展开后再匹配SELECT * FROM users WHERE EXISTS ( SELECT 1 FROM JSON_TABLE([张%, 李%, 王%], $[*] COLUMNS (pattern VARCHAR(50) PATH $)) jt WHERE users.name LIKE jt.pattern );这种写法虽然能解决“多模式取 OR”的冗长问题但有一个前提users.name LIKE jt.pattern里的jt.pattern是运行时值优化器很难预判索引利用率通常不如直接在 WHERE 里写常量LIKE。所以我的建议是模式数量少3 到 5 个时直接写OR LIKE模式超过 10 个再考虑LIKE ANY或REGEXP。4.2 动态模糊匹配的通用设计拼接模式 OR 用正则实际业务里最常见的场景是“用户在前端输入关键词后端做包含匹配”。这时候千万别直接拼 SQL 字符串很容易产生注入问题。正确做法是使用参数化查询把%关键词%整体作为参数传入。以 Java MyBatis 为例select idsearch resultTypeUser SELECT * FROM users where if testkeyword ! null and keyword ! AND name LIKE CONCAT(%, #{keyword}, %) /if /where /select或者用 MySQL 的REGEXP配合动态拼接多关键词参数化后仍是安全的SELECT * FROM users WHERE name REGEXP CONCAT((, REPLACE(?, ,, |), ));这里?是绑定参数传入张,李,王SQL 变成name REGEXP (张|李|王)效果等同多关键词任一命中而且代码里无需拼接多个OR LIKE。不过要提醒REGEXP参与动态条件时正则特殊字符要处理用户输入带.、(、)时会导致匹配异常。稳妥做法是先做一次正则转义把用户输入里的特殊字符替换成\.、\(等安全形式。4.3 模糊匹配的排序与去重问题模糊查询一旦涉及多字段或多关键词结果排序往往被忽略。LIKE只回答“是否匹配”不回答“匹配程度”。常见需求是“匹配度高的排前面”比如搜索“数据库”标题命中“数据库”的应该排在正文命中“数据库”的前面。MySQL 里可以写成SELECT *, CASE WHEN title LIKE %数据库% THEN 3 WHEN summary LIKE %数据库% THEN 2 ELSE 1 END AS match_score FROM article WHERE title LIKE %数据库% OR summary LIKE %数据库% ORDER BY match_score DESC;PostgreSQL 里可以用ts_rank前提是用了全文索引SELECT *, ts_rank(to_tsvector(chinese, title || content), to_tsquery(数据库)) AS rank FROM article WHERE to_tsvector(chinese, title || content) to_tsquery(数据库) ORDER BY rank DESC;去重问题同样关键尤其当查询条件里同时出现“A 表的 LIKE 命中某字段”和“B 表的 JOIN 命中另一个字段”时DISTINCT的写法要小心。SELECT DISTINCT会把整行包括match_score一起计算如果match_score不相同去重会失效。正确做法是先去掉计算列再在外层查询里保留match_score。5. 各操作符的适用场景、性能对比与选型清单操作符/函数匹配能力是否走索引典型场景性能风险LIKE 前缀%前缀模式可走索引B-Tree按名称前缀筛选低LIKE %后缀后缀模式通常不走索引按邮箱后缀筛选中LIKE %包含%包含模式几乎不走索引模糊包含查询高REGEXP完整正则几乎不走索引结构校验、多关键词匹配高SIMILAR TO折中模式几乎不走索引PG 内多模式匹配中高POSITION/LOCATE/INSTR子串定位几乎不走索引子串存在性判断中高FULLTEXT分词全文检索走全文索引大文本关键词搜索低前提是建立索引LIKE ANY多模式任一匹配取决于实现PG 动态多模式过滤中选型时我一般按这个顺序判断先看数据量。万级以内LIKE %xxx%随便用只要把LIMIT加上别一次性返回几万行就行。万级到百万级且查询集中在“包含”场景优先全文索引前提是业务字段适合分词。模式本身复杂多个备选词、边界条件、字符类型校验直接REGEXP性能靠“限定其他条件先缩小数据集”来兜底。有多个前缀/后缀条件且数据库是 PGLIKE ANY值得用MySQL 则用REGEXP或直接多条OR LIKE。这里特别强调一个容易踩的坑不要在LIKE %xxx%前盲目加索引。你建了idx_name但LIKE %xxx%因为前导通配符优化器会放弃索引直接全表扫索引白白占空间和写入开销。你要是真想优化要么改成LIKE xxx%让前缀命中要么用全文索引替代。6. 关键细节与注意事项汇总排序规则、大小写、通配符转义和 NULL6.1 排序规则决定 LIKE 是否大小写敏感很多人没有意识到LIKE是否区分大小写完全取决于字段的排序规则COLLATE。MySQL 里默认的utf8mb4_general_ci是大小写不敏感的所以LIKE abc能匹配ABC。但换成utf8mb4_bin后大小写敏感了LIKE abc匹配不到ABC。PostgreSQL 里默认LIKE是区分大小写的要忽略大小写得用ILIKE。这个差异在跨库迁移时极其坑。我处理过一个案例从 MySQL 迁到 PostgreSQL 后用户搜“zhangsan”搜不出“ZhangSan”排查了半天才发现是大小写敏感性问题。结论是写模糊查询逻辑前先确认字段的COLLATE或统一用函数规范化-- MySQL 忽略大小写 WHERE LOWER(name) LIKE LOWER(%Zhang%);但要注意对字段应用LOWER()后索引直接失效所以这个写法只适合小数据量场景。6.2 通配符转义与正则特殊字符处理LIKE里的%和_是通配符如果业务数据本身含这两个字符必须转义。MySQL 默认转义符是反斜杠但更推荐显式指定ESCAPEWHERE location LIKE %\_% ESCAPE \\;这里ESCAPE \\表示用反斜杠作为转义符\_就表示普通下划线。不转义的话_会被当成“匹配任意一个字符”比如查路径home_dirLIKE home_dir会匹配homeXdir结果完全错了。正则场景更是重灾区。用户输入的关键词如果带有(,),[,],.,*,,?,|等字符直接拼进REGEXP会破坏整个正则结构。处理方法是先转义import re safe_pattern re.escape(user_input) # 再用 safe_pattern 构造 SQL 正则转义之后拼进REGEXP CONCAT((, safe_pattern, ))既有匹配能力又能防注入。6.3 NULL 参与模糊匹配的坑LIKE遇到NULL结果是NULL不是FALSE所以WHERE name NOT LIKE %张%会把name为NULL的行也排除掉——这与很多人直觉相反以为NOT LIKE能查出“不匹配张的所有行”实际上它为 NULL 的行也不在结果里。如果需要把 NULL 单独兜住写法要改成WHERE name IS NULL OR name NOT LIKE %张%;这个细节在做“黑名单过滤”“数据清洗标记”时尤其重要。比如统计“名称中不含非法关键词的用户数”直接NOT LIKE %违规词%会把没有名称的用户全部漏掉导致统计口径错乱。7. 常见误用场景与排查实录7.1 误用场景一把 LIKE 当正则把 REGEXP 当 LIKE这两种误用都非常普遍。LIKE [A-Z]%想匹配大写字母开头结果什么都不返回REGEXP 100%把%当成“重复前一个字符零次或多次”的正则符号匹配结果完全不可控。排查技巧先确认执行计划。EXPLAIN SELECT ... WHERE name LIKE %xxx%如果看到typeALL说明是全表扫描性能问题根源确认再检查 SQL 里的符号语义拿一条确定命中的样本数据手工跑一遍看是不是通配符/正则符号理解错了。7.2 误用场景二索引失效而不自知LOWER(name) LIKE %abc%也好CONCAT(first_name, last_name) LIKE %xxx%也好对字段做函数运算后索引基本失效。我在排查慢查询时见过最典型的例子表只有十万行一个模糊查询跑了三秒原因就是开发把CONCAT拼字段后做了LIKEMySQL 无法对表达式结果建索引生成列可以缓解但很少有人用。对应解法如果业务频繁按“名字全拼”模糊搜索直接新增一个冗余字段full_name建普通索引按full_name LIKE xxx%查询性能立刻从秒级降到毫秒级。7.3 误用场景三数据量上升后直接改成 REGEXP 兜底很多人发现LIKE %xxx%慢以后第一反应是换REGEXP其实更慢——REGEXP不仅不走索引还要逐行跑正则引擎开销比LIKE更大。正确路径是先分析业务是否真的需要“包含任意位置”的匹配如果不需要改成前缀匹配并走索引如果确实需要上全文索引或者离线数据同步到搜索服务如 Elasticsearch 系列在数据库层死磕LIKE %xxx%不划算。7.4 一次真实排查记录有段时间一个报表接口频繁超时SQL 大概是SELECT * FROM orders WHERE customer_name LIKE % AND remark LIKE %加急%;仔细一看第一个LIKE %是毒瘤——它匹配所有非 NULL 字符串等于无条件过滤还让优化器放弃了其他更优的执行路径。去掉这个条件后接口响应时间从 1.8 秒降到 0.3 秒。这事给我的教训是模糊查询条件里最怕写着写着跑出这种“必定为真的无效条件”review 时专门盯这类一眼看着没毛病、实际完全没用的条件。8. 扩展认知JSON 字段与数组字段里的模糊匹配现在很多表结构里直接存 JSON 或数组模糊查询也要跟着升级。PostgreSQL 里数组字段做包含匹配可以用操作符判断“是否包含某个元素”这是数组的精确包含不是字符串包含。如果想对数组里的字符串做模糊匹配要先把数组展开再LIKESELECT * FROM users WHERE EXISTS ( SELECT 1 FROM unnest(tags) AS t WHERE t LIKE %数据% );MySQL 的 JSON 字段可以做类似的事情用JSON_EXTRACT取出目标字段再LIKE但写法比较繁琐SELECT * FROM users WHERE JSON_UNQUOTE(JSON_EXTRACT(tags, $[0])) LIKE %数据%;这种写法的问题非常明显它只匹配数组第一个元素要匹配任意元素得用JSON_TABLE展开成多行再匹配。功能上能做但性能开销很大适合小表或不追求实时性的场景。更成熟的做法是如果这类查询是核心需求直接在应用层把 JSON 里的关键字段冗余成普通列再建索引。为了“偶尔查一下”的便利让数据库承担高开销的 JSON 解析长期看并不划算。9. 安全与合规视角模糊查询相关的 SQL 注入与日志脱敏模糊查询涉及动态拼接关键词是 SQL 注入的高危区。核心原则只有一条所有用户输入无论看起来多安全都必须参数化。LIKE的模糊条件同样可以参数化把%和关键词放在参数里-- 伪代码示意 cursor.execute(SELECT * FROM users WHERE name LIKE ?, [f%{keyword}%])千万不要写这种代码-- 危险写法直接拼接 String sql SELECT * FROM users WHERE name LIKE % keyword %;LIKE拼接的注入风险比等值查询更容易被忽视因为开发者潜意识里觉得“查询条件里带 % 没多大问题”实际上keyword完全可以被构造成% OR 11 --整表数据直接被打出来。另外模糊查询经常出现在日志和监控里而查询参数可能包含敏感信息手机号、身份证号等。打印慢 SQL 日志时至少要先做脱敏手机号只保留前三位后四位身份证只保留前六后四避免日志泄露用户隐私这是合规审计里常被点名的区域。10. 实操心得我平时是怎么组织模糊查询代码的聊了这么多操作符和技巧最后讲一下我自己在项目里沉淀下来的组织方式。第一优先建一个“查询条件包装层”。业务方传进来一个关键词我不直接写 SQL而是把它拆成三个字段exact_value、prefix_value、contains_value。能精确匹配就不能精确但能前缀匹配就LIKE xxx%只有确实需要包含匹配时才用LIKE %xxx%。这样在代码 review 时每个查询的匹配强度一目了然也方便后续优化——前缀匹配和时间字段范围条件可以组合走复合索引而包含匹配必须另想办法。第二统一封装“模糊匹配工具函数”。无论是做REGEXP转义、LIKE通配符转义还是把用户输入拼成多关键词正则都抽成公共函数避免每个团队成员各自为政地写拼接逻辑。这个看起来很简单实际能省掉非常多线上事故——你永远想象不到有人会怎么处理用户输入里的反斜杠。第三定期用慢查询日志和水位线自动识别“模糊查询失控”的表和 SQL。关注%开头的前缀占比如果某条查询长期走全表扫描且数据量在涨趁早做技术债登记。模糊查询不像等值查询那样有明确的“命中率”概念劣化通常是渐进的等业务方反馈“查询越来越慢”时表往往已经大得不好动了。第四做模糊查询方案之前先回答三个问题这个字段是否会被高频搜索搜索结果是否要求“任意位置命中”数据量是否超过单机 MySQL 的舒适区如果三个答案都是“是”直接考虑引入全文检索引擎数据库层的LIKE和REGEXP都不该出现在核心链路上。我个人体感是模糊查询真正的难点不在“会不会写”而在“什么时候用什么”——LIKE写错顶多是慢一点索引和全文方案选错后续重构成本要高一个数量级。希望这篇文章能帮你把“模糊查询操作符”从拿来即用的工具变成能根据场景做取舍的技能。下次面试或评审时如果能把“为什么这里不选LIKE而选全文索引”“为什么LIKE ANY在 MySQL 里不可用”讲清楚就已经赢过大多数只背语法的同行了。