在 SQL Server 2008 中跨多个表、列使用全文搜索

问题描述

我需要使用全文搜索从我的数据库中的两个表中搜索多个列.有问题的两个表具有全文索引的相关列.

我选择全文搜索的原因:1. 能够轻松搜索重音词 (cafè)2. 能够根据词的接近度等进行排名.3. 你是说XXX吗?"功能

这是一个虚拟表结构，用于说明挑战:

我需要搜索书名、书注和作者姓名.

我知道有两种方法可以做到这一点:

1. 使用全文索引视图:这本来是我的首选方法，但我不能这样做，因为要对视图进行全文索引，它需要是模式绑定的，没有任何外部连接，有一个唯一的索引.我需要获取数据的视图不满足这些约束(它包含我需要从中获取数据的许多其他连接表).

2. 在存储过程中使用连接:这种方法的问题是我需要按等级对结果进行排序.如果我要跨表进行多个连接，默认情况下 SQL Server 不会跨多个字段进行搜索.我可以在两个链接表上组合两个单独的 CONTAINS 查询，但我不知道从两个搜索查询中提取组合排名的方法.例如，如果我搜索Arthur"，则应考虑 Book 查询和 Author 查询的结果并相应地加权.

使用 FREETEXTTABLE，您只需要设计一些算法来计算每个连接表结果的合并排名.下面的示例将结果偏向于图书表中的命中.

SELECT b.Name, a.Name, bkt.[Rank] + akt.[Rank]/2 AS [Rank]从书 bINNER JOIN 作者 a ON b.AuthorID = a.AuthorIDINNER JOIN FREETEXTTABLE(Book, Name, @criteria) bkt ON b.ContentID = bkt.[Key]LEFT JOIN FREETEXTTABLE(Author, Name, @criteria) akt ON a.AuthorID = akt.[Key]ORDER BY [Rank] DESC

请注意，我简化了此示例的架构.

I need to search across multiple columns from two tables in my database using Full-Text Search. The two tables in question have the relevant columns full-text indexed.

The reason I'm opting for Full-text search: 1. To be able to search accented words easily (cafè) 2. To be able to rank according to word proximity, etc. 3. "Did you mean XXX?" functionality

Here is a dummy table structure, to illustrate the challenge:

Table Book
BookID
Name (Full-text indexed)
Notes (Full-text indexed)

Table Shelf
ShelfID
BookID

Table ShelfAuthor
AuthorID
ShelfID

Table Author
AuthorID
Name (Full-text indexed)

I need to search across Book Name, Book Notes and Author Name.

I know of two ways to accomplish this:

1. Using a Full-text Indexed View: This would have been my preferred method, but I can't do this because for a view to be full-text indexed, it needs to be schemabound, not have any outer joins, have a unique index. The view I will need to get my data does not satisfy these constraints (it contains many other joined tables I need to get data from).

2. Using joins in a stored procedure: The problem with this approach is that I need to have the results sorted by rank. If I am making multiple joins across the tables, SQL Server won't search across multiple fields by default. I can combine two individual CONTAINS queries on the two linked tables, but I don't know of a way to extract the combined rank from the two search queries. For example, if I search for 'Arthur', the results of both the Book query and the Author query should be taken into account and weighted accordingly.

Using FREETEXTTABLE, you just need to design some algorithm to calculate the merged rank on each joined table result. The example below skews the result towards hits from the book table.

SELECT b.Name, a.Name, bkt.[Rank] + akt.[Rank]/2 AS [Rank]
FROM Book b
INNER JOIN Author a ON b.AuthorID = a.AuthorID
INNER JOIN FREETEXTTABLE(Book, Name, @criteria) bkt ON b.ContentID = bkt.[Key] 
LEFT JOIN FREETEXTTABLE(Author, Name, @criteria) akt ON a.AuthorID = akt.[Key]
ORDER BY [Rank] DESC

Note that I simplified your schema for this example.

这篇关于在 SQL Server 2008 中跨多个表、列使用全文搜索的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！