正则匹配的两种语法体系
按模式匹配字符串时，两种数据库提供的是不同的语法：MySQL 用运算符形式，从 8.0 起还提供了一组正则函数，底层换成 Unicode 正则实现；PostgreSQL 用一组波浪号运算符区分大小写与不区分大小写，另有配套的处理函数，遵循的是一套较老的 POSIX 风格规则。写跨库语句时要注意两类差异：运算符写法完全不同，以及大小写敏感性由什么决定。
基本信息
| 适用平台 | MySQL 8.0 / PostgreSQL 12 及以上 |
|---|---|
| MySQL 写法 | 运算符形式匹配，另有查找、替换、截取、定位一组函数 |
| PostgreSQL 写法 | 四种波浪号运算符区分大小写与取反，配替换与拆分函数 |
| 大小写规则 | MySQL 与列的排序规则相关，PostgreSQL 由运算符本身决定 |
| 索引可用性 | 正则在多数情况下无法利用普通索引 |
典型使用场景
- 校验格式：手机号、身份证号、邮编这类固定模式
- 提取片段：从冗余文本里取出年月、编号等关键部分
- 模糊匹配：条件比简单通配更复杂时使用
- 数据清洗：批量替换文本中的统一格式
- 排查脏数据：找出不符合既定格式的记录
两库写法对照
两库写法对照
-- MySQL 8：运算符匹配与正则函数
SELECT * FROM users WHERE name REGEXP '^A[0-9]{3}$';
SELECT REGEXP_REPLACE('a-b-c', '-', '_'); -- 结果是 a_b_c
SELECT REGEXP_SUBSTR('order-2024-01', '[0-9]{4}'); -- 结果是 2024
SELECT REGEXP_INSTR('abc123', '[0-9]'); -- 返回首个数字的位置
-- PostgreSQL：区分大小写与不区分大小写两套运算符
SELECT * FROM users WHERE name ~ '^A[0-9]{3}$'; -- 区分大小写
SELECT * FROM users WHERE name ~* '^a[0-9]{3}$'; -- 不区分大小写
SELECT * FROM users WHERE name !~ 'test'; -- 取反
SELECT regexp_replace('a-b-c', '-', '_', 'g'); -- 全局替换需要加标志
SELECT regexp_split_to_array('a,b,c', ','); -- 拆分成数组能力对照
| 需求 | MySQL 8 | PostgreSQL |
|---|---|---|
| 条件匹配 | 正则运算符 | 波浪号运算符 |
| 不区分大小写 | 随列排序规则变化 | 另有专用运算符 |
| 取反匹配 | 用否定形式组合 | 有专门的取反运算符 |
| 提取子串 | 有专门的正则截取函数 | 用匹配函数返回数组 |
| 替换 | 正则替换函数 | 正则替换函数，需指定全局标志 |
| 索引利用 | 通常无法利用普通索引 | 通常无法利用普通索引 |
MySQL 的匹配运算符是否区分大小写取决于列的排序规则，不要默认它区分大小写。需要在两种情况下行为一致时，显式控制大小写或在函数里指定匹配模式。
正则条件会让数据库逐行计算，无法走索引。大表上先用可索引的等值或范围条件把候选集缩小，再用正则做二次筛选，代价差别很大。