MariaDB提取Articles表每行所有图片链接的SQL查询问题

先修正正则表达式问题 #

你原查询里的正则有笔误：gpeg应该是jpeg，这会导致部分包含jpeg格式的图片无法匹配，直接返回空。修正后的匹配模式应为：\d+\.(jpg|png|jpeg)，可以解决部分行返回空的问题。

提取所有匹配项的方案（适配MariaDB 10.4.19） #

MariaDB 10.4.x还不支持REGEXP_SUBSTR的全局匹配参数，要提取每行的所有图片文件名，可使用递归CTE来遍历所有匹配：

方案1（表存在唯一主键如id时使用）

WITH RECURSIVE img_matches AS (
    -- 初始步骤：提取每行第一个匹配的图片名，记录下一次匹配的起始位置
    SELECT 
        id,
        body,
        REGEXP_SUBSTR(body, '\\d+\\.(jpg|png|jpeg)', 1, 1) AS img_filename,
        REGEXP_INSTR(body, '\\d+\\.(jpg|png|jpeg)', 1, 1) + LENGTH(REGEXP_SUBSTR(body, '\\d+\\.(jpg|png|jpeg)', 1, 1)) AS next_pos
    FROM articles
    WHERE REGEXP_SUBSTR(body, '\\d+\\.(jpg|png|jpeg)', 1, 1) IS NOT NULL

    UNION ALL

    -- 递归步骤：从上一次匹配的结束位置继续提取下一个图片名
    SELECT 
        id,
        body,
        REGEXP_SUBSTR(body, '\\d+\\.(jpg|png|jpeg)', next_pos, 1) AS img_filename,
        next_pos + LENGTH(REGEXP_SUBSTR(body, '\\d+\\.(jpg|png|jpeg)', next_pos, 1)) AS next_pos
    FROM img_matches
    WHERE REGEXP_SUBSTR(body, '\\d+\\.(jpg|png|jpeg)', next_pos, 1) IS NOT NULL
)
SELECT id, img_filename
FROM img_matches
ORDER BY id;

方案2（表无唯一主键时使用）

如果表没有id这类唯一标识，先给每行生成行号再处理：

WITH numbered_articles AS (
    SELECT 
        ROW_NUMBER() OVER () AS row_id,
        body
    FROM articles
),
RECURSIVE img_matches AS (
    SELECT 
        row_id,
        body,
        REGEXP_SUBSTR(body, '\\d+\\.(jpg|png|jpeg)', 1, 1) AS img_filename,
        REGEXP_INSTR(body, '\\d+\\.(jpg|png|jpeg)', 1, 1) + LENGTH(REGEXP_SUBSTR(body, '\\d+\\.(jpg|png|jpeg)', 1, 1)) AS next_pos
    FROM numbered_articles
    WHERE REGEXP_SUBSTR(body, '\\d+\\.(jpg|png|jpeg)', 1, 1) IS NOT NULL

    UNION ALL

    SELECT 
        row_id,
        body,
        REGEXP_SUBSTR(body, '\\d+\\.(jpg|png|jpeg)', next_pos, 1) AS img_filename,
        next_pos + LENGTH(REGEXP_SUBSTR(body, '\\d+\\.(jpg|png|jpeg)', next_pos, 1)) AS next_pos
    FROM img_matches
    WHERE REGEXP_SUBSTR(body, '\\d+\\.(jpg|png|jpeg)', next_pos, 1) IS NOT NULL
)
SELECT row_id, img_filename
FROM img_matches
ORDER BY row_id;

额外调整说明 #

• 如果图片文件名不全是数字开头（比如包含字母、下划线），把正则里的\d+改成[a-zA-Z0-9_]+即可适配更多文件名格式。
• 如果图片标签里带路径（比如/static/456.png），要提取纯文件名的话，可将正则调整为[^/]+\\.(jpg|png|jpeg)，匹配最后一个斜杠后的文件名部分。

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