MySQL REGEXP_SUBSTR 怎样提取指定出现次数的文本

MySQL 的 REGEXP_SUBSTR() 默认只返回第一个匹配结果。要提取第 2 个、第 3 个或指定序号的匹配，不需要自己循环字符串，只要把目标序号放在第四个参数 occurrence 中即可；如果没有这个序号的匹配，函数返回 NULL。

最常用的写法是 REGEXP_SUBSTR(文本, 模式, 起始位置, 第几次, 匹配规则)。例如 REGEXP_SUBSTR('abc def ghi', '[a-z]+', 1, 3) 会返回 ghi。

一、先记住 REGEXP_SUBSTR 的参数顺序

MySQL 8.4 文档给出的形式是 REGEXP_SUBSTR(expr, pat[, pos[, occurrence[, match_type]]])。其中 expr 是被搜索的文本，pat 是正则模式，pos 是开始搜索的位置，occurrence 表示要取第几次匹配，match_type 控制大小写和换行等匹配方式。

二、用 occurrence 提取第 N 个匹配

假设一行文本中有多个由空格分隔的英文单词，模式 [a-z]+ 会从左到右找到每个连续的小写字母片段。第四个参数从 1 开始计数，因此取第三个单词时要写成 1, 3，而不是把 3 放到第三个参数位置。

-- 第四个参数 3 表示返回第 3 次匹配
SELECT
  REGEXP_SUBSTR('abc def ghi', '[a-z]+', 1, 3) AS third_word;

-- 输出：ghi

下面把同一段文本的第 1、2、3 次匹配放在一行，便于把序号和结果对应起来：

-- occurrence 从 1 开始，分别取三个匹配位置
SELECT
  REGEXP_SUBSTR('id=18; id=27; id=36', '[0-9]+', 1, 1) AS first_id,
  REGEXP_SUBSTR('id=18; id=27; id=36', '[0-9]+', 1, 2) AS second_id,
  REGEXP_SUBSTR('id=18; id=27; id=36', '[0-9]+', 1, 3) AS third_id;

-- 结果：18 | 27 | 36

如果数据来自表字段，可以把文本列直接传给第一个参数。不要先用 LIKE 判断再调用函数，因为真正的提取仍然由正则模式和 occurrence 决定。

-- 从日志列中提取第二个 key=value 数字片段
SELECT
  log_id,
  REGEXP_SUBSTR(log_text, '[0-9]+', 1, 2) AS second_number
FROM app_log;

三、pos 怎样影响第 N 个匹配

pos 是搜索起点，不是结果序号。它从 1 开始，并且会改变后续匹配的计数范围：函数会从这个字符位置开始找第一个匹配，再根据 occurrence 继续向后数。

-- 从第 10 个字符开始重新计数，再取遇到的第 2 个数字
SELECT REGEXP_SUBSTR(
  'code=18; code=27; code=36',
  '[0-9]+',
  10,
  2
) AS number_after_position;

-- 重要：pos 改变扫描起点，occurrence 仍表示从新起点开始数第几次

实际使用时，通常先固定 pos=1，只调整 occurrence。只有当文本前半段包含不想参与匹配的内容，或者要在某个偏移之后重新搜索时，才设置 pos。如果把它误当成“第几个匹配”，结果就会出现少一项或多一项的错位。

四、用 match_type 处理大小写和转义

当模式和文本的字符集、排序规则导致默认大小写行为不符合预期时，可以传入 c 强制区分大小写，或传入 i 忽略大小写。这个参数位于第五个位置，因此如果只想设置它，也要把 pos 和 occurrence 写出来。

-- c：区分大小写，只匹配小写 api
SELECT REGEXP_SUBSTR('API api Api', 'api', 1, 1, 'c') AS case_sensitive;

-- i：忽略大小写，按出现顺序取第二个 api
SELECT REGEXP_SUBSTR('API api Api', 'api', 1, 2, 'i') AS case_insensitive;

-- 关键点：第五个参数是 match_type，不能跳过前面的数字参数

模式中的反斜杠还要留意 MySQL 字符串转义规则。若模式需要匹配一个字面量反斜杠，通常要写成两个反斜杠；如果环境启用了 NO_BACKSLASH_ESCAPES，字符串转义规则会不同，应按当前 SQL 模式调整。

五、没有第 N 个匹配时怎样处理

REGEXP_SUBSTR() 在 expr 或 pat 为 NULL 时返回 NULL；当正则搜索不到匹配，或匹配次数不足指定的 occurrence，同样得到 NULL。这不是空字符串，后续应使用 IS NULL 判断。

-- 用 COALESCE 给没有第 4 个数字的文本一个业务展示值
SELECT
  COALESCE(
    REGEXP_SUBSTR('id=18; id=27', '[0-9]+', 1, 4),
    '未提供第4个编号'
  ) AS fourth_id;

-- 用 IS NULL 区分“确实没有第 N 个匹配”
SELECT
  CASE
    WHEN REGEXP_SUBSTR(message, '[0-9]+', 1, 2) IS NULL THEN '不足两个数字'
    ELSE '存在第二个数字'
  END AS match_state
FROM app_log;

六、按场景选择写法

七、常见问题

occurrence 能写 0 吗？

文章中的常规写法使用从 1 开始的匹配序号。需要“全部替换”时应看 REGEXP_REPLACE() 的语义，不要把 REGEXP_SUBSTR() 当成批量返回函数。

为什么我传了 3，却没有得到第三个结果？

检查 3 是否放在第四个参数位置，并确认模式实际产生了至少三次匹配。第三个参数是 pos，不是 occurrence；匹配次数不足时返回 NULL。

怎样取所有匹配？

REGEXP_SUBSTR() 一次只返回一个子串。若需要把所有结果拆成多行，通常要在应用层循环 occurrence，或根据数据模型使用更适合的拆分方案；不要把单次函数调用误认为集合返回。

总结

记住两个位置就够了：第三个参数 pos 决定从哪里开始搜索，第四个参数 occurrence 决定返回从这个起点数到的第几个匹配。默认取第一次，超过实际匹配次数返回 NULL；需要控制大小写时，再把 c 或 i 放到第五个参数。