MySQL没有regexp_extract_all怎么办？3种替代方案性能对比（附存储过程实现）

MySQL没有regexp_extract_all怎么办？3种替代方案性能对比（附存储过程实现）

最近在做一个日志分析项目，需要从一堆杂乱的文本字段里批量提取出所有符合特定规则的子串，比如提取所有出现的订单号、邮箱或者URL。第一反应就是找有没有类似regexp_extract_all这样的函数，结果MySQL官方文档翻了个遍，发现它确实没有提供这个“开箱即用”的功能。这其实是个挺常见的需求，尤其是在做数据清洗、日志解析或者从非结构化文本中抽取结构化信息的时候。

面对这个问题，我们不能简单地抱怨MySQL功能不全，而是得拿出工程化的解决方案。毕竟，数据就在那里，业务需求也在那里，我们得想办法高效、稳定地把活干了。经过一番折腾和测试，我总结出了三种在MySQL中实现批量正则提取的主流方案，并且针对百万级的数据量做了详细的性能压测。这篇文章，我就把这几种方案的实现细节、优缺点以及性能对比毫无保留地分享出来，还会附上一个可以直接复用的存储过程，希望能帮你绕过我踩过的那些坑。

1. 理解核心挑战：为什么需要“提取所有”

在深入方案之前，我们得先搞清楚regexp_extract_all这类函数到底在解决什么问题。它不仅仅是执行一次正则匹配，而是要遍历整个字符串，找出所有符合模式的子串，并以集合（通常是数组或表）的形式返回。这与REGEXP_SUBSTR()只返回第一个或第N个匹配项有本质区别。

举个例子，假设我们有一个文本字段 log_text，内容为： “用户[ID:1001]于2023-10-27购买了商品[SKU:A123,B456,C789]，联系邮箱为user@example.com和backup@mail.com。”

我们的需求可能是：

• 提取所有形如 [ID:xxxx] 或 [SKU:xxx] 的标记及其内容。
• 提取所有出现的邮箱地址。

对于单个匹配，REGEXP_SUBSTR(log_text, '\\[[^]]+\\]', 1, 1) 可以轻松拿到第一个[ID:1001]。但如何拿到第二个[SKU:A123,B456,C789]，以及如何一次性拿到所有邮箱？这就是我们面临的挑战。手动写循环或者依赖应用层代码处理固然可以，但当数据量巨大、且处理逻辑需要紧密耦合在数据库查询中时（比如作为视图的一部分，或在ETL流程里），我们就必须在数据库层面找到高效的解决方案。

2. 方案一：循环调用 REGEXP_SUBSTR

这是最直观的思路：既然REGEXP_SUBSTR可以指定匹配项的出现次数（occurrence参数），那么我们就可以在一个循环或递归查询中，递增这个次数，直到匹配不到任何结果为止。

2.1 实现原理与SQL示例

我们可以利用MySQL的递归公共表表达式（Recursive Common Table Expression， 简称Recursive CTE）来模拟这个循环。Recursive CTE是MySQL 8.0引入的强大功能，非常适合处理这种“未知循环次数”的场景。

假设我们有一张表 user_logs，其中 raw_text 字段包含需要提取的文本。我们要提取所有格式为 {code:XXX} 的代码（其中XXX为数字）。

-- 适用于 MySQL 8.0+
WITH RECURSIVE extraction_cte AS (
    -- 初始查询：获取第一次匹配，并记录匹配位置等信息
    SELECT
        id,
        raw_text,
        REGEXP_SUBSTR(raw_text, '\\{code:[0-9]+\\}', 1, 1) AS matched_code,
        1 AS match_index,
        REGEXP_INSTR(raw_text, '\\{code:[0-9]+\\}', 1, 1) AS match_pos
    FROM user_logs
    WHERE REGEXP_INSTR(raw_text, '\\{code:[0-9]+\\}', 1, 1) > 0
    
    UNION ALL
    
    -- 递归部分：基于上一次匹配的位置，寻找下一次匹配
    SELECT
        c.id,
        c.raw_text,
        REGEXP_SUBSTR(c.raw_text, '\\{code:[0-9]+\\}', c.match_pos + 1, 1) AS matched_code,
        c.match_index + 1,
        REGEXP_INSTR(c.raw_text, '\\{code:[0-9]+\\}', c.match_pos + 1, 1) AS match_pos
    FROM extraction_cte c
    WHERE REGEXP_INSTR(c.raw_text, '\\{code:[0-9]+\\}', c.match_pos + 1, 1) > 0
)
SELECT id, matched_code, match_index
FROM extraction_cte
WHERE matched_code IS NOT NULL
ORDER BY id, match_index;

