Hive QL如何基于关键词表实现通配符搜索的优雅查询方案

以下是替代硬编码多OR LIKE语句的多种实现方案，兼容不同数据库场景：

全数据库通用方案（EXISTS子查询） #

该写法支持MySQL、PostgreSQL、SQL Server、Oracle等所有主流关系型数据库，是兼容性最高的优雅实现：

SELECT * 
FROM 业务表 t
WHERE EXISTS (
    SELECT 1 
    FROM 关键词表 k
    WHERE t.Message LIKE CONCAT('%', k.Words, '%')
)

• 逻辑为遍历业务表每一行，检查是否存在任意一个关键词能匹配Message字段，匹配成功即返回该行
• 关键词表新增、删除、修改关键词后不需要调整SQL语句，完全解耦

JOIN实现（支持返回匹配关键词） #

如果需要同时返回业务记录匹配到的关键词，可以用JOIN写法，注意加DISTINCT避免一条业务记录匹配多个关键词时重复返回：

SELECT DISTINCT t.*
FROM 业务表 t
INNER JOIN 关键词表 k 
  ON t.Message LIKE CONCAT('%', k.Words, '%')

各数据库专属优化写法 #

• MySQL：可以用REGEXP正则匹配减少表关联，先把所有关键词拼接成|分隔的正则串即可：

SELECT * 
FROM 业务表
WHERE Message REGEXP (
    SELECT GROUP_CONCAT(Words SEPARATOR '|') 
    FROM 关键词表
)

注意：关键词如果包含正则特殊字符（比如.、*、+等）需要提前转义，避免匹配逻辑出错。

• PostgreSQL：可以用LIKE ANY语法更简洁：

SELECT *
FROM 业务表
WHERE Message LIKE ANY (
    SELECT ARRAY_AGG('%' || Words || '%') 
    FROM 关键词表
)

性能注意事项 #

• 带前缀%的LIKE查询无法命中普通B树索引，数据量超过10万行时查询速度会明显下降
• 大数据量场景建议开启数据库的全文索引功能，用全文检索语法替代通配符匹配，性能可以提升几十到上百倍

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