如何在MySQL中循环提取posts表文本字段内@开头的单词？

嘿，这个需求在处理社交类数据时特别常见！下面我会针对几种主流数据库，给出具体的实现方案，你可以根据自己使用的数据库来选择：

MySQL（8.0及以上版本） #

MySQL 8.0开始支持正则表达式函数，我们可以用递归CTE（公共表表达式）来循环提取所有匹配的@开头单词：

WITH RECURSIVE extract_mentions AS (
    SELECT 
        id,
        user,
        posts,
        REGEXP_SUBSTR(posts, '@\\w+') AS mention,
        REGEXP_REPLACE(posts, '@\\w+', '', 1, 1) AS remaining_text
    FROM posts
    WHERE REGEXP_LIKE(posts, '@\\w+')
    
    UNION ALL
    
    SELECT 
        id,
        user,
        posts,
        REGEXP_SUBSTR(remaining_text, '@\\w+') AS mention,
        REGEXP_REPLACE(remaining_text, '@\\w+', '', 1, 1) AS remaining_text
    FROM extract_mentions
    WHERE REGEXP_LIKE(remaining_text, '@\\w+')
)
SELECT id, user, mention
FROM extract_mentions
ORDER BY id, mention;

简单解释下：

• REGEXP_SUBSTR(posts, '@\\w+') 用来匹配第一个以@开头的单词（\\w+表示匹配字母、数字、下划线）
• 递归CTE会不断从剩余文本中提取下一个匹配项，直到没有更多@开头的单词为止
• 最终结果会把每个匹配的mention单独成行，关联对应的id和user

PostgreSQL #

PostgreSQL的regexp_matches函数可以直接返回所有匹配的数组，配合unnest就能把数组转成多行结果，实现起来更简洁：

SELECT 
    id,
    user,
    unnest(regexp_matches(posts, '@\\w+', 'g')) AS mention
FROM posts
WHERE posts ~ '@\\w+';

解释：

• regexp_matches的第三个参数'g'表示全局匹配，会返回所有符合条件的结果组成的数组
• unnest把数组拆分成单独的行，这样每个mention就对应一条独立的记录

SQL Server（2017及以上版本） #

如果你的SQL Server版本支持正则函数（2016+），可以用类似MySQL的递归CTE方案：

WITH RECURSIVE extract_mentions AS (
    SELECT 
        id,
        user,
        posts,
        REGEXP_SUBSTR(posts, '@\\w+') AS mention,
        REGEXP_REPLACE(posts, '@\\w+', '', 1, 1) AS remaining_text
    FROM posts
    WHERE REGEXP_LIKE(posts, '@\\w+')
    
    UNION ALL
    
    SELECT 
        id,
        user,
        posts,
        REGEXP_SUBSTR(remaining_text, '@\\w+') AS mention,
        REGEXP_REPLACE(remaining_text, '@\\w+', '', 1, 1) AS remaining_text
    FROM extract_mentions
    WHERE REGEXP_LIKE(remaining_text, '@\\w+')
)
SELECT id, user, mention
FROM extract_mentions
ORDER BY id, mention;

要是版本不支持正则函数，也可以用PATINDEX和SUBSTRING组合实现递归提取，效果是一样的。

这样不管用哪种数据库，都能把posts字段里所有以@开头的单词准确提取出来啦！

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