如何用SQL提取wp_post表中含[xxx >> yyy]格式的指定内容

核心思路 #

1. 将post_content中的多行内容按换行符拆分，给每行分配行号
2. 过滤出包含[xxx >> yyy]格式的行，同时限定行号为3、5
3. 去除每行的<p>和</p>转义标签（<p>、</p>），清理多余空格

MySQL 8.0+ 实现代码 #

WITH RECURSIVE split_lines AS (
    -- 初始化：提取第一行并初始化行号
    SELECT 
        id,
        TRIM(SUBSTRING_INDEX(post_content, '\n', 1)) AS line,
        TRIM(SUBSTRING(post_content, LENGTH(SUBSTRING_INDEX(post_content, '\n', 1)) + 2)) AS remaining,
        1 AS line_num
    FROM wp_post
    WHERE post_content IS NOT NULL

    UNION ALL

    -- 递归拆分剩余行，递增行号
    SELECT 
        id,
        TRIM(SUBSTRING_INDEX(remaining, '\n', 1)) AS line,
        TRIM(SUBSTRING(remaining, LENGTH(SUBSTRING_INDEX(remaining, '\n', 1)) + 2)) AS remaining,
        line_num + 1 AS line_num
    FROM split_lines
    WHERE remaining != ''
)
SELECT 
    TRIM(REPLACE(REPLACE(line, '<p>', ''), '</p>', '')) AS cleaned_content
FROM split_lines
WHERE 
    -- 匹配[xxx >> yyy]格式，转义方括号避免正则语法冲突
    line REGEXP '\\[.*>>.*\\]'
    -- 限定目标行号
    AND line_num IN (3, 5);

PostgreSQL 实现代码 #

如果使用PostgreSQL，可借助STRING_TO_ARRAY和UNNEST简化拆分逻辑：

SELECT 
    TRIM(REPLACE(REPLACE(line, '<p>', ''), '</p>', '')) AS cleaned_content
FROM (
    SELECT 
        UNNEST(STRING_TO_ARRAY(post_content, '\n')) AS line,
        -- 按原内容的行顺序分配行号
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY 1) AS line_num
    FROM wp_post
    WHERE post_content IS NOT NULL
) AS split_lines
WHERE 
    line ~* '\\[.*>>.*\\]'
    AND line_num IN (3, 5);

关键细节说明 #

• 正则匹配：\\[.*>>.*\\]用来精准匹配[xxx >> yyy]格式，其中方括号需要转义，避免被正则识别为字符集
• 行号控制：通过递归或窗口函数生成的line_num，确保只提取原内容的第3、5行
• 标签清理：用双层REPLACE去除转义后的<p>标签，TRIM清理前后多余空格
• 批量处理：该逻辑支持处理大量post_content数据，无需逐行手动处理

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