针对多个搜索词的全文搜索多列 [英] Fulltext search multiple columns for multiple search terms

问题描述

我有一个客户要求有一个搜索字段，他想在其中输入任何文本并从客户信息表中搜索包含客户信息的多个全文索引列中该文本字段中的每个单词.

I have a requirement from a client to have a search-field where he wants to input any text and search for every word in that text field in multiple full-text indexed columns which contain customer information, from a customer information table.

因此，例如，如果他输入 FL Diana Brooks Miami 90210，他需要所有这些词(FL、Diana、Brooks、Miami、90210) 到每个都被搜索到 State、FirstName、LastName、City 和 Zip 列中.

So, for example, if he inputs FL Diana Brooks Miami 90210, he wants all of these terms (FL, Diana, Brooks, Miami, 90210) to each be searched into the State, FirstName, LastName, City and Zip columns.

现在，这似乎完全是一个坏主意，作为替代方案，我建议使用多个字段分别输入这些信息.尽管如此，我的重点是必须证明为什么这行不通，从性能的角度来看，最好有多个字段来输入您的术语想搜索.

Now, this seems totally a bad idea to begin with and as an alternative I suggested using multiple fields where to input this information separately. Nonetheless, the point I am at is having to make a proof of concept as to why this won't work, from a performance perspective, and that it's better to have multiple fields where you input the term you want to search for.

因此，对于我的查询，我正在尝试编写一个全文查询来执行客户要求的操作，以获得性能基准.

So, getting to my query, I'm trying to write a Full-Text query to do what the client has asked for in order to get a benchmark for performance.

到目前为止我所拥有的似乎不起作用，所以我想我在问是否有可能做到这一点?

What I have so far doesn't seem to work, so I guess I am asking if it's even possible to do this?

declare 
    @zip varchar(10)            = 90210
    , @lastName varchar(50)     = 'Brooks'
    , @firstName varchar(50)    = 'Diana'
    , @city varchar(50)         = 'Miami'
    , @state char(2)            = 'FL'
    , @searchTerm varchar(250)  = ''
    , @s varchar(1) = ' '

set @searchTerm = @state + ' ' + @firstName + ' ' + @lastName + ' ' + @city

select * 
from freetexttable(contacts, (zip, lastName, FirstName, city, state), @searchTerm) ftTbl
inner join contacts c on ftTbl.[key] = c.ContactID

我上面的查询似乎有效，但限制性不够，无法仅找到我正在查找的单个记录，并且返回了更多(我猜这是因为我正在使用FREETEXTTABLE).

The query I have above seems to work, but is not restrictive enough in order to find only the single record I'm looking for and is returning a whole lot more (which I'm guessing that it's because I'm using FREETEXTTABLE).

我也尝试用 CONTAINSTABLE 替换它，但我收到一条错误消息:

I've also tried replacing it with CONTAINSTABLE, but I get an error saying:

消息 7630，级别 15，状态 3，第 26 行

Msg 7630, Level 15, State 3, Line 26

全文搜索条件FL Diana Brooks Miami"中Diana"附近的语法错误.

Syntax error near 'Diana' in the full-text search condition 'FL Diana Brooks Miami'.

通过使用常规索引，我已经能够解决这个问题，但我很好奇是否可以使用全文来做同样的事情.

With using regular indexes I have been able to solve this, but I'm curious if it's even possible to do the same thing with Full-Text.

使用常规索引，我有一个带有自适应 WHERE 子句的查询，如下所示:

Using regular indexes I have a query with a adaptable WHERE clause, like below:

WHERE C.FirstName like coalesce(@FirstName + '%' , C.FirstName)
    AND C.LastName like coalesce(@LastName + '%' , C.LastName)
    etc.

推荐答案

您可以创建一个带有 id 和连接列的 WITH SCHEMABINDING 视图:

You can create a view WITH SCHEMABINDING with id and concatinated columns:

CREATE VIEW dbo.SearchView WITH SCHEMABINDING 
AS
SELECT  id,
        [State]+' ',
        [FirstName]+' ',
        [LastName]+' ',
        [City]+' ',
        [Zip] as search_string
FROM YourTable

创建索引

CREATE UNIQUE CLUSTERED INDEX UCI_SearchView ON dbo.SearchView (id ASC)

然后在 search_string 字段上创建全文索引.

Then create full-text index on search_string field.

USE YourDB
GO

--Enable Full-text search on the DB
IF (SELECT DATABASEPROPERTY(DB_NAME(), N'IsFullTextEnabled')) <> 1
EXEC sp_fulltext_database N'enable'
GO

--Create a full-text catalog
IF NOT EXISTS (SELECT * FROM dbo.sysfulltextcatalogs WHERE [name] = N'CatalogName')
EXEC sp_fulltext_catalog N'CatalogName', N'create'
GO

EXEC sp_fulltext_table N'dbo.SearchView', N'create', N'CatalogName', N'IndexName'
GO

--Add a column to catalog
EXEC sp_fulltext_column N'dbo.SearchView', N'search_string', N'add', 0 /* neutral */
GO

--Activate full-text for table/view
EXEC sp_fulltext_table N'dbo.SearchView', N'activate'
GO

--Full-text index update
exec sp_fulltext_catalog 'CatalogName', 'start_full'
GO

之后，您需要编写一些函数来构造搜索条件.费

After that you need to write some function to construct a search condition. F.e.

FL Diana Brooks Miami 90210

变成:

"FL*" AND "Diana*" AND "Brooks*" AND "Miami*" AND "90210*"

并在 FREETEXT 或 CONTAINS 搜索中使用它:

And use it in FREETEXT or CONTAINS searches:

DECLARE @search nvarchar(4000) = '"FL*" AND "Diana*" AND "Brooks*" AND "Miami*" AND "90210*"'

SELECT  sv.*
FROM dbo.SearchView sv
INNER JOIN CONTAINSTABLE (dbo.SearchView, search_string, @search) as c 
    ON c.[KEY] = sv.id

这篇关于针对多个搜索词的全文搜索多列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！