做数据这几年只要涉及文本清洗和模糊匹配我第一个想到的就是 SQL 里的REGEXP。如果说LIKE是一板一眼地查字典那REGEXP就是给了你一套完整的正则表达式语法让你直接在数据库里做模式匹配、提取和替换。今天这篇不是给你贴满文档再让你自己悟而是把我在实际项目里用 SQL 查询日志、清洗手机号、校验邮箱、统计重复订单时最常用到的 REGEXP 写法连同踩过的坑一起整理出来。不管你写后端、做数据运营还是刚接触 SQL这篇文章都可以直接拿去用。1. 为什么我要在 SQL 里直接写正则表达式1.1 先别急着用 REGEXP分清 LIKE 和正则的边界很多刚接触 SQL 的朋友会把LIKE和正则混在一起。LIKE是简单的通配符匹配%代表任意长度字符串_代表单个字符。它适合做这个字段里有没有包含某段固定内容的判断比如WHERE name LIKE 张%可以查出所有姓张的用户。但一旦业务要求变得具体比如查出用户名必须是由字母开头、后面跟着至少 5 位数字而且整串长度不能超过 12 个字符LIKE就会写得很痛苦。你大概率要拼一堆AND name LIKE a% OR name LIKE b%这种重复条件想一想就知道维护成本有多高。这时候REGEXP的优势就体现出来了它可以描述一种模式而不是一个一个去拼取值组合。用正则写上面的条件大概就是^[A-Za-z][0-9]{5,11}$一行搞定。LIKE适合已知的、相对固定的片段REGEXP适合需要描述复杂规则、边界条件和重复次数的地方。1.2 正则能帮你解决哪些 SQL 问题我先罗列几个我在真实项目里遇到过、并且用 REGEXP 解决掉的问题类型字段格式校验判断邮箱、手机号、身份证号、订单编号是否合法。数据清洗把文本里的 HTML 标签、不可见字符、多余符号去掉。长文本筛选从备注、日志、商品描述里筛出包含特定模式的数据比如网址、日期、电话号码。分组去重先把字段内容经正则归一化再GROUP BY统计重复项。这些场景有一个共同点都是处理非结构化或半结构化文本。数据库里最常用的等值和范围查询解决不了这种问题而正则恰恰是专门为文本模式设计的。但我也要提醒你正则不是万能的它有几个明显短板比如性能消耗大、不同数据库语法有差异、调试比较费劲。后面我都会讲到。1.3 我推荐的适用范围以我的经验REGEXP 最适合用在三个地方数据接入层做合规校验、数仓 ETL 里的字段清洗、以及日常数据分析中的临时探查。换句话说它适合在可以接受一定耗时和扫描量的场景里替代人工翻数据。如果你要把它放在用户请求的实时查询链路上我会非常谨慎。因为正则匹配通常走不了索引对千万级大表的压力很大。先搞清楚这一点后面讲优化才有意义。2. 先记住这套 REGEXP 核心语法SQL 里才不慌2.1 元字符与字符类速查正则之所以看起来难是因为它有一套自己的符号语言。SQL 里的 REGEXP 语法本质上和 Perl、Python 里的正则很像但不同数据库实现略有区别。先看最常用的字符匹配模式含义SQL 示例能匹配什么.匹配任意单个字符LIKE对应_的升级版a.c可以匹配abc、a1c[abc]匹配方括号中任意一个字符WHERE name REGEXP [a-c]a、b、c[^abc]匹配不在方括号中的任意字符WHERE name REGEXP [^0-9]任意非数字字符[a-z]字符范围WHERE code REGEXP [a-f0-9]a到f或0到9\d数字等价于[0-9]WHERE id REGEXP ^[0-9]{5}$01234\w字母、数字、下划线WHERE name REGEXP ^[A-Za-z0-9_]$abc_123\s空白字符WHERE content REGEXP [[:space:]]空格、制表符这里有个特别容易踩的坑在 SQL 字符串里写\d、\w这种反斜杠开头的内容很多数据库会当字符串转义处理。比如 MySQL 默认把\d解析成d结果就是正则里你本来想匹配数字实际却匹配了字母d。所以我个人在 SQL 里会尽量避免单反斜杠要么写成[0-9]要么就把反斜杠双写。以 MySQL 8.0 为例匹配手机号可以写SELECT phone FROM customer WHERE phone REGEXP ^1[3-9][0-9]{9}$;注意我写的是[0-9]而不是\d就是为了少踩一个转义坑。这种习惯在写复杂正则时能帮你省不少事。2.2 量词、定位符与分组从能匹配到精确匹配单一字符的模式不够用你还需要描述出现多少次和在哪里出现。量词/定位符含义示例*前一个字符出现 0 次或多次ab*c匹配ac、abc、abbc前一个字符出现 1 次或多次abc匹配abc但不会匹配ac?前一个字符出现 0 次或 1 次ab?c匹配ac、abc{m}恰好出现 m 次[0-9]{4}匹配 4 位数字{m,}至少出现 m 次[0-9]{2,}匹配至少 2 位数字{m,n}出现 m 到 n 次[0-9]{5,11}匹配 5 到 11 位数字^匹配字符串开头^OR匹配以 OR 开头的字符串$匹配字符串结尾\.sql$匹配以 .sql 结尾的字符串()分组提取子串或改变优先级(ab)匹配ab、abab或者量词背后的逻辑可以这样理解你要匹配的字符本质是一个元素量词表达的其实是这个元素的重复次数。我经常用正则来查订单编号比如订单号规则是ORD后面跟 8 到 12 位数字SQL 就可以写成SELECT order_id FROM orders WHERE order_id REGEXP ^ORD[0-9]{8,12}$;这里^和$一定要加。如果不加REGEXP 会去你的字符串里找任意一个能匹配的子串可能会导致ABCDORD12345678XYZ这种脏数据也被查出来。定位符的意义就是告诉数据库我要从头到尾精确匹配不是从中间任意位置找一段。2.3 大小写、Unicode 和转义三个隐蔽的坑不同数据库的正则大小写敏感度不一样。MySQL 的正则默认不区分大小写比如REGEXP ^abc能匹配ABC。你在做用户名校验时可能不想要这种宽松行为可以用BINARY关键字强制区分SELECT username FROM users WHERE BINARY username REGEXP ^Admin[0-9]{2}$;PostgreSQL 则正好反过来默认区分大小写普通~运算符是大小写敏感匹配如需忽略大小写请用~*。这会带来一个后果同样一条正则语句在 MySQL 里和 PostgreSQL 里查出来的结果可能不一样。跨库迁移时最容易栽在这里。中文匹配又是另一个话题。大多数数据库的正则默认按字节或特定字符集处理如果你的表使用 utf8mb4而正则里只是写[\u4e00-\u9fa5]有些数据库并不认这种写法。更稳妥的做法是在 SQL 正则可用的范围内使用[一-龥]或者干脆用[^A-Za-z0-9]来表示非中英文数字内容。不过这类匹配在不同数据库里表现差异很大我通常并不会把数据库正则当作中文分词的替代方案最多用来判断是否包含连续两个汉字这种粗粒度规则。关于转义最保险的做法是反斜杠在 SQL 字符串和正则引擎之间一共有两层解析。既然你控制不了不同数据库的底层行为那就尽量避开反斜杠。匹配普通不需要特殊含义的字符时用字符类包一层比如匹配小数点用[.]而不是\.匹配括号用[(]而不是\(。这个技巧能让你少写很多让人抓狂的双反斜杠。2.4 一次搞懂不同数据库的 REGEXP 函数差异我列一个我在项目里经常翻看的对照表方便你直接用数据库匹配运算符/函数替换函数提取函数正则可匹配中文MySQLREGEXP/RLIKEREGEXP_REPLACE8.0REGEXP_SUBSTR8.0视字符集而定PostgreSQL~、~*、!~、!~*REGEXP_REPLACEREGEXP_MATCHES、REGEXP_SUBSTR支持 UTF-8OracleREGEXP_LIKEREGEXP_REPLACEREGEXP_SUBSTR支持SQLite无内置需要扩展一般无一般无取决于扩展SQL Server原生不提供无原生无原生不适用这里值得解释一下 SQLite 和 SQL Server 的情况。SQLite 默认安装确实没有完整支持 REGEXP如果你直接跑WHERE name REGEXP ...很大概率会报no such function: REGEXP。这个错误不是你的正则写错了而是底层没注册这个扩展。SQL Server 的情况类似原生 T-SQL 里没有正则运算符大家常用LIKE、CHARINDEX或者 CLR 自定义函数来凑合。遇到这两个数据库我的建议是别硬刚改用自带能力或者把数据取到应用层处理。3. 六类真实业务场景的 SQL 正则实战3.1 字段格式校验邮箱、手机号、身份证的 SQL 写法正则最直接的用途就是数据校验。我刚接手上一个项目时发现客户表里邮箱乱七八糟于是就用一条 SQL 把异常邮箱揪了出来SELECT id, email FROM customer WHERE NOT email REGEXP ^[A-Za-z0-9._%-][A-Za-z0-9.-]\\.[A-Za-z]{2,}$;注意我这里用了\\.来匹配点号因为在 MySQL 字符串转义之后需要让正则引擎真正收到一个\.它才不会被当作任意字符。如果你嫌麻烦可以直接用[.]代替SELECT id, email FROM customer WHERE NOT email REGEXP ^[A-Za-z0-9._%-][A-Za-z0-9.-][.][A-Za-z]{2,}$;这段正则的前半段[A-Za-z0-9._%-]是邮箱用户名常用字符中间加域名部分用[A-Za-z0-9.-]最后[.]加顶级域。它不能保证邮箱真实存在但能把明显格式错误、或者根本没有和点号的数据筛出来。再比如手机号校验。每个国家的手机号规则不一样如果只做国内常见的手机号格式初筛可以写^1[3-9][0-9]{9}$。这条规则匹配以 1 开头第二位是 3 到 9后面还有 9 位数字的 11 位号码。这里要用[3-9]而不要写[0-9]不然会把一些不存在的号码段也算进去。实际业务里可能还要考虑虚拟号段但我的原则是校验规则宁可先严一点把可疑数据暴露出来也不要一开始就放太宽。身份证号的 SQL 正则就要更小心。它不止是 18 位数字最后一位还可能是 X。不少教程给你写^[1-9][0-9]{5}(19|20)[0-9]{2}(0[1-9]|1[0-2])[0-9]{3}[0-9Xx]$这只能做基础格式校验。比如生日里的 2 月 30 日、非法日期、以及最后一位校验码正则都很难完美判断。没有校验位逻辑的身份证正则只能用来筛长得很像身份证的数据不能作为最终有效性依据。3.2 数据清洗用 REGEXP_REPLACE 去掉杂质数据清洗是我最喜欢用正则的场景因为很多脏数据根本没有规律靠肉眼盯根本盯不过来。MySQL 8.0 以上、PostgreSQL、Oracle 基本都支持REGEXP_REPLACE用法非常直观把匹配到的内容替换成指定字符串。比如我在清洗商品描述时经常要删除 HTML 标签SELECT id, REGEXP_REPLACE(content, [^], ) AS clean_content FROM product LIMIT 100;[^]这个模式的意思很直白小于号开头然后匹配一个或多个非大于号的字符最后以大于号收尾。它能把p、div classxxx这类原本可能是任意内容的闭合标签处理掉。不过我必须泼一盆冷水正则不是解析 HTML 的正确工具嵌套标签、属性里携带的情况都会让它翻车。在 ETL 里做初步清洗可以但正式解析 HTML 应该用专门的解析器。另一个超级实用的场景是提取数字。比如订单备注里混着金额和手机号你只想把第一段连续数字拎出来SELECT id, REGEXP_SUBSTR(remark, [0-9]) AS first_number FROM orders WHERE remark REGEXP [0-9] LIMIT 20;[0-9]会匹配第一段连续数字。如果你想提取所有匹配项很多数据库还会提供REGEXP_EXTRACT_ALL或REGEXP_MATCHES之类的函数效果类似。这种操作特别适合在数仓里做特征提取但记得控制扫描行数别直接跑全表。3.3 从长文本里筛选目标记录业务表里经常会存在那种又臭又长的备注字段比如客服记录、工单内容、日志描述。你想找出其中包含网址或电话的记录LIKE 要写很多个条件而正则只需要一行SELECT id, note FROM service_log WHERE note REGEXP https?://[a-zA-Z0-9.-];这段模式能匹配http://或https://开头的网址。s?表示字母 s 出现 0 次或 1 次这是正则里非常经典的非贪婪分支写法。同理如果你要找出包含电话的记录可以把模式换成[0-9]{3,4}-[0-9]{7,8}用横线把区号和号码连起来。注意这里没有加^和$因为我要找的是备注中出现过的子串而不是整条字段完全匹配。这种包含即命中的行为正是 REGEXP 和 LIKE 最适合替代的场景。但也要记住WHERE 里的正则不会走普通索引每次查询都是全表扫描。所以我一般先在运营数据的小副本上跑或者加上日期分区限制。3.4 正则归一化后做 GROUP BY 去重统计很多同学搜过SQL 语句去重常见的写法是SELECT DISTINCT。但实际业务里很多重复并不是一模一样的重复比如手机号可能存成了138-1234-5678、13812345678、(138)1234-5678三种格式。如果直接用DISTINCT这三个会被当作三条数据。正确思路是先用正则把所有非数字字符清掉得到规范的手机号再做分组统计SELECT REGEXP_REPLACE(phone, [^0-9], ) AS clean_phone, COUNT(*) AS cnt FROM customer GROUP BY clean_phone HAVING COUNT(*) 1 ORDER BY cnt DESC;这段 SQL 在 MySQL 8.0 里可以直接跑[^0-9]匹配所有非数字字符把它们都替换成空字符串剩下的就是纯数字号码。HAVING COUNT(*) 1把重复的号码挑出来快速定位同一个客户是不是录入了多条记录。这里有个经验清洗规则要写清楚否则会把不同号码合并成一个。比如固定电话和手机号如果都只保留数字01012345678和1012345678可能会混在一起一定要结合业务规则判断是否只取后 11 位或加区号处理。3.5 用正则做输入白名单校验防止 SQL 注入聊到SQL 注入很多人以为只要过滤几个关键词就万事大吉。其实更有效的做法是白名单校验用正则限定用户的输入只能由哪些字符组成。比如用户名字段只允许字母、数字和下划线就可以写SELECT id FROM users WHERE username REGEXP ^[A-Za-z0-9_]{3,20}$;这种方式可以从根上排除掉很多拼接进 SQL 的恶意内容因为、--、;这类特殊字符根本不在白名单里。不过我要强调一点正则校验只能作为辅助防线永远不能替代参数化查询或预编译语句。真正安全的核心还是不要让用户输入直接拼 SQL这点比任何正则都重要。在数据接入层用正则做格式校验还有一个好处可以从源头拒绝坏数据。哪怕一条记录只有个别字符异常也会让下游报表出现难以解释的脏数据。我在做爬虫数据入库时会用类似的正则在写入前跑一遍把明显不是目标格式的数据丢进待人工审核表。3.6 正则相关的慢 SQL 优化思路正则查询一旦上了生产环境最常碰到的性能问题就是慢 SQL。我在性能排查时第一步永远是EXPLAIN看一下执行计划。如果看到type ALL就说明数据库在走全表扫描需要认真想优化方案。WHERE remark REGEXP ...这种条件天然无法利用普通 B 树索引因为索引建立在原始值上而正则要计算中间结果。我的优化思路大致有三种把需要复杂匹配的字段拆成独立标记列。比如预先清洗出has_phone、has_url这种布尔字段后续查询直接走索引列判断。使用数据库的生成列或表达式索引。PostgreSQL 可以对REGEXP_REPLACE(phone, [^0-9], )建立表达式索引MySQL 也支持生成列但依然要看字段类型和匹配规则。限制扫描范围。多给查询加时间范围、状态范围等条件尽可能减少参与正则匹配的行数。说到底正则查询的本质是算力换灵活。能用简单字段解决就别用正则能提前清洗就别在查询时临时算这个原则比任何调优技巧都重要。4. 性能、常见报错与排查技巧4.1 可怕的灾难性回溯正则引擎在最坏情况下会进入灾难性回溯表现就是一条看似简单的 SQL 在几千万行数据上跑很久都出不来。比如(a)$这种嵌套量词一旦匹配失败引擎会反复尝试无数种拆分方式CPU 直接被拉满。我踩过一次很痛的坑用一行WHERE content REGEXP (.*?)somekeyword去查日志表结果数据库 CPU 报警差点把线上服务拖垮。后来我把正则改成更精确的somekeyword只查关键词本身速度立刻恢复正常。教训是什么在生产环境跑正则之前先用小数据集验证一下匹配结果和耗时特别是不要在大表上直接执行含有多个.*、嵌套括号和加号的正则。可以适当增加锚点^$或者前面加固定字符串给正则引擎更多可确定的信息。4.2 数据库兼容性导致的常见报错不同数据库抛出同一个 REGEXP 问题的表现完全不一样。我把工作中遇到过的高频问题整理成一张速查表现象可能原因解决方法正则里写\d结果匹配到字母dSQL 字符串转义把反斜杠吞掉了用[0-9]代替或写成\\dSQLite 报no such function: REGEXPSQLite 默认未注册正则函数加载扩展或用LIKE/GLOB替代SQL Server 提示 REGEXP 不是内置函数SQL Server 原生不支持正则用LIKE、CHARINDEX迁移时改造逻辑中文匹配不到字符集、排序规则或正则实现不同表字段确认 utf8mb4用[一-龥]粗匹配同一正则两个库查出不同结果大小写敏感度不同明确标识是否BINARY或者用 PostgreSQL 的~*正则能找到子串但加^$后查不到字段包含前后空白或换行先用TRIM或改成允许\r\n的模式这类问题最麻烦的地方是你在本地工具里用 PCRE 语法测试完全正常一拿到数据库里就变了。所以跨库使用前面我给的对照表非常有必要至少不会把 PostgreSQL 的\d习惯直接搬到 MySQL 里踩坑。4.3 测试正则前先做三件事我一直强调正则线上运行前要做测试但很多人会直接拿一个SELECT去生产库扫。我是这么做的第一步从生产表抽取一小批有代表性的样本放到本地或测试环境。第二步用 Python、Perl 或你熟悉的语言先把正则跑一遍确认匹配结果和预期一致。第三步在测试库执行EXPLAIN看执行计划同时把WHERE条件加一个LIMIT用部分数据验证 SQL 有没有语法问题。等这些都通过了再把完整查询或UPDATE脚本放进事务里执行执行前先备份。说到底SQL 里的正则不是不能在生产用而是要用得有分寸。它是我做数据探查和清洗时的高效工具却不是每条线上查询都需要的万能药。这段经验希望对你有帮助少走几个我走过的弯路。