SQL如何整词匹配另一关键词表的内容查询符合条件的记录

表结构与需求说明 #

涉及两张业务表：

• 主表（main_table）：存储物品数据，包含ID（物品ID）、Item（物品名称）两个字段，样例数据如下：
  • ID=11，Item='colored paper'
  • ID=12，Item='red shirt'
  • ID=13，Item='antiblack shoe'
  • ID=14，Item='yellow desk'
• 关键词表（keywords_table）：存储待匹配的颜色关键词，仅含keyword字段，存储值为red、yellow、black、green
  需求为匹配物品名称中独立包含某一关键词的记录，排除关键词作为其他单词子串的误匹配场景（如colored中包含的red、antiblack中包含的black均不算有效匹配），预期返回ID=12、ID=14两条记录。

核心实现思路 #

不要直接用LIKE '%关键词%'做全模糊匹配，这种写法无法区分子串和整词。可以通过给文本内容增加单词边界标记的方式做匹配，不需要额外拆分字符串，写法简单且执行效率更高。

可直接复用的SQL代码 #

通用兼容写法（支持MySQL、PostgreSQL、SQL Server等绝大多数数据库） #

通过给匹配字段和关键词前后拼接空格的方式构造单词边界，保证匹配到的关键词是独立存在的整词：

SELECT DISTINCT m.ID, m.Item
FROM main_table m
INNER JOIN keywords_table k
  ON CONCAT(' ', LOWER(m.Item), ' ') LIKE CONCAT('% ', LOWER(k.keyword), ' %')

说明：LOWER()函数用于实现大小写不敏感匹配，如果业务数据中关键词和物品名称大小写完全统一，可以移除该函数；DISTINCT用于避免同一物品命中多个关键词时返回重复记录。

正则简化写法（支持正则匹配的数据库适用） #

如果所用数据库支持正则匹配，可以直接用单词边界符实现，写法更简洁：

• MySQL 5.5+/PostgreSQL 版本：

SELECT DISTINCT m.ID, m.Item
FROM main_table m
INNER JOIN keywords_table k
  ON m.Item REGEXP CONCAT('[[:<:]]', k.keyword, '[[:>:]]')

代码中[[:<:]]和[[:>:]]是数据库内置的单词边界匹配符，会自动识别独立存在的整词，自动过滤子串误匹配场景。

匹配效果验证 #

用上述语句运行样例数据的返回结果：

• ID=11（colored paper）：red是colored的内部子串，无单词边界，不命中
• ID=12（red shirt）：red是句首独立单词，命中
• ID=13（antiblack shoe）：black是antiblack的内部子串，无单词边界，不命中
• ID=14（yellow desk）：yellow是句首独立单词，命中
  完全符合预期输出要求。

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