SQL Server - 多列子串匹配

Question

我的一位客户迷上了多列子字符串匹配。

我明白 Contains和 FreeText搜索词(至少在包含的情况下，词前缀)。但是，根据我对 this 的理解MSDN 书，这些及其变体都不能搜索子字符串。

我用过 LIKE相当广泛(Select * from A where A.B Like '%substr%')

示例表 A:

ID | Col1     | Col2     | Col3     |
-------------------------------------
1  | oklahoma | colorado | Utah     |
2  | arkansas | colorado | oklahoma |
3  | florida  | michigan | florida  |
-------------------------------------

以下代码将为我们提供第 1 行和第 2 行:

 select * from A where Col1 like '%klah%' or Col2 like '%klah%' or Col3 like '%klah%'

这相当丑陋，可能很慢，我只是不太喜欢它。可能是因为我正在处理的实现有 10 多个需要搜索的列。

随着代码可读性的发展，以下内容可能会略有改进，但就性能而言，我们仍然处于同一个球场。

 select * from A where (Col1 + ' ' + Col2 + ' ' + Col3) like '%klah%'

我曾想过简单地添加插入、更新和删除触发器，这些触发器只是将上述列的连接版本添加到一个单独的表中，该表会隐藏该表。

示例 Shadow_Table:

ID | searchtext                 |
---------------------------------
1  | oklahoma colorado Utah     |
2  | arkansas colorado oklahoma |
3  | florida michigan florida   |
---------------------------------

这将允许我们执行以下查询来搜索 '%klah%'

select * from Shadow_Table where searchtext like '%klah%'

我真的不喜欢必须记住这个影子表存在并且我应该在执行多列子字符串匹配时使用它，但它可能会以写入和存储空间为代价产生非常快的读取。

我的直觉告诉我，SQL Server 2008 中内置了一个现有的解决方案。但是，除了关于该主题的研究论文之外，我似乎找不到任何其他内容。

任何帮助，将不胜感激。

Answer 1

从您的描述来看，您似乎正在寻找一种改进精确搜索的方法。 LIKE是在您尝试查找与您的字符串完全匹配的字符串时使用的正确工具。如果您担心性能，那么您应该考虑索引，甚至是您所描述的自定义索引。

也许考虑使用持久计算列而不是影子表。插入/更新的开销应该小于使用触发器，并且查询时间可能相当。

关于全文检索

全文搜索被设计为自然语言搜索。

从最终用户的角度考虑。如果我要搜索“Oklahoma”，我可能会以“okla”或“ok”或“oklahoma”开头。我不会搜索“homa”。这就是我们人类的思维方式。因此，“自然”语言搜索。

自然语言搜索使用词根和相似词来增加结果总数。但是，如果您希望所有结果都特别匹配您的搜索词，这并不是最佳选择:例如自由文本会将“我开车去上课”与“驾驶和类(class)”匹配，即使这两个词都没有特别出现。