使用REGEXP_SUBSTR提取邮箱遇pcre_exec匹配超限，求MariaDB解决方案

问题原因 #

这个错误是由于PCRE正则引擎的回溯/递归次数限制被触发。当处理全表数据时，某些格式异常或过长的login字段会让你的正则表达式（尤其是带*的重复匹配模式）产生大量回溯操作，超过了MariaDB默认的PCRE限制阈值。

解决方案 #

1. 调整MariaDB的PCRE系统变量 #

MariaDB提供了两个控制PCRE匹配限制的变量，你可以临时或永久调整它们：

• 临时调整（当前会话生效）：

  SET SESSION pcre.backtrack_limit = 1000000;
  SET SESSION pcre.recursion_limit = 100000;
  

  调整后重新执行你的SQL语句即可，数值可以根据实际情况增大（比如如果还是报错，继续调高）。

• 全局调整（所有新会话生效，需权限）：

  SET GLOBAL pcre.backtrack_limit = 1000000;
  SET GLOBAL pcre.recursion_limit = 100000;
  

  注意：全局设置需要SUPER权限，且重启MariaDB后会失效，要永久生效需修改配置文件（如my.cnf或my.ini）：

  [mysqld]
  pcre_backtrack_limit = 1000000
  pcre_recursion_limit = 100000
  

  修改后重启MariaDB服务。

2. 优化正则表达式，减少回溯开销 #

你的原始正则使用了捕获组，且重复模式可能引发不必要的回溯。可以做以下优化：

• 使用非捕获组（(?:...)）替代捕获组，减少引擎的资源消耗；
• 简化匹配逻辑，避免无意义的回溯。

优化后的正则示例：

REGEXP_SUBSTR(login, '[_a-z0-9-]+(?:\\.[_a-z0-9-]+)*@[a-z0-9-]+(?:\\.[a-z0-9-]+)*\\.[a-z]{2,3}')

另外，可以先过滤掉明显不包含邮箱特征的行，减少正则处理的数据集：

SELECT id,
       REGEXP_SUBSTR(login, '[_a-z0-9-]+(?:\\.[_a-z0-9-]+)*@[a-z0-9-]+(?:\\.[a-z0-9-]+)*\\.[a-z]{2,3}') clean_login,
       login
FROM account
WHERE login LIKE '%@%' -- 先排除不含@的行

3. 分批处理数据 #

如果调整变量和优化正则后仍无法解决，可以将数据分批处理，避免一次性处理全表数据：

DROP TABLE IF EXISTS tmp_test;
CREATE TABLE tmp_test (id INT, clean_login VARCHAR(255), login VARCHAR(255));

-- 分批插入，示例按id范围划分，每次处理10000行
INSERT INTO tmp_test
SELECT id,
       REGEXP_SUBSTR(login, '[_a-z0-9-]+(?:\\.[_a-z0-9-]+)*@[a-z0-9-]+(?:\\.[a-z0-9-]+)*\\.[a-z]{2,3}') clean_login,
       login
FROM account
WHERE id BETWEEN 1 AND 10000;

INSERT INTO tmp_test
SELECT id,
       REGEXP_SUBSTR(login, '[_a-z0-9-]+(?:\\.[_a-z0-9-]+)*@[a-z0-9-]+(?:\\.[a-z0-9-]+)*\\.[a-z]{2,3}') clean_login,
       login
FROM account
WHERE id BETWEEN 10001 AND 20000;

-- 重复以上步骤直到处理完所有数据

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