做数据清洗或者后台开发的时候我经常遇到这类需求一张会员表里存的手机号五花八门有的带横杠有的带空格还有的直接混进了汉字日志表里要提取IP和状态码订单备注里要把“#”号后面的编号抠出来。这类问题用LIKE写条件会非常痛苦一个规则一个规则地拼SQL长到没法维护。MySQL内置的正则表达式能力就是干这个的它就是数据库层面的文本匹配与模式检索工具一条SQL就能完成复杂的模糊判断、字段提取和批量替换。这篇内容围绕 MySQL 正则表达式展开从 REGEXP 基础语法讲到 REGEXP_SUBSTR、REGEXP_REPLACE 等四个函数的完整用法再进入转义、字符集、性能优化和常见深坑。适合刚接触数据库正则的开发人员也适合天天跟脏数据打交道的运维和数据分析同学。1. 从LIKE到REGEXPMySQL里正则匹配的地基用法1.1 REGEXP和RLIKE到底是什么关系先解决一个很多人问过的问题MySQL里的REGEXP和RLIKE有什么区别答案是没区别RLIKE就是REGEXP的别名写哪个都行。SELECT * FROM user_info WHERE mobile REGEXP ^138; SELECT * FROM user_info WHERE mobile RLIKE ^138;这两条SQL完全等价。我个人习惯写REGEXP因为可读性更好JAVA、Python等语言里都用这个词。REGEXP的核心作用是判断一个字符串是否包含某个模式片段。注意是“包含”不是“完全等于”。比如WHERE mobile REGEXP 138会匹配所有包含138这三个连续字符的号码不管是开头、中间还是结尾。1.2 LIKE和REGEXP的本质差异LIKE用的是通配符只有%和_两个元字符语义是“整个字符串的模糊匹配”。REGEXP用的是完整的正则语法语义是“在字符串中寻找模式的片段”。对比项LIKEREGEXP通配符%任意多字符、_单个字符完整正则语法如.*、[0-9]、^、$匹配方式默认匹配整个字符串默认部分匹配字符串中任一段命中即可大小写受collation影响一般忽略大小写默认同样受collation影响可加BINARY强制区分性能前缀abc%可走索引绝大部分情况全表扫描举一个最能说明差异的例子。表里有两条记录abc和xxabcxxSELECT abc LIKE abc; -- 1 SELECT xxabcxx LIKE abc; -- 0 SELECT xxabcxx REGEXP abc; -- 1REGEXP abc不做锚定的话只要字符串里出现了abc就算匹配。如果要求和LIKE一样匹配整个字符串必须显式加锚点^abc$。这个特性是新手最容易踩的第一个坑后面实战部分还会展开讲。1.3 一个正常人看得懂的入门例子假设会员表里有个字段存手机号但历史数据很脏CREATE TABLE members ( id INT PRIMARY KEY, phone VARCHAR(20) ); INSERT INTO members VALUES (1, 13812345678), (2, 139-1234-5678), (3, 021-88886666), (4, 1581234abcd);现在要找出所有手机号格式明显不对的记录一条SQL搞定SELECT id, phone FROM members WHERE phone NOT REGEXP ^1[3-9][0-9]{9}$;这个模式拆开看^1要求以1开头[3-9]第二位是3到9之间的数字[0-9]{9}后面是9个数字$锚定结尾。结果会返回2和4——一个带了横杠一个带了字母。对比传统写法要用LIKE 1%配合长度函数、排除非数字等一堆条件正则明显更干净。基础用法就这么简单接下来进入真正能提升效率的部分四个正则函数。2. 四个正则函数判断、提取、替换与定位的完整链路MySQL 8.0把正则能力从单一的REGEXP判断扩展成了一整套函数。它们分别是REGEXP_LIKE、REGEXP_SUBSTR、REGEXP_REPLACE、REGEXP_INSTR。函数作用返回结果REGEXP_LIKE(str, pat)判断是否匹配1/0/NULLREGEXP_SUBSTR(str, pat)提取匹配的子串命中的文本否则NULLREGEXP_REPLACE(str, pat, rep)替换匹配的子串替换后的完整文本REGEXP_INSTR(str, pat)定位匹配出现的位置起始下标否则02.1 REGEXP_LIKE只做判断REGEXP_LIKE在8.0里被明确提出来功能和REGEXP操作符一致但语义更清晰而且它能用match_type参数控制匹配行为。SELECT REGEXP_LIKE(abc123, [0-9]); -- 1 SELECT REGEXP_LIKE(abc, [0-9], c); -- 0c代表大小写敏感 SELECT REGEXP_LIKE(ABC, [a-z], c); -- 0match_type参数里常用的有c大小写敏感i忽略大小写m让^、$匹配每一行的开头结尾而不是整个字符串。如果表里一个字段存了多行文本m就特别有用。SELECT REGEXP_LIKE(line1\nerror line2, ^error, m); -- 1不加m的话^只匹配整个字符串的起点这条SQL会返回0。2.2 REGEXP_SUBSTR把字符串抠出来这个函数是数据清洗神器。日志表里存了一整行文本想提取里面的IP地址SELECT log_text, REGEXP_SUBSTR(log_text, ([0-9]{1,3}\\.){3}[0-9]{1,3}) AS ip FROM access_log;注意SQL里点号写成了\\.这是因为正则表达式里点号是任意字符要匹配字面意义上的点必须转义成\.而SQL字符串本身还要求再转义一层反斜杠。更实用的是配合捕获分组。从订单备注订单A123#456已发货中提取两个#号中间的数字SELECT REGEXP_SUBSTR(订单A123#456已发货, #([0-9])#, 1, 1, c, 1) AS number_str;最后一个参数1是subexpr表示返回第一个捕获分组。这个参数是MySQL 8.0.17之后才加上的之前想提取分组内容没法直接做到只能先提取整个匹配再人工收拾。搜索热词里提到“提取出中间的数字及#符号后的字符串”这个场景用REGEXP_SUBSTR非常合适。如果数据里有连续多个#数字#的片段第四个参数occurrence可以指定取第几个。2.3 REGEXP_REPLACE批量清洗脏字符数据清洗最常用的场景是去掉字符串里的所有非数字字符UPDATE raw_phone_list SET phone REGEXP_REPLACE(phone, [^0-9], );[^0-9]表示匹配任何不是数字的字符把它们全部替换成空串。执行完之后139-1234-5678就变成了13912345678。再比如把文本里连续出现的多个空格压缩成一个SELECT REGEXP_REPLACE(a b c, , ); -- 结果a b c2.4 REGEXP_INSTR从“有没有”进阶到“在哪”REGEXP_INSTR返回匹配的起始位置从1开始匹配不到返回0。它在需要配合SUBSTRING做手工截取时有用。SELECT REGEXP_INSTR(abc123def, [0-9]); -- 结果4这个函数比LOCATE强大得多因为LOCATE只能找静态字符串而REGEXP_INSTR可以直接定位一个模式。比如从一堆混合文本里找到第一个连续数字出现的位置再结合LEFT、MID去截取。四个函数基本覆盖了正则操作闭环判断有没有、提取在哪、替换成什么、定位在何处。正常业务开发里REGEXP_LIKE和REGEXP_SUBSTR用得多REGEXP_REPLACE在做数据修复时价值最大。3. 转义、字符类与中文匹配最容易翻车的语法细节3.1 MySQL里的转义是双层的这一点必须单独拿出来讲。正则模式本身有转义规则而字符串写法又有自己的转义规则。两层规则叠加导致一个正则里如果想表达“匹配一个点号”你写的是\\.而如果匹配反斜杠本身要写\\\\。SELECT REGEXP_LIKE(www.example.com, example\\.com); -- 1 SELECT REGEXP_LIKE(path\\to\\file, \\\\\\\\); -- 1很多人把JAVA或Python里的正则模式直接粘到MySQL里发现匹配不出来十有八九就是转义层数不对。我的习惯是先在脑子里把SQL字符串解码一遍看看MySQL实际收到的模式是什么再按正则规则去解读。比如\\.在SQL层面收到的是\.到正则引擎里才是“字面意义上的点号”。3.2 字符类与POSIX写法MySQL支持常见的字符类写法上有一点要特别注意POSIX字符类在方括号内使用时要再套一层[]。SELECT REGEXP_LIKE(abc123, [[:alpha:]]); -- 1 SELECT REGEXP_LIKE(abc123, [[:digit:]]); -- 1[:alpha:]单独拿出来看它是一组“字符类名”要放进一个字符集合里使用所以写成了[[:alpha:]]。这个双括号结构经常让人懵理解成“集合里面套类名”就不容易忘了。写法含义[0-9]或[[:digit:]]数字[a-zA-Z]或[[:alpha:]]英文字母[[:space:]]所有空白字符[[:punct:]]标点符号[[:alnum:]]字母数字3.3 中文匹配的实际做法中文匹配在MySQL里绕不开一个现实UTF-8编码下一个汉字由多个字节组成而正则的量词如{2}、默认作用在字符上还是字节上不同版本行为有差异。稳妥的做法是直接匹配具体的字或词组而不是用[一-龥]这种范围匹配。SELECT REGEXP_LIKE(深圳市南山区, 深圳|南山); -- 1 SELECT REGEXP_LIKE(深圳市南山区, [\\x{4E00}-\\x{9FFF}]); -- 依赖版本不稳定实际业务里我几乎不用Unicode范围去匹配中文而是用白名单词组去匹配。比如判断地址里有没有一线城市直接写法是SELECT REGEXP_LIKE(北京市朝阳区, 北京|上海|广州|深圳);中文环境下还有一个隐藏问题默认的collation是utf8mb4_general_ci时不区分大小写但对中文来说无所谓大小写。真正影响中文匹配的是字符集本身所以设计表时尽量统一utf8mb4。3.4 转义在括号表达式里的特殊情况在字符集合内部很多特殊字符不需要转义。比如[.]直接表示点号写[\.]反而在某些版本里是错误写法。这里有一个经验法则方括号外面的特殊字符才需要转义方括号里面的特殊字符优先按字面理解。SELECT REGEXP_LIKE(file.txt, file[.]txt); -- 1 SELECT REGEXP_LIKE(file.txt, file\\.txt); -- 1都行但是如果想匹配方括号本身比如要匹配一个[字符写法就变成了\\[又是一层转义。4. 真实场景实战数据清洗、日志提取与字段分类打标4.1 场景一手机号与邮箱格式清洗某运营表里的用户联系方式字段完全放飞既有正常格式也有各种符号和乱填。需求是标记出疑似无效的数据。SELECT id, contact, CASE WHEN contact REGEXP ^1[3-9][0-9]{9}$ THEN 有效手机号 WHEN contact REGEXP ^[a-zA-Z0-9._%-][a-zA-Z0-9.-]\\.[a-zA-Z]{2,}$ THEN 有效邮箱 ELSE 疑似无效 END AS contact_status FROM user_contact;这一条CASE WHEN替代了原来的十几个IF判断。实际跑完把疑似无效筛选出来导出人工复核整体效率提升非常明显。4.2 场景二从日志文本中提取关键词与IP系统跑批任务的日志表里存了整段文本需要每天统计出现ERROR的次数以及对应的服务器IP。传统做法是把日志拉出来用脚本处理但数据量大了以后直接在SQL里提取更省事。SELECT server_name, COUNT(*) AS error_cnt, REGEXP_SUBSTR(log_text, ip[: ]([0-9]{1,3}\\.){3}[0-9]{1,3}) AS ip FROM task_log WHERE REGEXP_LIKE(log_text, ERROR|Exception|FATAL) GROUP BY server_name, ip;REGEXP_SUBSTR配顺序写在这里有一个坑如果日志里出现了多个IP它只会返回第一个。要拿全部IP需要结合递归CTE或者把日志拆开处理单独写正则解决不了。但用来做初步统计这个SQL足够撑起日常巡检。4.3 场景三提取#符号后面的编号订单备注的格式是类型#编号#附加说明需要把中间的数字编号提取出来。这个直接对应热词里“提取出中间的数字及#符号后的字符串”的需求。SELECT order_id, remark, REGEXP_SUBSTR(remark, #([0-9])#, 1, 1, c, 1) AS order_no FROM orders;如果是MySQL 5.7版本没有REGEXP_SUBSTR还有一套替代方案先用REGEXP_REPLACE把#号后到结尾的部分全部删除再把#号前面删掉。写法是SELECT order_id, SUBSTRING_INDEX( SUBSTRING_INDEX(remark, #, 2), #, -1 ) AS order_no FROM orders;两种方式结果一致但逻辑上正则写法明显更好懂。升级到8.0之后这类提取需求我全改REGEXP_SUBSTR了。4.4 场景四基于正则的字段分类打标业务上经常需要对模糊文本做分桶。比如根据用户填写的地址判断城市层级。这个问题适合用REGEXP批量打标。SELECT user_id, addr, CASE WHEN addr REGEXP 北京|上海|广州|深圳 THEN 一线城市 WHEN addr REGEXP 杭州|南京|成都|武汉|西安|重庆 THEN 新一线城市 WHEN addr REGEXP [0-9]号 THEN 有详细门牌地址 ELSE 其他 END AS addr_tag FROM user_addr;正则的优势是可以在一个表达式里列出几十个候选关键词用竖线分隔SQL看起来还是一行。换成LIKE的话几个城市就要拼好几个OR LIKE可读性会迅速恶化。再配合UPDATA和WHERE可以把标记结果写回字段减轻后续报表逻辑的压力UPDATE user_addr SET addr_tag CASE WHEN addr REGEXP 北京|上海|广州|深圳 THEN 一线城市 WHEN addr REGEXP 杭州|南京|成都 THEN 新一线城市 ELSE 其他 END;5. 性能真相正则在MySQL里的扫描代价与优化三板斧5.1 不要指望正则走普通索引这个结论要直说REGEXP和四个正则函数在绝大多数情况下不会使用BTREE索引优化器对它们的处理方式基本都是全表扫描和LIKE完全不同。LIKE的abc%形式能走索引是因为优化器可以把它改写成范围查询而正则是真正的逐行模式匹配。EXPLAIN SELECT * FROM members WHERE phone REGEXP ^138;执行计划里看到的通常是typeALL、keyNULL意味着它会把整个表的每一行都扫描一遍并执行正则匹配。几百上千行的配置表无所谓如果是几百万行的业务表一条带正则的查询就能把数据库拖出明显压力。5.2 优化三板斧先缩范围再做正则在第一板斧最容易想到先用低成本条件缩小数据范围再对结果集做正则。比如要统计错误日志可以先按日期字段过滤到当天数据SELECT * FROM task_log WHERE log_date CURRENT_DATE AND log_text REGEXP ERROR;这样虽然正则还是扫描但扫描的行数从全表缩小到当天分区代价减低一个数量级。如果表没有分区至少也要保证有一个时间字段能走索引。第二板斧是把必须用正则的场景转化为前缀LIKE。很多需求看起来是“以某个模式开头”实际写成LIKE前缀更划算-- 代价高 SELECT * FROM member WHERE mobile REGEXP ^138; -- 代价低且能走索引 SELECT * FROM member WHERE mobile LIKE 138%;如果确实需要对整串做完整格式校验可以分两步mobile LIKE 138% AND mobile REGEXP ^138[0-9]{8}$。先让LIKE收窄范围再让正则做精确校验两者语义不冲突。第三板斧是生成列加索引。MySQL 8.0支持在生成列里使用确定性表达式包括REGEXP_SUBSTR。如果业务里频繁按照“日志中提取的某个编号”来查询可以先建生成列再对这个列建索引。CREATE TABLE app_log ( log_id INT PRIMARY KEY, log_text VARCHAR(500), user_id INT GENERATED ALWAYS AS ( CAST(REGEXP_SUBSTR(log_text, user_id([0-9]), 1, 1, c, 1) AS UNSIGNED) ) STORED, INDEX idx_user_id (user_id) );这样之后的WHERE user_id 12345就是索引点查而不是全表正则匹配。这个方案在处理重复、固定的正则提取需求时非常高效相当于把正则的代价从查询阶段转移到了写入阶段。5.3 正则模式的编译与匹配开销还有一个容易被忽略的性能点正则表达式的复杂度。模式里写(a|b|c|d|e|f)这类存在大量回溯风险的模式在长文本上的匹配开销会成倍增长。日志文本又往往很长一个糟糕的正则可能让单条匹配耗时从微秒级涨到毫秒级乘上几百万行就是一个灾难。优化思路是让正则尽快失败。用^锚定、避免过度使用.*、减少嵌套分组这三点对匹配性能影响很大。能用字符类表达的范围不要用多个竖线枚举能精确限定长度不要用无上限的和*。6. 踩坑实录我反复遇到的七个正则深坑6.1 坑一REGEXP默认是部分匹配正则表达式不带锚点时只要字符串里有一小段命中就算匹配通过。WHERE status REGEXP OK会把NOT_OK也选中因为OK出现在字符串尾部。所有要求“整串符合格式”的判断都必须写成^模式$的形式。这个坑在从LIKE逻辑迁移到正则时尤其常见。6.2 坑二转义层数永远搞不清我见过很多次有人把JAVA代码里能跑的正则直接扔进SQL结果匹配不到任何数据。原因就是字符串转义多了一层。排查方法很简单先对模式单独执行一个最简单的查询逐步加字符看到底哪一步开始失效。反复确认的结论是MySQL里匹配数字[0-9]不用转义匹配点号要写\\.匹配反斜杠要写\\\\。6.3 坑三REGEXP对NULL的传染性这个非常阴险。如果被匹配的字段是NULL那么REGEXP_LIKE(NULL, ...)返回的不是0而是NULL。在WHERE条件里NULL会被当作不成立不会报错但会静默排除掉这些行。更麻烦的是在CASE WHEN里NULL会直接落到ELSE分支导致统计结果失真。排查时先用IS NULL单独把空值调出来看再回头审查正则结果。6.4 坑四REGEXP_SUBSTR提取不到时返回NULL在UPDATE里用REGEXP_SUBSTR覆盖字段时匹配不到的行会变成NULL如果后续还要用这个字段做字符串拼接结果是整段变NULL。这是我的真实教训一次批量清洗某行数据没有匹配项结果把原本正常的备注字段整体覆盖成NULL了。正确做法是先WHERE过滤完再UPDATE或者用COALESCE把NULL兜底成原值。6.5 坑五贪婪匹配在REPLACE里的意外表现正则默认是贪婪的.*会尽可能匹配更多内容。REGEXP_REPLACE在替换时如果模式能匹配空串会在每个位置都执行替换。比如REGEXP_REPLACE(abc, x*, -)的结果是-a-b-c-因为x*在字符之间也能匹配长度为0的子串。这些看起来反直觉的结果只有在实际跑过之后才会真正有记忆。6.6 坑六大小写敏感度由collation决定MySQL的REGEXP大小写行为并不是一概而论。如果字段的collation是utf8mb4_general_ciREGEXP默认不区分大小写如果是utf8mb4_bin则区分。同一个正则模式在不同表上表现可能完全不同。强制区分大小写最稳的写法是加BINARYSELECT REGEXP_LIKE(ABC, BINARY abc); -- 返回06.7 坑七MySQL 5.7和8.0的方言差异5.7到8.0MySQL把正则引擎换成了ICU导致部分语法行为不一致。最典型的是词边界5.7时代常用的[[::]]和[[::]]在8.0里不再被支持需要改用\\b。-- 5.7可用的写法 SELECT cat REGEXP [[::]]cat[[::]]; -- 8.0推荐写法 SELECT cat REGEXP \\bcat\\b;老项目升级到8.0时把存量正则模式过一遍语法兼容性检查是必做的一步。做完整套正则函数梳理和踩坑复盘之后我个人最深的体会是正则表达式在MySQL里的定位不是银弹而是一把精细的手术刀。它能把过去要拉出数据再写脚本处理的活直接下沉到SQL层面让数据在源头就被清洗和结构化。我习惯把常用的正则模式记录到一张配置表里标明表名、字段和用途下次遇到同类型清洗需求直接查询配置复用稍微改改边界条件就能跑。工具和版本会一直迭代但“先把要匹配的业务模式想清楚再动手写正则”这个顺序永远不会变。
