LIKE 与 REGEXP一篇彻底掌握 SQL 字符串匹配前置阅读《06EXISTS 与 NOT EXISTS》《07MySQL 常用字符串函数》前言在学习 SQL 的过程中大多数人都会觉得字符串相关的知识比较简单。无非就是几个字符串函数再加上一点模糊查询。直到开始刷 LeetCode SQL很多人才发现真正容易出错的恰恰是字符串匹配。例如下面几道经典题1517. Find Users With Valid Emails查找合法邮箱1527. Patients With a Condition查询患有某种疾病的患者1667. Fix Names in a Table修正姓名格式第一次做这些题时很多人都会有类似的疑问为什么有些题目使用LIKE就能解决而有些题目必须使用REGEXP%和_到底是什么意思正则表达式里的^、$、[]、*又分别代表什么为什么邮箱正则要写成leetcode[.]com而不是leetcode.com为什么有时候还会出现REGEXP_LIKE(..., c)如果只是死记硬背这些写法换一道题目就很容易不会写。因此这一篇文章我们不只是介绍语法而是从字符串匹配的原理开始一步一步理解LIKE 到底解决什么问题REGEXP 为什么会出现两者有什么区别LeetCode 中什么时候应该使用 LIKE什么时候必须使用 REGEXP学完这一篇你不仅能完成 SQL50 中所有字符串匹配相关的题目也能够理解正则表达式背后的设计思想。一、为什么需要字符串匹配先来看一个最简单的例子。假设有一张用户表user_idname1Alice2Allen3Bob4Alex5Amy现在老板提出一个需求查询所有名字以字母 A 开头的用户。如果按照之前学习过的等值查询我们可能会写SELECT * FROM Users WHERE name A;显然这条 SQL 不会得到正确结果。原因很简单。表示完全相等。数据库会去寻找名字恰好等于A的记录而不会理解以 A 开头这样的含义。但是现实开发中这种需求却非常常见例如查询姓王的用户查询以.com结尾的邮箱查询包含 SQL 的课程名称查询所有手机号以138开头的用户。这些都不是完全匹配而是按照一定规则进行匹配。为了满足这种需求SQL 提供了第一种字符串匹配方式——LIKE。二、LIKE 到底是什么LIKE 可以理解成按照某种模式Pattern进行模糊匹配。它最大的特点就是不要求字符串完全相同只要求满足指定规则即可。例如SELECT * FROM Users WHERE name LIKE A%;这里的A%就不是一个普通字符串而是一种匹配规则。数据库看到这条 SQL 后会理解成第一位必须是字母A后面可以跟任意数量的字符。因此name是否匹配A✅Amy✅Alice✅Allen✅Alex✅Bob❌可以发现虽然这些名字长度不同但只要满足以 A 开头这一规则就会被查询出来。这就是 LIKE 的作用。三、LIKE 为什么只有两个通配符很多初学者会以为 LIKE 有很多复杂的语法。实际上并不是。LIKE 真正需要掌握的只有两个通配符%_只要理解了这两个符号就已经掌握了 LIKE 的全部内容。1.%匹配任意长度的字符%是 LIKE 中最常用的通配符。它表示任意长度包括 0 个的字符。例如WHERE name LIKE A%可以理解成A 任意多个字符因此A Amy Allen Alice Alex都会匹配成功。需要特别注意的是%同样可以表示 0 个字符。因此字符串A本身也是符合条件的。再来看另外一个例子WHERE name LIKE %ice这里%放在最前面表示前面可以是任意内容但必须以ice结尾。因此Alice ice Aice都会匹配成功。如果写成WHERE name LIKE %lex%则表示字符串中只要包含lex即可。例如Alex Alex123 123Alex456都会被查询出来。因此关于%记住一句话即可%表示任意长度的字符可以出现在字符串的任何位置。2._匹配任意一个字符与%不同_只能匹配一个字符。例如WHERE name LIKE A_表示第一位必须是 A后面只能再有一个字符。因此Ab A1 AA都符合要求。但是Alice Allen都不会匹配。因为它们在 A 后面不止一个字符再例如WHERE name LIKE A___表示总长度必须是 4 个字符并且第一位是 A。因此Alex可以匹配。而Alice由于长度为 5因此不会匹配。到这里你会发现LIKE 的规则其实非常简单。整个 LIKE只需要记住两个符号通配符含义%任意长度包括 0 个的字符_任意一个字符对于大多数简单的模糊查询例如前缀匹配后缀匹配包含某个字符串LIKE 都已经足够使用。但是当我们开始刷 LeetCode SQL 时很快就会遇到一个问题为什么邮箱验证不用 LIKE为什么 Patients 那道题官方题解使用的是 REGEXP这说明LIKE 并不能解决所有字符串匹配问题。下一部分我们就来看看 LIKE 的局限性以及 REGEXP 为什么会出现。继续。四、为什么 LIKE 不够用学习 LIKE 之后很多同学都会产生一个疑问既然 LIKE 已经可以进行模糊匹配为什么 SQL 还要设计 REGEXP答案很简单LIKE 能表达的规则非常有限。回顾一下我们刚刚学习过LIKE 真正能够使用的通配符只有两个%任意长度的字符_任意一个字符。也就是说LIKE 只能描述字符串的位置关系。例如以 A 开头以.com结尾包含 SQL固定长度。但是当匹配规则变得复杂时LIKE 就无能为力了。例如下面几个需求。场景一邮箱验证假设有下面几个邮箱winstonleetcode.com alice_123leetcode.com 1aliceleetcode.com alice#123leetcode.com现在要求找出所有合法邮箱。题目规定第一位必须是字母后面允许出现字母数字_.-域名必须是leetcode.com这时候你会发现LIKE 根本写不出来。例如WHERE mail LIKE %leetcode.com它只能保证以leetcode.com结尾。却完全无法限制第一位是不是字母中间是否出现非法字符#是否允许。因此1aliceleetcode.com居然也会匹配成功。显然这是错误的。场景二Patients 那道经典题来看 LeetCode 第 1527 题。数据如下patient_nameconditionsBobDIAB100 MYOPGeorgeACNE DIAB100AlainDIAB201要求找出患有Ⅰ 型糖尿病的患者。题目规定疾病代码必须以 DIAB1 开头很多同学第一反应都会写WHERE conditions LIKE %DIAB1%看起来似乎没有问题。但是实际上它会匹配DIAB100 DIAB101 DIAB199 ABCDIAB100最后这一条ABCDIAB100其实是不合法的。因为真正的疾病代码应该是DIAB100或者MYOP DIAB100而不是ABCDIAB100LIKE 无法表达DIAB1 必须出现在字符串开始或者前面必须有一个空格。所以官方题解才使用WHERE conditions REGEXP (^| )DIAB1这一点LIKE 根本做不到。场景三手机号假设手机号必须长度 11 位第一位必须是 1第二位必须是 3~9。LIKE 能写吗不能。因为LIKE 无法表示第二位只能是 3 4 5 6 7 8 9它只能表示任意字符例如LIKE 1__________甚至1AAAAAAAAAA都会匹配。因此LIKE 不能做格式校验。小结所以LIKE 更适合前缀查询后缀查询包含查询固定长度查询。而涉及邮箱手机号身份证疾病编码身份证号复杂字符串格式都需要REGEXP。五、什么是 REGEXPREGEXP 的全称是Regular Expression正则表达式它可以理解为使用一套规则来描述字符串应该长什么样。例如下面这条规则^[A-Za-z][A-Za-z0-9_.-]*leetcode[.]com$第一次看到很多同学都会觉得这是什么天书其实它只是很多小规则组合在一起。只要拆开来看每一部分都非常简单。例如^[A-Za-z]表示第一位必须是字母。继续[A-Za-z0-9_.-]*表示后面可以出现任意多个字母数字下划线点短横线。然后leetcode表示固定字符串leetcode接着[.]表示真正的.最后com$表示必须以com结束。是不是一下就清晰很多实际上REGEXP 就是把很多简单规则按照顺序拼接起来。理解这一点之后学习正则表达式就会容易很多。六、REGEXP 最常用的几个符号很多人觉得正则表达式特别复杂。其实LeetCode SQL 真正常用的符号只有几个。掌握它们基本就足够应付 SQL50 中所有字符串匹配题目。下一部分我们就逐个讲解^$[]*.|()并结合邮箱题和 Patients 题彻底理解它们的含义。六、REGEXP 最常用的几个符号很多同学第一次接触正则表达式时都会被一长串符号吓到。例如^[A-Za-z][A-Za-z0-9_.-]*leetcode[.]com$感觉每个字符都很陌生。实际上在 LeetCode SQL50 中真正经常出现的正则符号只有几个。掌握它们以后绝大多数 SQL 正则题目都能读懂。下面我们逐个来看。1.^—— 匹配字符串开始^表示字符串必须从这里开始。例如WHERE name REGEXP ^A表示名字必须以A开头。下面的数据name是否匹配Alice✅Allen✅Amy✅Bob❌JackA❌为什么JackA不匹配因为虽然它包含字母A但A 不是字符串的第一个字符。所以^A真正表达的是字符串开始 ↓ Alice ^2.$—— 匹配字符串结束与^相对应$表示字符串必须在这里结束。例如WHERE mail REGEXP com$表示必须以com结尾。例如mail是否匹配abcleetcode.com✅test.com✅abc.com.cn❌为什么第三个不匹配因为它真正的结尾是.cn而不是.com因此$的作用就是限制字符串不能再有后续内容。3.[]—— 字符集合这是 LeetCode 中出现频率最高的符号之一。例如[A-Z]表示任意一个大写字母。可以匹配A B C ... Z如果写成[a-z]表示任意一个小写字母。如果把两者放在一起[A-Za-z]就表示任意一个英文字母。因此A a B z都符合。同样地[0-9]表示任意一个数字。例如3 8 9都会匹配。实际开发中经常会把它们组合使用。例如[A-Za-z0-9]表示一个字符可以是字母数字。这也是邮箱、用户名等规则中最常见的写法。4.*—— 重复零次或多次很多人容易把*和 LIKE 中的%混淆。它们虽然都有多个的意思但含义并不相同。例如[A-Za-z]*表示前面的规则可以重复0 次1 次多次。举几个例子A ABC Hello都符合。甚至空字符串也符合。因为*包含重复 0 次。这是很多初学者容易忽略的地方。5..—— 任意一个字符这一点是最容易出错的。很多人看到.第一反应都是点号。实际上在正则表达式中.表示任意一个字符。例如a.c可以匹配abc a1c a-c ac原因很简单中间那个.可以代表任何字符。所以它并不是一个真正的点。6. 为什么邮箱里面要写[.]现在回到 LeetCode 邮箱题。很多同学都会写leetcode.com看起来没有任何问题。但是实际上因为.代表的是任意字符。所以下面这些字符串leetcodeXcom leetcode1com leetcode-com理论上都能匹配成功。显然这是错误的。真正需要的是一个真正的点号。因此可以写成leetcode[.]com这里[.]表示字符集合里面只有.因此它就失去了正则表达式的特殊含义变成了真正的.这也是我最推荐的写法。7. 为什么有的人写\\.你在网上还会看到另一种写法leetcode\.com或者leetcode\\.com这是因为反斜杠\本身就是转义符。它表示不要把后面的.当成正则符号而是当成普通字符。但是SQL 字符串本身也需要解析反斜杠因此很多数据库里面一个\需要写成\\于是最终变成\\.这就是很多同学第一次看到\\.觉得特别奇怪的原因。哪一种更推荐对于 LeetCode 和初学者来说我更推荐[.]原因有两个第一可读性更高别人一眼就知道这里就是一个真正的点。第二不需要考虑字符串转义。因此后面的文章我都会使用[.]而不是\\.下一部分我们开始讲LeetCode 两道最经典的字符串匹配题1517 Find Users With Valid Emails邮箱验证1527 Patients With a ConditionDIAB1 正则七、LeetCode 高频案例一合法邮箱Valid E-Mails这一题对应的是 LeetCode1517. Find Users With Valid Emails。题目要求找出所有合法邮箱。合法邮箱需要满足下面两个条件前缀必须以字母开头前缀后面只能出现字母数字_.-域名必须是leetcode.com例如邮箱是否合法winstonleetcode.com✅bella-leetcode.com✅sally.comeleetcode.com✅1aliceleetcode.com❌alice#123leetcode.com❌davidgmail.com❌很多同学第一次都会想到WHERE mail LIKE %leetcode.com这样写能够通过吗答案是不能。因为LIKE 只能判断是否以leetcode.com结尾。它根本不会检查第一位是不是字母中间有没有#是否允许其它特殊字符。例如下面两个邮箱1aliceleetcode.com abc#123leetcode.com同样能够通过LIKE %leetcode.com显然不符合题意。正确写法官方推荐SELECT user_id,name,mail FROM Users WHERE REGEXP_LIKE( mail, ^[A-Za-z][A-Za-z0-9_.-]*leetcode[.]com$, c );不要急着背。我们把它拆开来看。第一部分^表示字符串开始。说明邮箱必须从这里开始匹配。第二部分[A-Za-z]表示第一位只能是字母因此下面正确Alice下面错误1Alice这就是题目前缀必须以字母开头的体现。第三部分[A-Za-z0-9_.-]*表示后面可以出现字母数字下划线点短横线例如abc123 abc_123 abc.xyz abc-xyz全部合法但是abc#123里面出现#由于不在字符集合里面。因此匹配失败。第四部分leetcode表示固定字符串数据库不会模糊匹配必须完全一致。第五部分[.]表示真正的点而不是任意字符这一点前面已经讲过。第六部分com$表示必须以com结束。后面不能再有任何字符因此leetcode.com.cn不会匹配。为什么要加c很多同学在刷题时还会遇到REGEXP_LIKE( mail, ..., c )这里的c表示Case-sensitive区分大小写为什么需要它因为MySQL 8 中REGEXP_LIKE()默认是否区分大小写与字段使用的字符集Collation有关。例如下面这个邮箱winstonleetcode.COM如果数据库采用不区分大小写的排序规则例如utf8mb4_general_ci那么没有c时它也可能匹配成功。但是题目要求域名必须是leetcode.com全部小写。因此需要告诉数据库按照大小写严格匹配。于是写REGEXP_LIKE( mail, ..., c )如果使用的是LeetCode 较老版本REGEXP由于它无法指定c通常官方会调整测试环境。因此两种写法都可能出现。八、LeetCode 高频案例二Patients With a Condition这是LeetCode另一道经典正则题。数据如下patient_nameconditionsDanielYFEV COUGHBobDIAB100 MYOPGeorgeACNE DIAB100AlainDIAB201要求找出患有Ⅰ 型糖尿病的患者。很多同学第一反应都是WHERE conditions LIKE %DIAB1%为什么这是错误的来看下面两个字符串。MYOP DIAB100和ABCDIAB100如果使用LIKE它们都会匹配但是真正合法的疾病编码应该满足两种情况。第一就在字符串开始。例如DIAB100第二前面有一个空格。例如MYOP DIAB100因此官方使用WHERE conditions REGEXP (^| )DIAB1这句话可以拆成两部分。(^| )表示前面要么是字符串开始要么是一个空格后面紧跟DIAB1于是能够匹配DIAB100 MYOP DIAB100但是不会匹配ABCDIAB100因为前面既不是开始。也不是空格。这一题也是LIKE完全无法解决的。九、LIKE 和 REGEXP 如何选择实际开发中可以按照下面这张表来判断。场景推荐前缀查询LIKE后缀查询LIKE包含字符串LIKE固定长度LIKE邮箱验证REGEXP手机号验证REGEXP身份证验证REGEXP特殊格式校验REGEXP记住一句话简单模糊匹配用 LIKE复杂格式校验用 REGEXP。十、本章总结这一篇我们系统学习了 SQL 中两种字符串匹配方式。首先我们认识了 LIKE。LIKE 适合处理简单的模糊查询它只有两个通配符%匹配任意长度字符_匹配任意一个字符。随后我们进一步学习了 REGEXP。REGEXP 可以描述更加复杂的字符串规则因此非常适合邮箱、手机号、身份证等格式校验。最后我们结合 LeetCode 两道经典题1517. Find Users With Valid Emails1527. Patients With a Condition完整分析了官方正则表达式的设计思路而不是死记答案。希望读完这一篇之后你不仅知道如何写 LIKE 和 REGEXP更能够理解为什么要这样写。本章重点回顾掌握%与_的使用方法理解^、$、[]、*、.等正则符号能够独立分析邮箱正则表达式理解(^| )DIAB1的含义知道 LIKE 与 REGEXP 的适用场景。下一篇预告下一篇我们将进入日期专题《LeetCode SQL 从入门到精通MySQL09日期函数与日期处理专题》我们将结合 LeetCode 高频题系统讲解YEAR、MONTH、DAYDATE_ADD、DATE_SUBDATEDIFFCURDATE、NOW首次登录次日留存连续日期问题