无关联字段的两张大表如何实现关键词列与文本列的匹配

你认为两张表无共同字段无法关联是错误的，文本匹配条件本身可以作为JOIN的关联依据，不需要写4000个CASE分支。

方案1：数据库原生SQL方案（推荐优先使用） #

基础写法 #

直接用关联查询替代UPDATE+CASE逻辑，直接得到你要的结果，不需要提前给Raw_Text加冗余字段：

SELECT kt.Keyword, rt.Block_of_Text
FROM Keyword_Table kt
INNER JOIN Raw_Text rt 
ON rt.Block_of_Text LIKE CONCAT('%', kt.Keyword, '%');

性能优化（必做，否则和你原来的CASE写法效率一样低） #

通配符开头的LIKE查询无法走普通B树索引，必须给Raw_Text.Block_of_Text列建全文索引，再用数据库自带的全文匹配函数替换LIKE：

• SQL Server环境：

SELECT kt.Keyword, rt.Block_of_Text
FROM Keyword_Table kt
INNER JOIN Raw_Text rt 
ON CONTAINS(rt.Block_of_Text, kt.Keyword);

• MySQL环境：

SELECT kt.Keyword, rt.Block_of_Text
FROM Keyword_Table kt
INNER JOIN Raw_Text rt 
ON MATCH(rt.Block_of_Text) AGAINST(kt.Keyword IN BOOLEAN MODE);

• PostgreSQL环境：

SELECT kt.Keyword, rt.Block_of_Text
FROM Keyword_Table kt
INNER JOIN Raw_Text rt 
ON to_tsvector('simple', rt.Block_of_Text) @@ plainto_tsquery('simple', kt.Keyword);

全文索引优化后，匹配速度会比普通LIKE全表扫描提升10~100倍，完全可以支撑400万级文本表的匹配需求。

方案2：程序批量处理方案（适合数据库不支持全文索引的场景） #

如果你的数据库没有全文索引能力，可以用代码逻辑处理，步骤如下：

• 第一步：一次性读取Keyword_Table全部4000条关键词，存入内存数组/集合（4000条数据内存占用可以忽略）
• 第二步：分批读取Raw_Text表数据，单次读取1000~10000条，避免内存溢出
• 第三步：对每一条Block_of_Text，遍历关键词列表做匹配，匹配成功就把<关键词, 文本>对存入结果表
• 可优化点：开启多线程并行处理不同批次的文本数据，进一步提升速度

特殊场景说明 #

如果存在一条文本匹配多个关键词的情况，需要先明确你的业务规则：

• 如果需要输出所有匹配组合，上面的方案直接用即可
• 如果只需要保留第一个匹配到的关键词，可以在SQL中用ROW_NUMBER()窗口函数按文本分组取第一条，程序处理时匹配到第一个关键词就终止遍历即可

疑问解答 #

缺少可关联字段不是核心问题，文本匹配条件本身就可以作为关联依据。大表文本模糊匹配本身确实有固有计算成本，但只要用对索引和处理方案，完全可以在可接受的时间内跑完。

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