提取匹配片段用什么函数
从字符串里按正则取出片段：MySQL 8 用 REGEXP_SUBSTR，可以指定起始位置、取第几次匹配以及匹配模式；PostgreSQL 用 regexp_match 返回数组（只取第一个匹配），要拿全部匹配得用 regexp_matches 加 'g' 标志。它们与 REGEXP_REPLACE 的区别是只取不替换，与 SUBSTRING 的区别是位置由模式决定而不是固定下标，因此字段格式变化时更稳。
基本信息
| MySQL 8.0 | REGEXP_SUBSTR(expr, pattern[, pos[, occurrence[, match_type]]]) |
|---|---|
| PostgreSQL | regexp_match(str, pattern[, flags]) 返回 text[]；regexp_matches(str, pattern, 'g') 返回集合 |
| 大小写 | MySQL 默认取决于 collation；PostgreSQL 默认区分大小写，可用 (?i) 或 'i' 标志 |
| 捕获组 | 两边都支持 () 提取子组，PG 按数组下标取，MySQL 返回整段匹配 |
| 无匹配时 | MySQL 返回 NULL；regexp_match 返回 NULL，regexp_matches 返回空集 |
典型使用场景
- 从日志文本里抽取订单号、流水号或 IP 地址
- 从地址字段里提取固定长度的区号或邮编
- 提取邮箱域名用于统计与风控
- 数据清洗：把混杂文本里的编号抽出来做关联
- 脱敏前定位需要替换的片段
- 从混合文本里拆分出结构化字段，替代按固定位置截取
调试正则时先单独跑一次提取语句看输出，再放进业务 SQL：正则的错误往往表现为「取到多余片段」而不是报错，只看最终结果很难发现。
如果对提取性能有要求，注意这类函数无法使用普通索引，过滤条件应尽量用其他等值列先缩小范围，再在结果上做正则提取。
MySQL 写法
MySQL 写法
SELECT REGEXP_SUBSTR('order-2026-000123-paid', '[0-9]{6}') AS order_no, # 000123
REGEXP_SUBSTR('a1b2c3', '[0-9]', 1, 2) AS second_digit, # 2：第 2 次匹配
REGEXP_SUBSTR('user@example.com', '[^@]+$') AS domain, # example.com
REGEXP_SUBSTR('ABC-123', '[0-9]+', 1, 1, 'c') AS case_sensitive; # 第 5 个参数控制匹配模式PostgreSQL 写法
PostgreSQL 写法
# 取第一个匹配的捕获组：regexp_match 返回数组，下标从 1 开始
SELECT (regexp_match('order-2026-000123-paid', '([0-9]{6})'))[1] AS order_no,
(regexp_match('user@example.com', '^(.+)@(.+)$'))[2] AS domain;
-- 一次取全部匹配：返回多行
SELECT m FROM regexp_matches('a1b2c3', '[0-9]', 'g') AS t(m);
-- 大小写无关匹配
SELECT (regexp_match('Order-ABC', 'abc', 'i'))[1] AS hit;两库能力对照
| 能力 | MySQL 8.0 | PostgreSQL |
|---|---|---|
| 单次提取 | REGEXP_SUBSTR(str, pat) | (regexp_match(str, pat))[1] |
| 指定第 N 次匹配 | REGEXP_SUBSTR(str, pat, 1, N) | 需用 regexp_matches 配合行号筛选 |
| 全部匹配 | 无直接函数 | regexp_matches(str, pat, 'g') |
| 取捕获组 | 默认返回整段匹配 | 返回数组，按下标取组 |
| 无匹配 | 返回 NULL | regexp_match 返回 NULL，regexp_matches 返回空集 |
MySQL 的 REGEXP_SUBSTR 从 8.0 才提供，5.7 及更早版本没有该函数；REGEXP 关键字匹配（WHERE col REGEXP '…'）在旧版本可用，但取片段不行，迁移脚本时要注意版本。
提取结果是字符串：要参与数值比较或排序就得显式转换，PostgreSQL 里直接拿它和整数比会报类型错误，需写成 (regexp_match(...))[1]::int；MySQL 会做隐式转换，但转换发生在表达式上，代价同样要留意。
正则没写锚点（缺 ^ 或 $）时容易匹配到非预期片段：例如在长文本里找 6 位数字，会命中日期或金额的一部分，取数前先用完整样例验证。