全文搜索多个搜索词的多个列

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.

推荐答案

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

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

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

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

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