MySQL如何查询表字段中包含指定子串的所有独立单词

实现方案（适用于MySQL 8.0及以上版本） #

直接用JSON_TABLE函数拆分单词后过滤即可，语句如下：

SELECT DISTINCT word AS title
FROM myTable,
     JSON_TABLE(
         -- 先处理多余空格，再转成JSON数组格式
         CONCAT('["', REPLACE(REGEXP_REPLACE(TRIM(title), '\\s+', ' '), ' ', '","'), '"]'),
         '$[*]' COLUMNS (word VARCHAR(255) PATH '$')
     ) AS words
WHERE word LIKE '%re%';

逻辑说明：

• 先通过REGEXP_REPLACE处理标题里的连续空格，避免拆分出空字符串
• 用字符串拼接把空格分隔的标题转为标准JSON数组格式
• 通过JSON_TABLE把JSON数组的每个元素转为独立行的单词数据
• 用LIKE筛选包含子串re的单词，加DISTINCT去重避免同一个单词重复返回
• 如果需要忽略大小写匹配，把WHERE条件改为WHERE LOWER(word) LIKE '%re%'即可

MySQL 5.x版本适配方案 #

如果使用的是不支持JSON_TABLE的低版本MySQL，可以提前生成一张包含连续数字的辅助表numbers（表内字段n的取值需要覆盖单条title的最大单词数量），写法如下：

SELECT DISTINCT SUBSTRING_INDEX(SUBSTRING_INDEX(title, ' ', n), ' ', -1) AS title
FROM myTable
JOIN numbers ON n <= LENGTH(title) - LENGTH(REPLACE(title, ' ', '')) + 1
WHERE SUBSTRING_INDEX(SUBSTRING_INDEX(title, ' ', n), ' ', -1) LIKE '%re%'

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