注意：这里的关键是REGEXP_INSTR函数的使用，它返回匹配子串的起始位置。在递归部分，我们从上一次匹配位置+1的地方开始搜索，避免了重复匹配同一段文本。match_index列清晰地标明了这是第几个匹配项。

2.2 优缺点分析

这种方案的优点在于逻辑清晰，与regexp_extract_all的概念最接近，并且能保留匹配项的顺序（通过match_index）。代码也相对易于理解和修改。

但是，它的缺点也非常明显：

• 性能开销大：每一行文本只要包含至少一个匹配，就会触发一次递归查询。匹配项越多，递归深度越大，产生的中间结果集也呈线性增长。对于海量数据或匹配项很多的文本，性能可能成为瓶颈。
• 版本限制：依赖MySQL 8.0+的Recursive CTE功能。对于仍在使用MySQL 5.7的旧系统，无法直接使用此方法。
• SQL复杂度高：查询语句较长，对于不熟悉递归CTE的开发者来说，维护成本较高。

3. 方案二：巧用字符串分割函数组合

如果你还在用MySQL 5.7，或者希望寻找一种更“轻量级”的解决方案，可以尝试将正则匹配与字符串分割函数结合起来。核心思路是：先用REGEXP_REPLACE将非匹配部分替换成一个统一的分隔符，然后再用字符串分割函数（如SUBSTRING_INDEX）将结果拆分成多行。

3.1 实现步骤拆解

我们继续用提取邮箱的例子。假设raw_text字段中混杂着多个邮箱。

第一步：标准化分隔 我们无法预知匹配项之间是什么字符。一个巧妙的做法是，用REGEXP_REPLACE把所有非邮箱的部分，替换成一个绝对不会在邮箱中出现的特殊字符（比如|），而保留邮箱本身。

SELECT
    raw_text,
    REGEXP_REPLACE(raw_text, '([^@\\s]+@[^@\\s]+\\.[^@\\s]+)', '|\\1|') AS replaced_text
FROM user_logs;

这个正则([^@\\s]+@[^@\\s]+\\.[^@\\s]+)匹配一个简单的邮箱格式，并用|邮箱|的形式包裹它。不匹配的部分（原文本中的其他内容）保持不变。但这样还不够，我们需要把所有“非包裹邮箱”的部分都变成分隔符。

更通用的做法是进行两次替换：

1. 将每个匹配项前后都加上唯一标记。
2. 将所有不包含此唯一标记的连续字符替换为分隔符。

第二步：分割字符串 经过第一步处理，字符串会变成类似 |email1@a.com||email2@b.com| 的形式。然后，我们可以结合SUBSTRING_INDEX函数和数字辅助表（或通过UNION生成序列）来将其拆分成多行。

-- 假设我们有一个数字辅助表 numbers，包含从1到足够大的连续整数（如1-20）
-- 或者使用内联方式生成序列
SELECT
    l.id,
    TRIM(BOTH '|' FROM SUBSTRING_INDEX(SUBSTRING_INDEX(l.replaced_text, '|', n.num), '|', -1)) AS extracted_email
FROM (
    SELECT id, REGEXP_REPLACE(raw_text, '([a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\\.[a-zA-Z]{2,})', '|\\1|') AS replaced_text
    FROM user_logs
) l
-- 生成一个1到10的序列，假设最多10个邮箱
CROSS JOIN (
    SELECT 1 AS num UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5
    UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10
) n
WHERE n.num <= 1 + (LENGTH(l.replaced_text) - LENGTH(REPLACE(l.replaced_text, '|', ''))) / 2
  AND SUBSTRING_INDEX(SUBSTRING_INDEX(l.replaced_text, '|', n.num * 2 - 1), '|', -1) != '';

