如何在MariaDB中用纯SQL提取VARCHAR列全部匹配项为行（无需存储过程）

可以通过**递归CTE（公共表表达式）**实现，无需存储过程或自定义函数，MariaDB 10.2及以上版本支持该特性。

假设你的表名为my_table，目标列是my_string，以下是可直接使用的SQL语句：

WITH RECURSIVE matches AS (
    -- 初始步骤：提取每行第一个匹配项
    SELECT 
        id, -- 替换为你的表的唯一标识列，用于关联原数据行
        my_string,
        REGEXP_SUBSTR(my_string, '[xy][0-9]+') AS matched_value,
        -- 计算下一次匹配的起始位置
        REGEXP_INSTR(my_string, '[xy][0-9]+') + LENGTH(REGEXP_SUBSTR(my_string, '[xy][0-9]+')) AS next_pos
    FROM my_table
    WHERE my_string REGEXP '[xy][0-9]+' -- 过滤无匹配的行

    UNION ALL

    -- 递归步骤：提取后续匹配项
    SELECT 
        id,
        my_string,
        REGEXP_SUBSTR(my_string, '[xy][0-9]+', next_pos) AS matched_value,
        next_pos + LENGTH(REGEXP_SUBSTR(my_string, '[xy][0-9]+', next_pos)) AS next_pos
    FROM matches
    WHERE REGEXP_SUBSTR(my_string, '[xy][0-9]+', next_pos) IS NOT NULL -- 无匹配时终止递归
)
SELECT id, matched_value
FROM matches
ORDER BY id, next_pos;

无唯一标识列的适配方案 #

如果你的表没有id这类唯一标识列，可以用ROW_NUMBER()生成临时行标识，避免不同行的匹配项混淆：

WITH RECURSIVE numbered_rows AS (
    SELECT 
        ROW_NUMBER() OVER () AS row_id,
        my_string
    FROM my_table
    WHERE my_string REGEXP '[xy][0-9]+'
),
matches AS (
    SELECT 
        row_id,
        my_string,
        REGEXP_SUBSTR(my_string, '[xy][0-9]+') AS matched_value,
        REGEXP_INSTR(my_string, '[xy][0-9]+') + LENGTH(REGEXP_SUBSTR(my_string, '[xy][0-9]+')) AS next_pos
    FROM numbered_rows

    UNION ALL

    SELECT 
        row_id,
        my_string,
        REGEXP_SUBSTR(my_string, '[xy][0-9]+', next_pos) AS matched_value,
        next_pos + LENGTH(REGEXP_SUBSTR(my_string, '[xy][0-9]+', next_pos)) AS next_pos
    FROM matches
    WHERE REGEXP_SUBSTR(my_string, '[xy][0-9]+', next_pos) IS NOT NULL
)
SELECT row_id, matched_value
FROM matches
ORDER BY row_id, next_pos;

效果验证 #

针对你的示例字符串"this is a test string with x12345 and y1264 ..."，执行后会返回：

row_id | matched_value
-------|--------------
1      | x12345
1      | y1264

提取出的matched_value可直接用于与其他表的键关联。

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