如何在MySQL中返回REGEXP匹配结果及匹配位置？

云原生阿岚

2026-5-20

嘿，这个问题我之前也踩过坑！MySQL的REGEXP在提取匹配内容和位置上确实不像Python、Java这类语言那么顺手，但其实有更规范的实现方式，完全不用去遍历所有可能的子字符串~

一、返回REGEXP的匹配结果

如果你的MySQL版本是8.0及以上，那恭喜，官方已经提供了专门的REGEXP_SUBSTR()函数，直接就能提取匹配正则的子串，效率比遍历子字符串高多了。

提取单个匹配结果

假设你的表是your_table，目标列是Y，正则表达式是aa*b，可以这么写：

SELECT 
  Y AS original_string,
  REGEXP_SUBSTR(Y, 'aa*b') AS matched_result
FROM your_table
WHERE Y REGEXP 'aa*b';

先通过WHERE过滤出符合正则的行，再用REGEXP_SUBSTR提取第一个匹配的子串。

提取所有匹配结果

如果字符串里有多个符合aa*b的子串，想要全部提取的话，可以结合递归CTE来实现：

WITH RECURSIVE matches AS (
  -- 初始查询：提取第一个匹配项
  SELECT 
    Y AS original_string,
    REGEXP_SUBSTR(Y, 'aa*b', 1, 1) AS matched_result,
    1 AS match_count
  FROM your_table
  WHERE Y REGEXP 'aa*b'
  
  UNION ALL
  
  -- 递归查询：提取下一个匹配项，直到没有结果为止
  SELECT 
    m.original_string,
    REGEXP_SUBSTR(m.original_string, 'aa*b', 1, m.match_count + 1),
    m.match_count + 1
  FROM matches m
  WHERE REGEXP_SUBSTR(m.original_string, 'aa*b', 1, m.match_count + 1) IS NOT NULL
)
SELECT original_string, matched_result
FROM matches;

这个递归会不断查找下一个匹配项，直到REGEXP_SUBSTR返回NULL，就能得到所有匹配结果了。

二、获取匹配项的位置

同样，MySQL 8.0+提供了REGEXP_INSTR()函数，直接返回匹配子串在原字符串中的起始位置（注意：MySQL里的位置是从1开始计数的）。

获取单个匹配的位置

如果只需要第一个匹配项的起止位置：

SELECT 
  Y AS original_string,
  REGEXP_SUBSTR(Y, 'aa*b') AS matched_result,
  REGEXP_INSTR(Y, 'aa*b') AS start_position,
  -- 计算结束位置：起始位置 + 匹配子串长度 - 1
  REGEXP_INSTR(Y, 'aa*b') + LENGTH(REGEXP_SUBSTR(Y, 'aa*b')) - 1 AS end_position
FROM your_table
WHERE Y REGEXP 'aa*b';

获取所有匹配的位置

结合递归CTE，可以一次性拿到所有匹配项的位置信息：

WITH RECURSIVE matches AS (
  SELECT 
    Y AS original_string,
    REGEXP_SUBSTR(Y, 'aa*b', 1, 1) AS matched_result,
    REGEXP_INSTR(Y, 'aa*b', 1, 1) AS start_position,
    1 AS match_count
  FROM your_table
  WHERE Y REGEXP 'aa*b'
  
  UNION ALL
  
  SELECT 
    m.original_string,
    REGEXP_SUBSTR(m.original_string, 'aa*b', 1, m.match_count + 1),
    REGEXP_INSTR(m.original_string, 'aa*b', 1, m.match_count + 1),
    m.match_count + 1
  FROM matches m
  WHERE REGEXP_SUBSTR(m.original_string, 'aa*b', 1, m.match_count + 1) IS NOT NULL
)
SELECT 
  original_string,
  matched_result,
  start_position,
  start_position + LENGTH(matched_result) - 1 AS end_position
FROM matches;

小提示

如果需要区分大小写匹配，可以在函数里添加参数'c'，比如REGEXP_SUBSTR(Y, 'aa*b', 1, 1, 'c')，或者用REGEXP_BINARY替代REGEXP。
如果你还在使用MySQL 5.x版本，这些函数是没有的，虽然可以用LOCATE等字符串函数变通实现，但局限性很大，建议优先升级到8.0版本，用官方提供的工具更可靠。

内容的提问来源于stack exchange，提问作者reinierpost