MySQL的LIKE和REGEXP到底有什么区别两种写法为什么会查出不一样的结果这是我被问得最多的问题之一。前几天帮同事排查一个线上数据需求他写了条name LIKE %abc%能正常查出记录换成name REGEXP abc后结果集却对不上甚至多出一些完全看不懂的行。这种问题几乎每个写SQL的人都遇到过一两次而且特别容易被忽略——因为大多数时候两种写法返回的结果长得一模一样。可一旦表里混进一条包含点号、百分号、脱字符这类特殊字符的数据差异就突然暴露出来轻则数据重复重则漏数。这篇就把LIKE和REGEXP的匹配机制、典型翻车场景和排查思路一次性讲透适合所有负责写查询SQL的同学收藏备用。1. 先建立共识LIKE和REGEXP到底怎么“看”字符串1.1 LIKE两个通配符走天下LIKE是SQL标准里的模糊匹配语法规则非常简单只有两个通配符%表示任意长度的字符序列包括空字符串_表示任意单个字符。除此之外其他字符都是字面量匹配时原样比对。注意一个隐含规则LIKE是对整个字符串做整体匹配。如果模式里没有%和_LIKE的行为基本等同于等号比较。比如name LIKE abc它要求字段值从头到尾精确等于abc不会去匹配xxabcxx这一点和大多数人的直观理解一致也是后续对比的关键。LIKE的大小写敏感性由字段的排序规则collation决定。大部分情况下MySQL默认使用utf8mb4_general_ci或utf8mb4_0900_ai_ci后缀ci表示不区分大小写所以在这些默认规则下LIKE abc可以匹配ABC。如果字段或库表用的是_bin后缀的排序规则那大小写就敏感了。从存储引擎的角度看LIKE模式还有一个特殊待遇如果模式以固定字符串开头比如name LIKE abc%MySQL有机会使用该字段的普通索引做范围扫描这一点在生产环境里对查询性能影响非常大后面还会专门展开说。1.2 REGEXP一整条正则语法链REGEXPMySQL 8.0后也推荐使用REGEXP_LIKE()函数走的是正则表达式引擎。正则的规则比LIKE复杂得多.匹配任意单个字符[]表示字符集*表示前一个元素重复零次或多次^和$分别锚定字符串开头和结尾|表示或关系()可以分组\d\w\s这类预定义字符类也全都支持。REGEXP有一个最容易被忽略的默认规则它是部分匹配。也就是说只要字符串的任意一个位置能匹配上正则模式判断结果就是真。REGEXP abc会匹配abc、xxabc、abcxx、xxabcxx等所有包含abc子串的记录而不是要求整个字符串等于abc。这与LIKE“整体匹配”的直觉形成了鲜明反差。MySQL 8.0之后REGEXP默认使用ICU正则库功能更接近现代编程语言里的正则支持Unicode属性。MySQL 5.7及更早版本使用的则是POSIX扩展正则虽然基本语法相同但在一些边界行为上有细微差别。日常排查时不需要太关注引擎差异但要知道一旦表里有特殊字符这两套规则判定出的结果可能完全不同。为了快速建立心理模型可以这样对比对比维度LIKEREGEXP匹配方式整体匹配模式必须覆盖整个字符串部分匹配命中任意子串即返回真通配符/元字符只有%和_有特殊含义.*[]^$查找“包含”关系必须手写%关键词%直接写关键字默认就是包含完全匹配语义不带通配符即完全匹配必须用^关键字$手动锚定索引使用前缀%形式可走索引通常无法使用索引全部扫描大小写控制由collation决定由collation决定8.0可通过第三个参数指定2. 三种最常见“结果不同”的翻车现场还原2.1 翻车现场一REGEXP的“部分匹配”悄悄让你多查了N条先创建一个模拟表下面所有案例都在这张表上跑CREATE TABLE t_goods ( id INT PRIMARY KEY, name VARCHAR(50) ) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci; INSERT INTO t_goods VALUES (1, abc), (2, xxabcxx), (3, v1.2 firmware), (4, v1x2 firmware), (5, 50% off), (6, discount 50% flash sale), (7, v112 firmware), (8, note[urgent]), (9, note urgent), (10, [0-9] code);现在想筛选名称里精确等于abc的商品名称。-- 返回 id1 (abc) SELECT * FROM t_goods WHERE name LIKE abc; -- 返回 id1 (abc) 和 id2 (xxabcxx) SELECT * FROM t_goods WHERE name REGEXP abc;这个结果差异是最容易踩坑的。LIKE不带通配符时默认整体匹配只有abc这一条命中而REGEXP默认部分匹配只要包含abc子串的记录全部命中。如果业务本意是筛选名称就是“abc”的商品REGEXP一句不加锚点就会把xxabcxx这类记录全部带出来最终导致统计口径错误。解决办法也简单让REGEXP执行整体匹配用锚点把正则框死SELECT * FROM t_goods WHERE name REGEXP ^abc$;任何正则表达式在需要整体匹配时都建议显式加上^和$这也是避免差异的第一道防线。2.2 翻车现场二点号、方括号这些“看起来普通的字符”正则表达式里的元字符在LIKE看来只是普普通通的字符反过来也一样LIKE里的通配符在正则里也可能被当成特殊符号。这一条可以说是结果差异的重灾区。先看一个点号.的经典例子。表里有三条记录v1.2 firmware、v1x2 firmware、v112 firmware业务想找出名称里包含v1.2的记录。-- 返回 id3 (v1.2 firmware) SELECT * FROM t_goods WHERE name LIKE %v1.2%; -- 返回 id3, id4, id7三条全出来了 SELECT * FROM t_goods WHERE name REGEXP v1.2;LIKE语句里的点号没有特殊含义就是字面量的“.”所以只有v1.2这一条命中。但REGEXP里的.是“匹配任意单个字符”的元字符v1.2在正则里等价于“v 任意一个字符 1 任意一个字符 2”因此v1x2v后面是1中间任意字符是x然后2等等这里需要稍微推导一下v1.2的结构是 v、1、任意字符、2所以v112也能匹配因为 v、1、1、2 中第二个1充当了任意字符。于是v1x2和v112就被顺带捞了出来。再看方括号[]的案例。业务想筛出名称里含有数字的记录直觉上会这样写-- 返回 id10 ([0-9] code)因为它包含字面量 [0-9] SELECT * FROM t_goods WHERE name LIKE %[0-9]%; -- 返回所有包含数字的记录id1, 3, 4, 5, 6, 7 SELECT * FROM t_goods WHERE name REGEXP [0-9];LIKE里[0-9]就是方括号、数字、横杠、方括号这一串普通字符没有任何“数字集合”的含义REGEXP里[0-9]是一个字符集匹配0到9之间的任意一个数字。这个差异特别容易迷惑新手因为不少人会把LIKE里的%和正则的*混为一谈又把方括号当成通用语法。实际上SQL标准里的LIKE从来就不支持字符集。2.3 翻车现场三百分号与下划线LIKE的通配符不是普通字符上一节说的是“正则元字符被当成字面量”的情况反过来还有一类坑LIKE的通配符被业务人员误当成普通字符处理。比如数据里有50% off和discount 50% flash sale两条记录业务想找出名称中带50%的记录新手很容易写出-- 返回 id5, id6 SELECT * FROM t_goods WHERE name LIKE %50%%;这条语句的结果看起来对了两条记录都返回了。但仔细推敲一下LIKE里的%是任意长度字符序列%50%%本质上等同于%50%——只要名称里包含“50”就会命中。这和业务想找的“包含字面量50%”并不是一回事。如果表里有一条abcd50efgh的记录这条SQL也会把它查出来因为“50”后面并不需要紧跟百分号。这就属于结果碰巧对但语义完全错误的情况。正确的LIKE写法需要对%做转义SELECT * FROM t_goods WHERE name LIKE %50\%%;MySQL里LIKE的默认转义字符是反斜杠\%表示字面量的百分号所以%50\%%的意思才是“包含字面量50%的字符串”。而REGEXP写起来反而简单因为正则里%没有特殊含义直接写就行SELECT * FROM t_goods WHERE name REGEXP 50%;下划线_同理。LIKE里_是任意单个字符REGEXP里_是普通下划线。需要匹配字面量下划线时LIKE要写成\_而REGEXP直接写_即可。3. 一套可以原样抄走的排查定位流程3.1 先把“匹配意图”翻译成人话再写SQL遇到两种写法结果不一致时不要急着改SQL先停下来把业务意图翻译清楚。我通常会在草稿纸上明确三件事到底是要“等于某个字符串”“包含某个子串”“以什么开头/结尾”还是“某些字符集合里的任意一个”。这一步看起来很简单但90%的线上事故都是因为意图本身没想清楚。举个例子“查名字带v1.2的商品”这里必须区分是想匹配字面量v1.2还是想匹配“v开头、2结尾、中间任意”这种模式大部分人潜意识里是前者。一旦明确是前者写REGEXP时就要立刻想到转义点号REGEXP v1\\.2或者用字符集包裹点号REGEXP v1[.]2。意图清楚了后面就不容易跑偏。平时写代码我还会在SQL后面加一行注释写明这次匹配是“精确”“包含”“前缀”“后缀”还是“字符集”既方便自己复查也方便同事接手。3.2 用差集SQL快速定位“多出来的行”和“被漏掉的行”如果线上已经出现了两条SQL结果不一致最快定位差异的方法是做差集。先分别建两个临时结果集再用NOT IN或NOT EXISTS找差集。假设原业务需求是“名称包含abc”两条SQL分别是SELECT id, name FROM t_goods WHERE name LIKE %abc%; SELECT id, name FROM t_goods WHERE name REGEXP abc;先看看REGEXP比LIKE多了哪些行SELECT id, name FROM t_goods WHERE name REGEXP abc AND id NOT IN ( SELECT id FROM t_goods WHERE name LIKE %abc% );再看看LIKE比REGEXP多了哪些行SELECT id, name FROM t_goods WHERE name LIKE %abc% AND id NOT IN ( SELECT id FROM t_goods WHERE name REGEXP abc );把差集结果打印出来观察规律往往一眼就出来。比如多出来的行里都是xxabcxx这种带前后缀的数据立刻就能意识到是REGEXP部分匹配导致的。如果多出来的行里带着.[^这类特殊字符那就往元字符字面量方向查。这个方法不需要猜直接看数据说话是我排查这类问题最常用的第一招。3.3 给字符串做体检看LENGTH和HEX预防隐形字符还有一类更隐蔽的问题字段里看似相同的字符实际编码不一样。比如全角空格、中文括号和英文括号、换行符等在LIKE和REGEXP下的表现可能有细微差别。普通%abc%可能因为%包含了这些字符而“侥幸”匹配但REGEXP里的.在部分模式下不匹配换行符于是结果就出现分叉。排查这类问题我一般会把差异记录的原始字节打出来SELECT id, name, LENGTH(name) AS len, HEX(name) AS hex_code FROM t_goods WHERE id IN (差异记录id列表);LENGTH返回字节长度HEX返回十六进制编码。如果看到类似C2 A0这种连续编码基本就是不间断空格看到E4 B8 AD E6 96 87这种一串字节那就是中文字符。判断清楚之后要么在业务入口清洗数据要么在查询时用REPLACE等函数先做标准化尽量避免在SQL里依赖隐式的模糊匹配。3.4 用“等价对照表”快速互转LIKE和REGEXP规则排查到最后一步是要把一条SQL等价翻译成另一种写法。这里有一个对照参考我常年贴在笔记里业务意图LIKE写法REGEXP等价写法备注完全匹配aLIKE aREGEXP ^a$必须加锚点包含aLIKE %a%REGEXP aREGEXP默认部分匹配以a开头LIKE a%REGEXP ^a注意锚点位置以a结尾LIKE %aREGEXP a$末尾锚点任意单个字符LIKE _REGEXP .注意.不匹配换行的场景匹配a或b只能写两个LIKE再ORREGEXP ^a$^b$字符集不支持需要拆多个条件REGEXP ^[abc]正则优势之一匹配字面量%LIKE %\%%REGEXP %正则里%是普通字符匹配字面量_LIKE %\_%REGEXP _同上匹配字面量.LIKE %._%或LIKE %v1.2%REGEXP v1\\.2或REGEXP v1[.]2需要转义点号匹配数字不支持只能写多个ORREGEXP [0-9]性能上注意扫描量匹配字母或数字不支持REGEXP [a-zA-Z0-9]也可以用[[:alnum:]]这张表也是我平时写SQL时的速查卡。只要先确认了业务意图再查表选择对应写法基本不会翻车。4. 实战速查表与避坑清单4.1 常见字符匹配场景对照表实际项目中真正高频的场景其实就那么多。把匹配需求抽象成这几种分别给出最稳妥的写法业务场景推荐写法解析商品名完全等于某字符串WHERE name abc能用等号就别用模糊匹配走索引最稳商品名包含某段连续文字WHERE name LIKE %abc%语义直白注意大数据量下全表扫描商品名以固定前缀开头WHERE name LIKE abc%可走索引性能最佳商品名以固定后缀结尾WHERE name LIKE %abc无法使用索引注意数据量商品名以数字开头WHERE name REGEXP ^[0-9]只能用正则实现商品名包含手机号片段WHERE name REGEXP 1[3-9][0-9]{9}正则的强项商品名包含中文“促销”WHERE name LIKE %促销%中文直接匹配注意字符集统一商品名包含10%折扣字样WHERE name LIKE %10\%%必须转义百分号4.2 关于性能LIKE前缀走索引REGEXP多半全表扫描除正确性之外LIKE和REGEXP在性能上的差距也必须心里有数。MySQL的B树索引对LIKE是有“特殊照顾”的。只要LIKE的模式以固定字符串开头比如LIKE abc%优化器就能把它转换成一个范围扫描利用普通索引缩小查询范围。我用EXPLAIN验证过很多次这种SQL的执行计划里type通常是range或者ref效率很理想。但LIKE %abc%和LIKE %abc这类模式因为前缀不确定索引就帮不上忙了只能全表扫描。对于千万级以上的表这种扫描带来的IO压力非常大可能直接把慢查询日志打满。REGEXP的情况更棘手。即便写了REGEXP ^abc这种看似有明确前缀的模式目前MySQL的优化器也很难把正则条件改写为索引范围扫描。大多数情况下EXPLAIN出来是typeALL也就是全表扫描。也就是说正则的便利性完全建立在牺牲查询性能的基础之上。如果表很大且过滤字段没有其他可用的辅助筛选条件用REGEXP前务必三思。一个折中的方案是把正则里可能出现的固定前缀单独提取为普通字段比如商品名称的“首字母”或者“品牌编码”建普通索引先用索引缩小范围再对结果子集用REGEXP做精确匹配。我帮一个数据平台做优化时就是把一个正则跑全表的慢查询改成“前缀索引圈定候选集 小结果集正则过滤”查询耗时从秒级降到了毫秒级。4.3 我踩过的几个坑与建议的规范这几年在工作中积累了一些很实在的教训分享几个典型的第一个坑是“转义反斜杠的二次转义”。在MySQL字符串字面量里反斜杠本身就是转义字符。如果想在REGEXP中匹配一个反斜杠SQL字符串里要写\\\\也就是四个反斜杠因为MySQL先把\\解析成一个反斜杠正则引擎再把这个反斜杠当成元字符继续解析。我第一次遇到时光数反斜杠就数了半天。后来学乖了需要匹配\时就用CHAR(92)拼接或者用RLIKE配合BINARY关键字尽量减少肉眼数反斜杠的折磨。第二个坑是“小表试得通大表测出问题”。很多时候测试环境数据量少碰巧没有包含特殊字符的记录LIKE和REGEXP跑出来的结果完全一致。一上生产数据一多差异就全冒出来了。现在我的习惯是凡是写了模糊匹配不管LIKE还是REGEXP都要加一层“规则校验查询”先找出所有包含%、_、.、[、(、\的记录单独看一眼再上线避免生产数据里暗藏特殊字符。第三个坑是“多条件组合时忽略NULL”。LIKE和REGEXP对NULL的处理都是返回NULL在WHERE里会被当成假值过滤掉。这在单个条件时问题不大但一旦和其他条件用OR连接NULL的假值可能会让整行被排除和预期完全相反。比如WHERE name LIKE %abc% OR remark something如果name是NULL右边条件成立也整行被过滤。所以涉及模糊匹配的多条件时最好对字段做IFNULL预处理。除了具体坑我建议团队在SQL规范里明确一条模糊匹配场景优先用LIKE只有遇到字符集、或关系、重复次数等LIKE无法表达的需求时才使用REGEXP并且必须由编写人在SQL后注释说明匹配意图。这样做的原因是LIKE规则简单可读性好而正则虽然强大一行正则十个SQL功底不同的人能解读出十种意思维护成本很高。5. 最后分享一个压箱底的小技巧我在实际排查中还有个习惯同时也算压箱底的小技巧遇到LIKE和REGEXP结果不一致不直接改线上SQL而是先用临时表圈定一批特征数据把两条查询的命中集合并成一张对比表再添加一列标注来源。比如CREATE TEMPORARY TABLE tmp_compare AS SELECT id, name, like AS src FROM t_goods WHERE name LIKE %abc% UNION ALL SELECT id, name, regexp AS src FROM t_goods WHERE name REGEXP abc; SELECT id, name, GROUP_CONCAT(src ORDER BY src) AS srcs FROM tmp_compare GROUP BY id, name HAVING srcs like,regexp;这样能直观看到到底是哪一侧多匹配了记录。多出来的那侧去对应的字段值里找规律90%的问题都能在一分钟之内定位到根因。剩余10%则多半是隐字符或排序规则这类深水区问题再用前面说的HEX分析法解决。工具和方法都不难难得是每次写SQL时多想一步“我这个写法在特殊字符面前成立吗”。养成这个意识这类疑难杂症基本就不会再找上你了。
