1. 内容整体设计与思路拆解1.1 为什么你在SQL里需要正则表达式搞数据的人迟早会和正则表达式打交道。平时写SQL查数据我们最熟悉的是LIKE和IN这类操作符靠它们去匹配固定文本。但一旦碰上“找出所有手机号段是139或188开头、倒数第二位是奇数”这种需求LIKE要写多少条如果你一条条拼估计得把自己绕晕。REGEXPRegular Expression正则表达式是SQL里被严重低估的一个功能。它能让你在数据库层面直接完成文本模式的匹配、提取和校验不用把几百万行数据拉到应用层再慢慢过滤。很多同学一听到“正则”就头疼觉得那是脚本语言或者文本编辑器才用的东西。实际上主流数据库都内置了正则支持只是一线开发里用的人不多。我的经验是只要学会20%的正则知识就能解决80%的文本匹配需求。这篇文章我会直接拿真实业务场景说话把REGEXP的语法、函数、数据库差异、性能陷阱和排查方法一次讲透。不铺垫太多理论目标是让你看完就能在自己的SQL里落地。1.2 各数据库的正则支持现状先泼一盆冷水不同数据库的正则函数长得完全不一样。你如果在MySQL里写了REGEXP到SQL Server里直接报语法错误因为SQL Server原生不支持正则你得自己通过CLR去扩展或者退回去用LIKE。以下是几个主流数据库的现状对比方便你在不同环境里快速定位该用哪个函数。数据库支持正则的方式常用函数/操作符备注MySQL原生支持REGEXP、REGEXP_LIKE()、REGEXP_SUBSTR()、REGEXP_REPLACE()、REGEXP_INSTR()8.0之后函数化功能更完整MariaDB原生支持REGEXP、REGEXP_REPLACE()与MySQL语法大体兼容PostgreSQL原生支持最强~、~*、!~、REGEXP_MATCHES()、REGEXP_REPLACE()支持POSIX风格正则还有SIMILAR TOSQLite默认不支持需要编译扩展REGEXP需加载扩展若报no such function: REGEXP就是没启用扩展SQL Server原生不支持无需CLR集成或用LIKE折中方案Oracle原生支持REGEXP_LIKE、REGEXP_SUBSTR、REGEXP_REPLACE、REGEXP_INSTR、REGEXP_COUNT命名和MySQL不同注意区分这个表格我建议大家收藏起来后面哪段SQL在目标库上跑不通先过来核对语法能省不少排查时间。2. 核心语法拆解与匹配模式说明2.1 字面量与字符类的搭配逻辑还是先看最基础的用法。正则表达式的核心就两个概念匹配什么字符、匹配多少个字符。前者叫“字符类”后者叫“量词”。MySQL里的REGEXP操作符左边是字段名或字符串右边是正则模式匹配返回1不匹配返回0。比如SELECT abc123 REGEXP abc; -- 结果1这个太简单说点常用的字符类。[0-9]代表任意数字[a-zA-Z]代表任意字母[^0-9]代表非数字。量词里*代表0次或多次代表1次或多次?代表0次或1次。{n}代表精确n次{n,}代表至少n次。举个典型需求从一堆订单号里筛出符合“两位字母四位数字两位字母”格式的数据。SELECT order_no FROM orders WHERE order_no REGEXP ^[A-Za-z]{2}[0-9]{4}[A-Za-z]{2}$;这里加了^和$做边界限定分别表示字符串开头和结尾。不加边界的话只要任意位置出现符合条件的片段就会命中往往会导致误配。这个坑我在生产环境踩过筛选出来的数据量比预期多了一大截后来排查才发现是一条非法数据里中间某段碰巧符合模式。2.2 分组、捕获与后向引用的应用场景括号在正则里有两个作用一是分组把一串字符当作一个整体二是捕获把匹配到的内容单独提取出来。比如要清洗的表里电话号码和分机号混在一个字段里格式是13800138000-1234。你想把主号和分机拆开SELECT phone, REGEXP_SUBSTR(phone, ^(\\d{11})) AS main_phone, REGEXP_SUBSTR(phone, -(\\d{1,})$) AS extension FROM contacts WHERE phone REGEXP ^\\d{11}-\\d{1,}$;这里\\d在MySQL里需要双反斜杠转义因为字符串本身要先处理一次转义。正则引擎最终看到的是\d表示数字。{11}限定11位长度。^(\\d{11})就是匹配开头的11位数字并放入第一个捕获组。REGEXP_SUBSTR会把捕获到的那部分内容直接取出来。后向引用稍微进阶一点适合查重场景。比如找出连续重复两次的相同字母SELECT word FROM dictionary WHERE word REGEXP ([a-z])\\1;\\1表示引用第一个捕获组的内容。这个例子中只要任意位置出现连续两个相同字母就命中。做数据校验时比如检测用户设置的密码是否包含连续相同字符这个语法很实用。2.3 模式修饰符与贪婪匹配的差异正则里有个“贪婪”与“非贪婪”的概念一开始挺绕人。简单说贪婪模式会尽可能多地匹配字符非贪婪模式则尽可能少地匹配。看这个例子目标是从字符串p标题/pp正文/p中提取所有p标签里的内容SELECT REGEXP_SUBSTR(p标题/pp正文/p, p.*/p);在MySQL默认的贪婪模式下上面的查询会返回p标题/pp正文/p整段内容。因为.*会尽力往后吃字符直到最后一个/p才停下来。要改成非贪婪模式在MySQL里可以把.*写成.*?但需要注意MySQL的REGEXP引擎早期版本对非贪婪模式支持不完善8.0之后才比较可靠。PostgreSQL里则可以用.*?正常实现非贪婪匹配。所以在写SQL正则时一定要先确认当前数据库的正则引擎版本和行为。我的习惯是涉及贪婪/非贪婪的敏感场景先用SELECT配合纯字符串测一遍确认结果无误再上生产查询避免直接在大表上反复试错。3. 实操过程与核心环节实现3.1 数据清洗场景把乱七八糟的日志字段规整化我在一次日志表清洗工作中遇到过这样的字段来源地址信息是用户自助填写的格式五花八门有北京市-朝阳区-望京街道也有上海市浦东新区张江镇还有写广东 深圳 南山的。需要统一提取出“省级-市级”两级结构存到单独的两列里。这个场景直接用REGEXP_SUBSTR就能搞定。SELECT raw_address, REGEXP_SUBSTR(raw_address, ^[\\u4e00-\\u9fa5]{2,3}?(省|市|自治区)) AS province, REGEXP_SUBSTR(raw_address, (省|市|自治区)([\\u4e00-\\u9fa5]{2,8}?(市|州|区))) AS city FROM raw_address_table;等一下这里有个隐患。MySQL的REGEXP默认不支持\\u4e00-\\u9fa5这种Unicode范围写法。你得用[一-龥]这样的范围来代替或者开启ICU字符集支持。PostgreSQL则可以直接用Unicode属性\p{Han}。不同数据库在这一块的语法差异非常大我踩过坑所以列出来给你参考。数据库匹配中文的写法示例MySQL默认[一-龥]北京市 REGEXP [一-龥]{2,3}PostgreSQL\p{Han}北京市 ~ \p{Han}{2,3}Oracle[一-龥]或[:unicode:]REGEXP_LIKE(北京市, [一-龥]{2,3})上面SQL里我用(省|市|自治区)做了分组捕获省名结束的位置后面紧接着的城市名就基于这个分组结果往后提取。实际操作时如果地址里含有“自治区”三个字分组长度不固定所以用{2,8}匹配宽范围再用?非贪婪模式停到“市”或“州”为止。清洗完成后还建议顺手统计一下没匹配上的数据量SELECT COUNT(*) FROM raw_address_table WHERE raw_address NOT REGEXP ^[一-龥]{2,3}?(省|市|自治区);这样可以快速估算数据质量针对性补录或人工处理。这个“先统计不匹配数量、再动手清洗”的思路推荐你用上省得清洗完了才发现漏了一大批脏数据。3.2 数据校验场景手机号、邮箱和身份证的REGEXP写法数据校验是正则用得最频繁的领域。比如CRM系统里客户手机号录入后端提交前要做校验数据库层面也要二次把关。手机号校验大陆11位手机号段SELECT phone, phone REGEXP ^1[3-9]\\d{9}$ AS valid_flag FROM customers;解释一下^1表示以1开头[3-9]表示第二位在3到9之间\\d{9}表示后面跟9位数字所以总长度是11位。这个模式比较宽松对于当前开放的号段基本够用。如果要收紧可以针对具体号段多写几个分支比如^(13[0-9]|15[0-9]|18[0-9]|17[0-9])。邮箱校验的经典写法是SELECT email, email REGEXP ^[A-Za-z0-9._%-][A-Za-z0-9.-]\\.[A-Za-z]{2,}$ AS valid_flag FROM user_table;这里[A-Za-z0-9._%-]允许常见邮箱前缀字符后面是域名部分\\.[A-Za-z]{2,}表示至少两字母的顶级域名。实际使用中我见过不少漏网之鱼比如用户填了testexample没有顶级域名后缀这个模式能拦下来。但要提醒你邮箱格式本身没有绝对统一的标准这个模式属于实用主义写法能挡掉绝大多数错误输入但无法做到100%符合RFC规范。身份证号码校验是网上搜“java身份证校验”时经常连带的SQL需求。18位身份证包含17位数字加最后一位数字或XSELECT id_card, id_card REGEXP ^\\d{17}[0-9Xx]$ AS format_flag FROM users;注意这只是格式校验不代表身份证号真实有效连校验位都没验。更严格的校验需要写存储过程或UDF去算验证码这里就不展开了。3.3 日志分析与数据提取REGEXP_SUBSTR的高级用法日志表里的数据往往是一长串拼接文本需要提取特定的键值对。比如Nginx访问日志里URL参数被记录在request_uri字段中类似/api/user?id1024typeviptaggold。要从里面提取id的值SELECT request_uri, REGEXP_SUBSTR(request_uri, id([0-9]), 1, 1, e) AS extracted_id FROM access_log WHERE request_uri REGEXP id[0-9];注意MySQL 8.0里REGEXP_SUBSTR的最后一个参数e表示返回捕获组的内容如果不传这个参数默认返回整个匹配到的字符串即id1024。早期我在这里栽过跟头返回结果带上id前缀还得额外嵌套一层REPLACE才能清理掉。如果你用的MySQL版本低于8.0REGEXP_SUBSTR不存在只能退而求其次用SUBSTRING配合LOCATE截取或者用REGEXP_REPLACE把不匹配的部分替换成空串来间接提取。比如SELECT REGEXP_REPLACE(request_uri, ^.*id([0-9]).*$, \\1) AS extracted_id FROM access_log WHERE request_uri REGEXP id[0-9];这条的原理是用正则匹配完整字符串并将目标部分放入捕获组1然后整体替换为捕获组1的内容。效果上等同提取虽然性能不如专用函数但胜在兼容老版本。我这边的建议是能用8.0就用8.0正则在8.0里的函数化支持配合索引和生成列使用体验比老版本好很多。3.4 替换与脱敏场景REGEXP_REPLACE的实践路径数据脱敏是我日常做报表时的高频需求。手机号中间四位要打码邮箱用户名部分要隐藏原来用嵌套几层REPLACE硬写也能做但遇到格式不统一的情况就很痛苦。手机号统一脱敏为138****8000SELECT phone, REGEXP_REPLACE(phone, ^(\\d{3})\\d{4}(\\d{4})$, \\1****\\2) AS masked_phone FROM customers;这段正则把11位手机号分成三部分前3位、中间4位、后4位然后将中间部分替换为星号。\\1和\\2引用前后两段保留内容。邮箱脱敏保留第一个字符和后面的域名SELECT email, REGEXP_REPLACE(email, ^(.)[^]*(.*)$, \\1****\\2) AS masked_email FROM users;这个模式的含义是第一个字符捕获到\\1然后[^]*把之前剩余部分全部匹配掉(.*)$捕获从开始到结尾的内容。替换结果类似t****example.com。要注意的是如果邮箱前缀只有一个字符比如ab.com上面的正则实际上匹配不到因为[^]*要求至少吃掉一个非字符。实际数据中单字符邮箱非常少见但如果你的场景特别在意覆盖率可以先通过REGEXP_LIKE(email, ^[^]{2})筛出长度1的邮箱做脱敏其余单独处理。还有一个通用心得做脱敏之前先做一次REGEXP_REPLACE(email, [], #)测试模式是否正确确认结果符合预期再批量跑。脱敏是敏感操作一旦写错数据就不可逆务必小心。4. 常见问题与排查技巧实录4.1 为什么REGEXP查不出数据但LIKE可以遇到最多的问题明明字段里有目标字符串LIKE %abc%能查到换成REGEXP abc却查不到。这种情况十有八九是边界问题。REGEXP abc和LIKE %abc%在语义上确实等价在不加锚点的情况下都应该匹配出包含“abc”的行。但如果你的正则里带了^或$或者数据库在正则匹配时默认采用了全值匹配逻辑结果就会完全不同。比如SQLite的REGEXP扩展实现里如果模式没写^和$某些版本会做部分匹配而另外一些版本可能走全值匹配行为并不统一。MySQL相对稳定一些从5.7到8.0REGEXP一直默认部分匹配。PostgreSQL的~操作符也是部分匹配。遇到这类问题时最直接的办法是先用SELECT 实际字符串 REGEXP 目标模式测一下匹配行为排除数据层面的干扰。我有个排查固定套路先用一条SELECT 常量 REGEXP 模式确认正则引擎对模式的判断。再查SELECT 字段 FROM 表 LIMIT 10观察字段值是否包含不可见字符比如换行、回车、空格。最后再跑大表查询。很多REGEXP匹配失败的案例最后都发现是数据里有前导空格或CRLF换行符正则没写\\s*去兼容。4.2 正则匹配慢如蜗牛索引怎么救正则匹配在大表上跑得慢是必然的。因为REGEXP几乎无法利用普通B树索引它要做全表扫描加逐行匹配本身的计算成本比等值判断高很多。我处理过一个真实案例一张500万行的订单表需要按备注字段筛选出一批特定格式的订单号。原始SQL长这样SELECT * FROM orders WHERE remark REGEXP ORD-[0-9]{8}-[A-Z]{2};查询跑了几十秒业务方差点投诉。最终优化方案是两步走第一步增加一个生成列Generated Column把需要匹配的文本片段提取成独立字段ALTER TABLE orders ADD COLUMN remark_order_no VARCHAR(30) GENERATED ALWAYS AS (REGEXP_SUBSTR(remark, ORD-[0-9]{8}-[A-Z]{2})) STORED;第二步在生成列上建索引CREATE INDEX idx_remark_order_no ON orders(remark_order_no);生成列在插入和更新时自动计算值并且STORED模式会物理存储。虽然写入性能有一定损耗但对于查询远多于写入的业务这个代价完全可以接受。优化后原来几十秒的查询降到毫秒级因为后续可以直接用WHERE remark_order_no IS NOT NULL或等值匹配来代替正则。如果你的数据库不支持生成列比如SQLite旧版本可以退而求其次应用层在写入时额外维护一个标准化字段。但这就会增加逻辑复杂度得靠代码保证一致性。4.3 SQL注入场景与安全写法的提醒热搜词里反复出现“sql注入”“万能密码绕过”这里想专门提醒一下正则的安全边界。正则本身不会引发SQL注入但如果你把用户输入直接拼进正则表达式就可能在数据库层面被利用。比如一个搜索功能用户输入的关键词被拼进正则以模糊搜索-- 反例勿用 SELECT * FROM products WHERE name REGEXP .* || user_input || .*;攻击者可以构造特殊正则比如(?s).*这类模式修饰符或者a{1,9999999}这类超长量词触发灾难性回溯ReDoS导致数据库CPU飙升。更稳妥的做法是正则模式永远写死在SQL里用户输入仅作为普通文本参数进行绑定不参与模式拼接。如果确实需要把用户输入做成模糊搜索用LIKE CONCAT(%, ?, %)配合参数绑定反而更安全高效。另外提醒一点正则表达式用于校验的入参时要在后端和数据库两层都做校验依赖单一数据库层的正则校验不够全面。校验逻辑本身不复杂但应用场景里的组合问题很多。4.4 SQLite、SQL Server和Navicat环境下的REGEXP坑不少同学在自己电脑上装了Navicat连接SQLite调试SQL一执行REGEXP就报错no such function: REGEXP。我在前面表格里提过SQLite默认不带正则函数需要加载扩展。网上很多教程让你重新编译SQLite操作成本太高日常调试期可以直接用GLOB替代SELECT * FROM t WHERE name GLOB *abc*;GLOB大小写敏感且使用*和?通配符和正则不完全等价但能解决大部分简单模糊匹配的需求。SQL Server用户就比较尴尬了原生没有正则。我的建议是如果只是固定格式匹配LIKE配合[0-9]这类模式够用了。SQL Server的LIKE支持%、_和[...]字符集匹配虽然能力弱于正则但覆盖常见需求足够。如果业务确实需要完整正则再考虑CLR集成或改用带正则支持的数据库组件。Navicat这个工具本身不提供正则引擎它只是把SQL发给数据库执行。所以你在Navicat里看到“REGEXP报错”或“语法不对”问题在数据库端与客户端无关。排查时要把精力放到数据库版本和引擎支持上别在客户端设置里浪费时间。4.5 常见正则模式速查表最后上一张速查表都是实际业务里高频出现的模式可以直接复制改改就用。需求描述REGEXP模式适用数据库11位大陆手机号^1[3-9]\\d{9}$MySQL / PostgreSQL身份证格式^\\d{17}[0-9Xx]$MySQL / PostgreSQL邮箱格式^[A-Za-z0-9._%-][A-Za-z0-9.-]\\.[A-Za-z]{2,}$MySQL / PostgreSQL提取URL中的IDid([0-9])配合REGEXP_SUBSTR/REGEXP_REPLACE匹配连续重复字母([a-z])\\1MySQL / PostgreSQL中文2~3字省份名^[一-龥]{2,3}?(省|市|自治区)MySQL订单号格式^ORD-[0-9]{8}-[A-Z]{2}$MySQL / PostgreSQL去除HTML标签[^]配合REGEXP_REPLACE这些模式在实际使用中要根据数据样本做微调别拿过来直接上生产。先用小表或常量测试验证再扩大范围。我的个人习惯是建立一个“正则模式库”文档把每次业务验收通过的模式记录进去写明数据库版本和适用场景。下次再遇到类似需求直接查文档能省掉大量的重复试错时间。正则这门手艺靠的是积累不是突击记忆。