提示：WHERE子句中的条件 n.num <= 1 + (LENGTH(...) ... ) / 2 用于动态计算一行文本中实际有多少个被|包裹的匹配项，避免返回空行。SUBSTRING_INDEX(... , n.num * 2 - 1, ...) 是为了精准定位到第N个匹配项的内容。

3.2 适用场景与局限

这个方案的优点是对MySQL版本要求低（5.7甚至更早版本都支持），且在一次查询中完成所有行的处理，避免了递归可能带来的深度问题。

它的局限性在于：

• 逻辑复杂：SQL语句非常绕，尤其是分割和去空逻辑，编写和调试难度大。
• 需要预知最大匹配数：需要生成一个足够大的数字序列（如1-100），如果实际匹配数超过这个序列，结果会不完整。序列过大又会造成不必要的计算和空行过滤开销。
• 正则表达式需谨慎：用于包裹匹配项的正则必须精确，否则替换逻辑会出错，导致分割混乱。
• 性能不稳定：REGEXP_REPLACE对长字符串进行全局替换本身有开销，后续的字符串分割和连接操作在数据量大时也可能较慢。

下表对比了方案一和方案二的主要特点：

4. 方案三：封装为可复用的存储过程

当上述查询逻辑需要在多个地方重复使用时，将其封装成存储过程（Stored Procedure）或函数（Function）是最佳的工程实践。这不仅能隐藏复杂度，提供统一的调用接口，还能在一定程度上优化执行计划。

4.1 存储过程设计与实现

我们将实现一个存储过程 sp_regexp_extract_all，它接收输入字符串、正则表达式模式，并返回一个包含所有匹配项的结果集。

DELIMITER //

CREATE PROCEDURE sp_regexp_extract_all(
    IN input_text TEXT,
    IN pattern_text TEXT
)
BEGIN
    DECLARE v_counter INT DEFAULT 1;
    DECLARE v_match TEXT;
    DECLARE v_pos INT DEFAULT 1;

    -- 临时表用于存储结果
    DROP TEMPORARY TABLE IF EXISTS tmp_matches;
    CREATE TEMPORARY TABLE tmp_matches (
        match_index INT,
        matched_value TEXT
    );

    -- 如果输入或模式为空，直接返回
    IF input_text IS NULL OR pattern_text IS NULL THEN
        SELECT * FROM tmp_matches;
        LEAVE;
    END IF;

    -- 循环提取匹配项
    WHILE v_pos > 0 DO
        -- 查找第v_counter个匹配项
        SET v_match = REGEXP_SUBSTR(input_text, pattern_text, 1, v_counter);
        SET v_pos = REGEXP_INSTR(input_text, pattern_text, 1, v_counter);

        IF v_match IS NOT NULL AND v_pos > 0 THEN
            INSERT INTO tmp_matches (match_index, matched_value) VALUES (v_counter, v_match);
            SET v_counter = v_counter + 1;
        ELSE
            SET v_pos = 0; -- 触发退出循环
        END IF;
    END WHILE;

    -- 返回结果
    SELECT match_index, matched_value FROM tmp_matches ORDER BY match_index;

    -- 清理临时表（可选，连接结束后会自动删除）
    DROP TEMPORARY TABLE IF EXISTS tmp_matches;
END //

DELIMITER ;

调用示例：

CALL sp_regexp_extract_all('请联系 support@company.com 或 sales@domain.cn 获取帮助。', '[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\\.[a-zA-Z]{2,}');

执行后将返回：

4.2 高级封装与性能考量

基础存储过程解决了单次调用问题。但在处理表数据时，我们通常希望针对每一行执行提取。为此，我们可以创建另一个存储过程或使用游标（CURSOR）来遍历表。

DELIMITER //

