刚开始做后端那几年我在技术群里被问得最多的一句话就是除了LIKE和ANY还有哪些常用的模糊查询操作符说实话这个问题一出来很多人其实已经犯了概念混淆。LIKE是名副其实的模糊查询操作符而ANY在标准SQL里是子查询集合比较操作符并不是干模糊匹配的。只是因为它们常常一起出现在面试题和ORM文档里大家默认把它们归到了一类。这篇文章咱们就把数据库生态里的模糊查询操作符全部捋一遍把哪些是真模糊、哪些是假模糊、哪些在不同数据库里长得完全不一样一次性讲清楚。适合刚接触SQL的开发者、做数据查询优化的分析岗以及准备跨数据库迁移的团队参考。1. 先分清LIKE和ANY到底是不是一类操作符1.1 LIKE是“通配符匹配”的起点LIKE是SQL标准当中最基础、最常用的模糊查询操作符。它的核心机制很好理解就两个通配符百分号%匹配任意长度的字符序列下划线_匹配任意单个字符。比如WHERE name LIKE 张%匹配所有以“张”开头的名字WHERE code LIKE A_001匹配A加上任意一个字符再加上001的格式。很多新手会忽略一个关键点LIKE做的是整串匹配。换句话说%张%里的两个%让“张”可以出现在字符串的任何位置但整个模式必须和字段值的完整长度对上。你要查“张”结尾的人写%张要查开头是“王”且只有一个字的写王_。这个理解直接影响后面的性能优化为什么前缀匹配LIKE 张%能走索引而LIKE %张%大概率全表扫描原因就在这。1.2 ANY其实是子查询的“任意一个”ANY的出现和模糊匹配关系不大。它是SQL里做子查询集合比较的操作符标准语法是expr operator ANY (subquery)意思是expr只要能跟子查询返回结果中的任意一个值满足指定关系运算符、、等就返回真。比如SELECT * FROM employees WHERE age ANY (SELECT age FROM employees WHERE dept 研发部);这条查询的含义是找出年龄比研发部任意一个员工年龄都大的员工。注意“任意一个”不是“所有”也不是“某一个”。和它对称的是ALL要求满足全部值。ANY还有一个孪生兄弟叫SOME行为和ANY完全一样。很多人把ANY当作模糊查询操作符大概率是因为它在ORM的关联查询里看起来像是在“多个值里匹配一个”但它在SQL语义里从来不做字符串模糊匹配。先把这个界限划清楚后面再讲它怎么配合模糊查询使用才不跑偏。1.3 模糊查询操作符的真实谱系把LIKE和ANY分开之后问题就清楚了。真正意义上算模糊查询操作符的应该是能让字符串匹配从“精确等于”扩展到“部分匹配、模式匹配、近似匹配”的运算符。我按底层机制把它们分成五个流派通配符流派LIKE、ILIKE、GLOB、~~这一类基于通配符匹配的实现。正则流派REGEXP、REGEXP_LIKE、RLIKE、SIMILAR TO、PostgreSQL的~。相似度流派PostgreSQL pg_trgm扩展提供的%和-。发音流派MySQL的SOUNDS LIKE、标准SQL的SOUNDEX。全文检索流派MySQL的MATCH ... AGAINST、Oracle的CONTAINS、PostgreSQL的tsvector/tsquery、SQLite FTS5的MATCH。后面每个流派我都会拆开来聊包括它们能干的事、干不了的事以及我踩过的坑。2. 常规口味不用LIKE也能做通配匹配的操作符2.1 PostgreSQL的ILIKE和~~运算符PostgreSQL和MySQL在LIKE上有一个非常容易被忽略的差异PostgreSQL的LIKE是区分大小写的。你写WHERE name LIKE %smith%查不出“Alice Smith”因为S是大写。这时候就要用ILIKE它就是LIKE大小写不敏感版SELECT first_name FROM employees WHERE first_name ILIKE smi%;这条会把Smith、SMITH、smiTh都查出来在处理用户输入的英文名时非常实用。除了这两个关键字PostgreSQL还保留了一组运算符写法LIKE等价于~~NOT LIKE等价于!~~ILIKE等价于~~*NOT ILIKE等价于!~~*所以你经常会看到老代码里有人写WHERE last_name ~~ %son这不是乱写是有意用运算符形式。看到别懵就行。需要提醒的是ILIKE的索引支持和LIKE类似只有前缀模式ILIKE abc%能走普通B-tree索引。一旦写成ILIKE %abc%同样会退化成全表扫描这种场景建议直接用pg_trgm的GIN索引后文会讲。2.2 SQLite的GLOB和它风格迥异的通配符SQLite里除了LIKE还有一个GLOB操作符。GLOB的语法不像SQL更像Shell路径匹配*匹配任意多个字符?匹配单个字符支持字符类比如[a-z]和LIKE最大的区别是GLOB区分大小写而且没有ESCAPE子句。举例SELECT * FROM files WHERE filename GLOB *.log;会返回所有以.log结尾的文件名.LOG是查不出来的。如果你觉得GLOB的匹配规则太“硬核”SQLite也允许在编译时启用正则扩展但默认情况下REGEXP操作符是没有实现的。很多人天真地把MySQL的REGEXP习惯带到SQLite里结果直接报错这就是没查文档的教训。2.3 标准SQL里的SIMILAR TO一个很尴尬的存在SIMILAR TO是SQL标准定义的操作符实用度不高但面试经常被拿出来问所以必须知道。它的语法像是通配符和正则的混血儿支持%和_也支持字符类[abc]、选择|和量词*、、?。但有一个致命限制SIMILAR TO要求模式匹配整个字符串这和正则“部分匹配”的逻辑完全不一样。比如WHERE city SIMILAR TO (Bei|Nanj)ing;能匹配Beijing和Nanjing但你想用这个模式去匹配xxx-Nanjing就不行因为它必须整串匹配。这个设定让它的实际使用率非常低因为大多数模糊查询的需求恰恰是“包含某段模式”而不是“整串符合某个模式”。PostgreSQL支持SIMILAR TO其余主流数据库支持得并不好。真要写复杂的模式匹配直接用正则流派的操作符更省心。3. 进阶操作正则、相似度和发音匹配3.1 MySQL的REGEXP/RLIKE把字符串匹配升级成正则当LIKE满足不了“按规则提取”的需求就该上正则了。MySQL里REGEXP和RLIKE是完全等价的关键字底层是同一个实现。比如SELECT * FROM users WHERE email REGEXP ^[a-zA-Z0-9._%-][a-zA-Z0-9.-]\\.[a-zA-Z]{2,}$;这条可以粗略过滤合法邮箱格式。注意MySQL的REGEXP在默认情况下不区分大小写如果要做大小写敏感的正则匹配得配合BINARY关键字WHERE name REGEXP BINARY ^[A-Z];PostgreSQL里对应操作符是~大小写不敏感版是~*不匹配用!~和!~*。Oracle则必须用函数写法REGEXP_LIKE(name, ^[A-Z])。同样是正则不同数据库的引擎细节并不完全一致比如\w、\d这些转义字符是否支持、贪婪和懒惰匹配的写法规矩跨库迁移时最容易在这里翻车。3.2 pg_trgm的%和-用相似度做模糊查询如果用户输入有错别字LIKE和正则都无能为力这时候相似度操作符就派上用场了。PostgreSQL安装pg_trgm扩展后可以直接用%运算符判断两个字符串是否足够相似用-返回相似度距离。举个例子产品表里有一条“无线蓝牙耳机”用户搜“无限蓝牙耳机”CREATE EXTENSION IF NOT EXISTS pg_trgm; SELECT * FROM products WHERE product_name % 无限蓝牙耳机;%返回真表示相似度达到阈值默认阈值大约是0.3。你也可以调整阈值或者查询时直接按距离排序取最像的前N条SELECT product_name, product_name - 无限蓝牙耳机 AS dist FROM products ORDER BY dist LIMIT 5;这套机制底层是三元组trigram索引非常擅长处理基于字母的拼写变体和输入错误。不过中文场景要特别注意因为中文字符切成trigram之后如果不做额外处理效果并不理想后面的中文章节我会单独说。3.3 按发音匹配SOUNDEX和SOUNDS LIKE不少数据库支持按发音做模糊匹配最传统的就是SOUNDEX算法。SOUNDEX会把字符串编码成“字母三位数字”读音近似的词编码相近。MySQL专门提供了SOUNDS LIKE操作符SELECT * FROM users WHERE name SOUNDS LIKE Jhon;这条能匹配到John这类发音相近但拼写不同的名字。SQL Server也有SOUNDEX函数但需要自己写SOUNDEX(col) SOUNDEX(Jhon)。这种操作符对拼音和中文完全无效因为SOUNDEX本身是为英文发音设计的所以国内项目里基本用不到。但如果你做的是国际用户系统处理英文姓名、地址类数据时它确实能解决不少问题。3.4 别忘了这些操作符和LIKE的性能差异每次有人问我“正则和相似度能不能替代LIKE”我都会强调一句能力上能替代性能上不一定。正则和相似度的计算成本通常比LIKE高一个量级因为它们几乎不可能从普通B-tree索引里取得直接收益。LIKE最理想的情况是模式以固定前缀开头能走普通索引。一旦模式是%xxx%索引基本失效。REGEXP几乎必然全表扫描除非你用生成列预计算或者倒排索引来兜底。pg_trgm的%可以搭配GIN索引获得很好的性能但要提前建好扩展和索引。SOUNDS LIKE在数据库层面也很难直接索引。所以在生产环境里选型不只看哪个操作符“好用”更要先想清楚数据量和性能预算。我见过很多团队在小表上跑得欢数据一上千万级直接被打穿。4. 从“单个字段匹配”到“全文智能检索”更高级的模糊查询操作符4.1 MySQL的MATCH AGAINST全文检索式的模糊匹配如果你的目标不是找“包含某个词”的字段而是要在大量文本里检索语义相关的段落LIKE和正则会直接败下阵来。MySQL的全文检索用MATCH ... AGAINSTSELECT * FROM articles WHERE MATCH(title, body) AGAINST(数据库优化 IN NATURAL LANGUAGE MODE);它不再做简单的子串匹配而是把文本分词、建立倒排索引按相关性打分。只要查询字段有全文索引覆盖搜索性能远超LIKE %关键词%。需要注意MySQL全文检索默认只支持英文等拉丁文的空格分词中文要用ngram parser建索引时需要额外指定WITH PARSER ngram否则中文检索结果会让你怀疑人生。4.2 PostgreSQL的tsvector/tsquery更规范的全文搜索PostgreSQL的全文检索基于tsvector和tsquery两个类型SELECT * FROM docs WHERE to_tsvector(english, body) to_tsquery(database optimization);to_tsvector负责把文本切成词位并记录位置to_tsquery负责解析搜索词就是全文匹配操作符。配合GIN索引PostgreSQL的全文检索在处理英文和配置中文分词后都能支撑中等规模的搜索场景。和MySQL一样它的强项是“按词检索、相关性排序”弱项是没有上下文理解和同近义词识别指望它理解“苹果”是水果还是手机不太现实。4.3 SQLite FTS5的MATCH轻量级应用里的模糊方案如果是做本地应用或小工具SQLite自带的FTS5模块提供了MATCH操作符。创建虚拟表之后查询基本是SELECT * FROM notes WHERE notes MATCH 关键词;甚至支持更复杂的短语查询和高亮函数。FTS5的检索速度比LIKE %关键词%快几个数量级唯一的硬性要求是表结构需要提前设计为虚拟表不适合临时对已有表做全文检索。FTS5同样支持unicode61分词中文需要自己挂分词策略或者退而求其次用LIKE兜底。4.4 不同数据库的模糊查询操作符对照表我平时帮团队做跨库迁移时经常要把不同数据库的模糊查询写法做一套映射这里直接整理成速查表省得大家到处翻文档。能力MySQLPostgreSQLOracleSQLite通配符匹配区分大小写LIKELIKE / ~~LIKELIKE通配符匹配忽略大小写LIKE依赖collation配置ILIKE / ~~*-LIKEASCII默认忽略大小写正则匹配REGEXP / RLIKE / REGEXP_LIKE~ / ~*REGEXP_LIKE默认不可用需扩展相似度/编辑距离-pg_trgm % / -UTL_MATCH.JARO_WINKLER-发音匹配SOUNDS LIKE-SOUNDEX-全文检索MATCH ... AGAINSTtsvector tsqueryCONTAINSFTS5 MATCH集合比较非模糊IN / ANY / EXISTSIN / ANY / EXISTSIN / ANY / EXISTSIN / ANY / EXISTS细节提醒MySQL 8.0以上提供了REGEXP_LIKE函数和REGEXP等价PostgreSQL的LIKE区分大小写这是和很多数据库不一样的地方SQLite的LIKE对ASCII字符默认忽略大小写具体取决于编译参数。5. 实操过程把一张用户表玩明白5.1 准备一张测试表和测试数据先用PostgreSQL做一次完整的实操演示其他数据库思路完全一致。建一张用户表插入几条带有大小写和特殊字符的测试数据CREATE TABLE users ( id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT UNIQUE, note TEXT ); INSERT INTO users (name, email, note) VALUES (Alice Smith, aliceexample.com, 后端工程师负责订单系统), (bob smith, bobexample.org, 数据分析师关注用户增长), (Charlie Zhang, charlieexample.net, 产品经理写PRD), (david-wang, davidexample.cn, 前端工程师喜欢Rust), (Eve Li, eveexample.io, DevOps折腾CI/CD);5.2 用LIKE和ILIKE跑一遍基础模糊查询先看传统方案SELECT id, name FROM users WHERE name LIKE %smith%;因为PostgreSQL的LIKE区分大小写这条只会查出bob smithAlice Smith首字母大写查不出来。想忽略大小写就用ILIKESELECT id, name FROM users WHERE name ILIKE %smith%;这样两条都能查出来。如果写成运算符形式SELECT id, name FROM users WHERE name ~~ david-%;也能命中david-wang。这一轮实操想验证的就是同样的业务需求选对了操作符结果集会完全不同。5.3 用正则、相似度和全文检索处理更难的需求场景一变我想找出所有“名字里包含两个连续小写字母、后面跟一个连字符”的用户用LIKE写不出来上正则SELECT * FROM users WHERE name ~ ^[a-z]-[a-z]$;精准捞出了david-wang。再来一个用户输入错别字的场景用户搜“Alcie Smtih”明显是想找“Alice Smith”这时候用pg_trgm排序CREATE EXTENSION IF NOT EXISTS pg_trgm; SELECT id, name, name - Alcie Smtih AS dist FROM users ORDER BY dist LIMIT 3;最后看全文检索场景想找note里同时提到“后端”和“订单”的记录。虽然示例数据里能靠LIKE %后端% AND LIKE %订单%实现但文本一旦变长、数据量变大还是得靠分词全文检索。这也是为什么我始终建议业务稍微复杂一点就别死磕LIKE。5.4 ANY到底怎么在模糊查询场景里配合使用尽管ANY本身不是模糊查询操作符但它在“模糊查询一张表后再按集合条件过滤另一张表”的关联场景里非常常见。典型写法SELECT * FROM orders WHERE user_id ANY ( SELECT id FROM users WHERE name ILIKE %smith% );这里ANY接收的是子查询返回的一组user_id只要orders表的user_id等于集合里任意一个就命中。真正干模糊匹配的依旧是ILIKEANY只是负责把子查询的结果集合化和IN效果相同。但ANY还能配合比较运算符用比如 ANY这是IN做不到的。把这一层理解透基本就能回答大部分人关于ANY的疑惑了。6. 常见问题与排查技巧实录6.1 LIKE查询慢到爆炸怎么排查最经典的问题就是WHERE name LIKE %keyword%不走索引。在MySQL里模式以%开头的LIKE查询会直接放弃B-tree索引执行计划里看到typeALL基本就是全表扫描了。我的排查顺序很简单先看执行计划确认是不是全表扫描。判断这个查询是不是高频核心路径不是的话可以接受。能改前缀匹配就改前缀匹配比如LIKE keyword%。不能改前缀匹配但数据量大的MySQL上FULLTEXTngramPostgreSQL上pg_trgm的GIN索引。后缀匹配场景比如LIKE %keywordPostgreSQL可以对反转后的文本建表达式索引这个技巧帮我解决过好几个慢查询问题。6.2 下划线和百分号被当成通配符怎么转义用户搜索“100%纯棉”下划线、百分号都会被当成通配符结果比预期多出一堆。正确处理是在LIKE模式里显式声明ESCAPE字符SELECT * FROM products WHERE product_name LIKE %100\%纯棉% ESCAPE \;如果用户输入里本身带反斜杠转义会更麻烦。我建议在程序侧先将用户输入中的\、%、_都做一次转义再拼进SQL模式而不是让用户输入直接裸奔进查询。另一个容易踩的坑是正则里的小数点它匹配任意字符想匹配字面量.也得写成\\.。处理用户输入时永远不要直接拼接原字符串进模糊查询。6.3 中文场景下的模糊查询操作符怎么选中文没有空格分词大多数发音、词组相似度操作符都水土不服。纯LIKE %关键词%在小数据量下是简单方案但千万级数据上很容易打穿。我的建议分几层简单场景、数据量可控用LIKE %关键词%配合适当缓存。MySQL数据量大全文索引加ngram parser建索引时用WITH PARSER ngram。PostgreSQL数据量大上zhparser或pg_jieba插件配合tsvector全文检索。如果业务本身是搜索系统我建议直接引入Elasticsearch这类外部引擎数据库只做基础兜底。这些年看到最多的教训就是一开始数据量小用LIKE量上来之后才急着迁移全文检索历史数据、查询语法、维护成本全都要重来一遍。技术选型时一定先问清楚数据规模增速。6.4 跨数据库迁移时操作符不兼容怎么办这可能是最磨人的问题。LIKE是标准中的标准几乎所有数据库都认但ILIKE、~、REGEXP、SOUNDS LIKE、MATCH ... AGAINST这些都是数据库方言换一个库就是一套写法。我的做法是在服务端ORM层做一层查询构建抽象把所有模糊查询统一收敛成几个方法比如contains、startWith、regexMatch底层分别映射到各数据库的方言操作符。这样迁移时只需要替换映射表不需要翻历史代码。另一个笨但稳妥的办法是尽量用LIKE加自定义函数把正则、全文检索都封装成存储函数让业务SQL不感知底层方言差异。缺点是要维护一堆自己的函数库但胜在可控。7. 最后说几句踩过坑后的操作体会在实际项目里我见过太多人一上来就上正则、上全文检索最后被性能和维护成本拖垮。我的建议始终是能用LIKE前缀匹配解决的问题绝不额外上复杂操作符确实需要模糊子串匹配先评估能否用表达式索引或全文检索兜底只有遇到错别字、同音词这类“心理模糊”需求才考虑相似度和发音匹配。更关键的是别把ANY这类集合比较和模糊查询混为一谈它们解决的问题完全不同。等到你把这些操作符的能力边界想清楚SQL写起来就会稳很多排查问题也更有底气。后面有机会我再把MySQL全文检索ngram和PostgreSQL pg_trgm索引的具体配置单独拆一篇出来聊。
