使用正则表达式提取表行中href链接的SQL实现问询

你已经成功筛选出了包含href的记录，但要把字段里的所有href链接提取出来并剔除其他内容，得根据你使用的数据库类型来调整SQL语句，下面分两种常见情况给你说明：

MySQL 8.0+ 解决方案 #

MySQL 8.0及以上支持递归CTE和正则函数，我们可以通过递归提取每个href，再拼接成结果：

WITH RECURSIVE href_extractor AS (
    SELECT 
        id,
        web_data,
        REGEXP_SUBSTR(web_data, 'href="[^"]+"') AS href,
        REGEXP_REPLACE(web_data, 'href="[^"]+"', '', 1, 1) AS remaining_data
    FROM web_data_table
    WHERE web_data REGEXP 'href'
    
    UNION ALL
    
    SELECT 
        id,
        web_data,
        REGEXP_SUBSTR(remaining_data, 'href="[^"]+"') AS href,
        REGEXP_REPLACE(remaining_data, 'href="[^"]+"', '', 1, 1) AS remaining_data
    FROM href_extractor
    WHERE remaining_data REGEXP 'href'
)
SELECT 
    id,
    GROUP_CONCAT(href SEPARATOR ' ') AS web_data
FROM href_extractor
WHERE href IS NOT NULL
GROUP BY id;

原理说明 #

1. 递归CTE的初始查询：提取每行第一个匹配的href="..."，并移除这个匹配项得到剩余内容
2. 递归步骤：对剩余内容重复提取和移除操作，直到没有href匹配为止
3. 最后用GROUP_CONCAT把同一id下的所有href用空格拼接起来

PostgreSQL 解决方案 #

PostgreSQL的regexp_matches函数可以直接返回所有匹配的子串数组，再用array_to_string拼接：

SELECT 
    id,
    array_to_string(regexp_matches(web_data, 'href="[^"]+"', 'g'), ' ') AS web_data
FROM web_data_table
WHERE web_data ~ 'href';

正则说明 #

href="[^"]+" 是核心正则：

• href=" 匹配href属性的开头
• [^"]+ 匹配任意非双引号的字符（确保只提取到闭合的双引号为止）
• " 匹配属性的闭合引号

老版本MySQL（无CTE）的替代方案 #

如果你的MySQL版本低于8.0，需要创建一个自定义函数来循环提取所有href：

DELIMITER //
CREATE FUNCTION extract_all_hrefs(input_text TEXT) 
RETURNS TEXT
DETERMINISTIC
BEGIN
    DECLARE result TEXT DEFAULT '';
    DECLARE temp_href TEXT;
    DECLARE remaining_text TEXT DEFAULT input_text;
    
    WHILE remaining_text REGEXP 'href="[^"]+"' DO
        SET temp_href = REGEXP_SUBSTR(remaining_text, 'href="[^"]+"');
        SET result = CONCAT(result, ' ', temp_href);
        SET remaining_text = REGEXP_REPLACE(remaining_text, 'href="[^"]+"', '', 1, 1);
    END WHILE;
    
    RETURN TRIM(result);
END //
DELIMITER ;

-- 使用函数查询
SELECT id, extract_all_hrefs(web_data) AS web_data
FROM web_data_table
WHERE web_data REGEXP 'href';

这样就能得到你期望的输出啦！

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