sql-server - 针对多个搜索词的全文搜索多列

Question

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

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

现在，这似乎完全是一个坏主意，作为替代方案，我建议使用多个字段分别输入这些信息。尽管如此，我的观点是 必须从性能角度证明为什么这不起作用，并且最好在多个字段中输入要搜索的术语。

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

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

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 )。

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

Msg 7630, Level 15, State 3, Line 26

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

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

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

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

Answer 1

您可以创建 View WITH SCHEMABINDING与 id和连接列:

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上创建全文索引 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

之后，您需要编写一些函数来构建搜索条件。 F.e.

FL Diana Brooks Miami 90210

变成:

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

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

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