在MySQL 8.0.27中将长文本字段拆分为单行单词的实现方案

方案一：递归CTE拆分法 #

适合处理任意长度的LONGTEXT字段，无需额外依赖：

1. 先创建目标words表（如果还没建）：

CREATE TABLE IF NOT EXISTS words (
    word VARCHAR(255) PRIMARY KEY
);

2. 用递归CTE拆分description并插入去重后的单词：

WITH RECURSIVE split_words AS (
    -- 初始步骤：提取每个description的第一个单词和剩余文本
    SELECT
        TRIM(SUBSTRING_INDEX(description, ' ', 1)) AS word,
        TRIM(SUBSTRING(description, LENGTH(SUBSTRING_INDEX(description, ' ', 1)) + 1)) AS remaining
    FROM products
    WHERE description IS NOT NULL AND description != ''
    UNION ALL
    -- 递归循环拆分剩余文本
    SELECT
        TRIM(SUBSTRING_INDEX(remaining, ' ', 1)) AS word,
        TRIM(SUBSTRING(remaining, LENGTH(SUBSTRING_INDEX(remaining, ' ', 1)) + 1)) AS remaining
    FROM split_words
    WHERE remaining IS NOT NULL AND remaining != ''
)
-- 插入去重后的单词，避免重复记录
INSERT IGNORE INTO words (word)
SELECT word FROM split_words WHERE word != '';

方案二：JSON_TABLE函数法（MySQL 8.0+专属） #

借助JSON转换快速拆分字符串为行，代码更简洁：

1. 同样先创建words表：

CREATE TABLE IF NOT EXISTS words (
    word VARCHAR(255) PRIMARY KEY
);

2. 转换文本为JSON数组后展开插入：

INSERT IGNORE INTO words (word)
SELECT DISTINCT j.word
FROM products,
     JSON_TABLE(
         -- 把空格分隔的文本转为JSON数组格式
         CONCAT('["', REPLACE(TRIM(description), ' ', '","'), '"]'),
         '$[*]' COLUMNS (word VARCHAR(255) PATH '$')
     ) j
WHERE description IS NOT NULL AND description != '';

注意事项 #

• 两种方案都处理了空值和空字符串，不会生成无效的空单词
• 通过INSERT IGNORE或DISTINCT实现去重，像fruit这类重复单词只会保留一条
• 完全支持LONGTEXT类型的大文本，没有长度限制

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