CREATE PROCEDURE sp_batch_regexp_extract_all(
    IN source_table_name VARCHAR(64),
    IN text_column_name VARCHAR(64),
    IN pattern_text TEXT
)
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE v_id INT; -- 假设主键是id
    DECLARE v_text TEXT;
    DECLARE cur CURSOR FOR 
        SELECT id, CONVERT(`column` USING utf8mb4) FROM `source_table_name`; -- 动态SQL需用预处理语句，此处简化
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    DROP TEMPORARY TABLE IF EXISTS tmp_all_matches;
    CREATE TEMPORARY TABLE tmp_all_matches (
        source_id INT,
        match_index INT,
        matched_value TEXT,
        PRIMARY KEY (source_id, match_index)
    );

    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO v_id, v_text;
        IF done THEN
            LEAVE read_loop;
        END IF;

        -- 这里可以内联循环逻辑，或调用之前的sp_regexp_extract_all逻辑
        -- 为了性能，通常将循环逻辑内联，避免多次创建/销毁临时表
        BEGIN
            DECLARE v_counter INT DEFAULT 1;
            DECLARE v_match TEXT;
            DECLARE v_pos INT;
            WHILE TRUE DO
                SET v_match = REGEXP_SUBSTR(v_text, pattern_text, 1, v_counter);
                SET v_pos = REGEXP_INSTR(v_text, pattern_text, 1, v_counter);
                IF v_match IS NOT NULL AND v_pos > 0 THEN
                    INSERT INTO tmp_all_matches (source_id, match_index, matched_value) 
                    VALUES (v_id, v_counter, v_match)
                    ON DUPLICATE KEY UPDATE matched_value = v_match; -- 防止重复
                    SET v_counter = v_counter + 1;
                ELSE
                    LEAVE;
                END IF;
            END WHILE;
        END;
    END LOOP;
    CLOSE cur;

    SELECT * FROM tmp_all_matches ORDER BY source_id, match_index;
    DROP TEMPORARY TABLE IF EXISTS tmp_all_matches;
END //

DELIMITER ;

注意：使用游标处理大量数据时性能可能不佳。在实际生产环境中，更推荐在应用层分批读取数据，然后对每一批数据调用单次处理的存储过程或函数，或者直接使用方案一的递归CTE进行集合操作，这通常比过程化的游标循环更快。

5. 百万级数据性能实测与选型建议

理论分析再多，不如实际跑个分。我在一个测试环境中，使用包含100万行随机文本的表，分别测试了三种方案提取所有“单词”（由字母组成，长度大于3）的性能。测试环境为MySQL 8.0.33，机器配置为4核CPU，16GB内存。

测试结果概要：

提示：性能测试结果严重依赖于数据特征（文本长度、匹配密度、正则复杂度）和服务器配置。上述数据仅为趋势性参考。

最终选型建议：

1. 首选方案一（递归CTE）：如果你的数据库是MySQL 8.0+，并且正则匹配模式相对固定，递归CTE方案是性能最好、代码最清晰的选择。它充分发挥了SQL声明式编程的优势，让优化器来决定执行计划。
2. 备选方案二（字符串分割）：如果你的环境是MySQL 5.7，且单行文本的匹配项数量较少且可预估，可以考虑此方案。务必仔细测试其性能，并确保预设的数字序列足够覆盖最大匹配数。
3. 谨慎使用方案三（存储过程）：存储过程更适合作为复杂逻辑的封装和统一接口，或者用于单次、小批量的数据清洗。避免用游标处理大数据集。对于批量处理，应优先考虑在应用层拆分成批次，或者尝试在存储过程中使用基于集合的操作（如模拟递归）来替代游标。
4. 终极建议：评估数据导出处理：如果数据量极其庞大（千万级以上），且正则提取逻辑非常复杂，与其在数据库内绞尽脑汁，不如考虑将数据导出到更擅长此类文本处理的系统中进行处理（如Python的Pandas + regex库，或Spark），处理完成后再将结果导回数据库。数据库的优势在于存储和关联查询，而非复杂的逐行文本计算。

在我自己的项目中，最终采用了方案一（递归CTE），因为它完美契合了MySQL 8.0的环境，并且在可读性和性能之间取得了最佳平衡。对于少数需要向下兼容5.7的模块，我编写了一个适配层，在5.7上使用方案二的优化版本（通过简化正则和严格控制序列长度），虽然SQL看起来有点“魔法”，但经过充分注释和测试，也能稳定运行。