MySQL正则表达式触发无限循环及查询超时问题求助

问题背景 #

使用以下正则表达式验证邮箱格式时，匹配末尾带空格的邮箱（如sdasa@kj.nhg ）会触发无限循环，导致查询超时报错，而非返回预期的true/false：

^[[:alnum:]]+([_\.\-]?[[:alnum:]]+)*@[[:alnum:]]+([_\.\-]?[[:alnum:]]+)*(\.[[:alnum:]]{2,4})+$

对应的完整SQL查询：

SELECT
    cusworkemail NOT REGEXP '^[[:alnum:]]+([_\.\-]?[[:alnum:]]+)*@[[:alnum:]]+([_\.\-]?[[:alnum:]]+)*(\.[[:alnum:]]{2,4})+$' AS invalid_value,
    cusworkemail,
    num,
    cusid_list
FROM (
    SELECT
        IFNULL(cusworkemail, '') AS cusworkemail,
        count(*) AS num,
        GROUP_CONCAT(DISTINCT cusid) AS cusid_list
    FROM (SELECT cusid, cusworkemail FROM dealCRM.cus WHERE cusworkemail != '' AND cusworkemail IS NOT NULL) AS t
    GROUP BY cusworkemail
    ORDER BY num DESC
    -- LIMIT 0, 10000
    ) AS c
HAVING invalid_value;

核心疑问：

• 正则为何陷入无限循环？
• 能否让MySQL在正则超时后返回false？
• 正则解析器为何不检测重复状态？

1. 无限循环的根源：回溯爆炸 #

你的正则存在NFA回溯陷阱，以([_\.\-]?[[:alnum:]]+)*这段为例：

• [_\.\-]?是可选的分隔符（下划线/点/连字符）
• [[:alnum:]]+匹配至少一个字母数字
• 外层*允许整个组重复任意次数

当匹配到不符合规则的字符（如末尾空格）时，正则解析器会启动回溯机制：它会不断拆分[[:alnum:]]+的匹配内容，尝试让[_\.\-]?匹配不同位置，试图找到符合整个正则的可能。但由于[_\.\-]?是可选的，[[:alnum:]]+又支持任意长度，这种组合会产生指数级的回溯尝试，最终触发MySQL的正则匹配超时（表现为无限循环）。

以sdasa@kj.nhg 为例：正则匹配完nhg后遇到空格，而结尾要求(\.[[:alnum:]]{2,4})+$，空格不满足规则，解析器就会疯狂回溯前面的([_\.\-]?[[:alnum:]]+)*部分，尝试所有可能的拆分组合，直到耗尽时间限制。

2. 解决超时与返回false的方案 #

MySQL没有内置的“检测无限循环返回false”的功能，但可以通过以下方式规避：

修复正则表达式（彻底解决回溯问题） #

调整正则逻辑，消除歧义性，避免无意义回溯：

^[[:alnum:]]+(?:[_\.\-][[:alnum:]]+)*@[[:alnum:]]+(?:[_\.\-][[:alnum:]]+)*\.[[:alnum:]]{2,4}$

改动说明：

• 将([_\.\-]?[[:alnum:]]+)*改为(?:[_\.\-][[:alnum:]]+)*：去掉可选符?，要求分隔符必须跟在字母数字后，消除回溯歧义；使用非捕获组(?:...)提升性能
• 若需支持多级域名（如a.b.c.com），可保留结尾的+：(\.[[:alnum:]]{2,4})+$，确保每一层都是.[字母数字]的结构

提前清理无效输入 #

在子查询中先去除邮箱首尾空格，避免无效输入触发回溯：

SELECT cusid, TRIM(cusworkemail) AS cusworkemail 
FROM dealCRM.cus 
WHERE TRIM(cusworkemail) != '' AND cusworkemail IS NOT NULL

限制正则匹配时间（特定版本支持） #

Percona Server等衍生版本支持regexp_time_limit参数，可设置正则匹配的最大毫秒数，超时后返回false。标准MySQL需通过应用层拆分查询、批量处理数据来降低单次匹配压力。

替换为轻量验证逻辑 #

若仅做基础邮箱格式检查，可改用字符串函数组合，避免复杂正则：

-- 示例：检查是否有且仅有一个@，且后缀长度符合要求
SELECT
    (LOCATE('@', cusworkemail) = 0 OR LOCATE('@', cusworkemail) = CHAR_LENGTH(cusworkemail) 
     OR CHAR_LENGTH(RIGHT(cusworkemail, CHAR_LENGTH(cusworkemail)-LOCATE('@', cusworkemail))) < 3) AS invalid_value
FROM ...

3. 正则解析器的状态检测问题 #

MySQL采用的是Henry Spencer经典NFA正则库，这类解析器没有内置重复状态检测机制。NFA在处理存在大量回溯可能的正则时，会穷尽所有匹配路径，直到耗尽时间/资源，不会主动识别并终止无意义的循环。

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