Oracle与MySQL中REGEXP_SUBSTR正则匹配结果差异问题

问题重现 #

执行相同的REGEXP_SUBSTR语句，Oracle与MySQL返回结果不一致：

SQL语句 #

SELECT
  REGEXP_SUBSTR('http://www.example.com/products',
                'http://([[:alnum:]]+\.?){3,4}/?') "REGEXP_SUBSTR"
  FROM DUAL;

输出对比 #

• Oracle 输出：

  REGEXP_SUBSTR
  http://www.example.com/
  

• MySQL 输出：

  REGEXP_SUBSTR
  http://www.example.com/products
  

同时，MySQL官方说明其正则基于ICU库实现，但你提供的ICU测试代码（正则使用{3}而非原SQL的{3,4}）输出为http://www.example.com/，与Oracle结果一致。

核心原因：正则重复匹配的贪婪行为差异 #

问题出在正则表达式中的([[:alnum:]]+\.?){3,4}部分：

• 该表达式定义的是匹配3到4次由字母数字（[[:alnum:]]+）加可选点号（\.?）组成的组。
• Oracle的正则引擎在处理该重复匹配时，优先匹配到3组（www.、example.、com），再匹配后续的/?（即末尾的/），因此得到http://www.example.com/。
• MySQL的正则引擎则遵循极致贪婪匹配逻辑，尽可能匹配最多的次数（4次）：它将products也识别为符合[[:alnum:]]+\.?的组（点号匹配0次），此时后续的/?匹配0次（products后无/），最终匹配结果包含了products部分。

你提供的ICU测试代码中使用的是{3}（固定3次匹配），而非原SQL的{3,4}，因此不会触发贪婪匹配到第4组的情况，输出与Oracle一致。若将ICU测试的正则改为{3,4}，结果会与MySQL相同。

解决方案：锚定匹配范围或限制重复组规则 #

要让MySQL返回与Oracle一致的结果，可修改正则表达式明确匹配范围：

1. 固定重复次数为3次，避免引擎匹配第4组：

SELECT
  REGEXP_SUBSTR('http://www.example.com/products',
                'http://([[:alnum:]]+\.?){3}/?') "REGEXP_SUBSTR"
  FROM DUAL;

2. 或者锚定到域名后的/，确保匹配到域名部分后停止：

SELECT
  REGEXP_SUBSTR('http://www.example.com/products',
                'http://([[:alnum:]]+\.?){3}/') "REGEXP_SUBSTR"
  FROM DUAL;

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