sql-server - 我的全文搜索查询出了什么问题？

Question

我在使用全文 CONTAINS 运算符时遇到一些问题。这是一个快速脚本来展示我正在做的事情。请注意，WAITFOR 行只是给全文索引一点时间来完成填充。

create table test1 ( id int constraint pk primary key, string nvarchar(100) not null );
insert into test1 values (1, 'dog')
insert into test1 values (2, 'dogbreed')
insert into test1 values (3, 'dogbreedinfo')
insert into test1 values (4, 'dogs')
insert into test1 values (5, 'breeds')
insert into test1 values (6, 'breed')
insert into test1 values (7, 'breeddogs')

go
create fulltext catalog cat1
create fulltext index on test1 (string) key index pk on cat1
waitfor delay '00:00:03' 
go
select * from test1 where contains (string, '"*dog*"')

go
drop table test1
drop fulltext catalog cat1

返回的结果集为:

1   dog
2   dogbreed
3   dogbreedinfo
4   dogs

为什么记录#7“breeddogs”没有返回？

编辑

还有其他方法可以搜索其他字符串中包含的字符串吗？比 LIKE '%searchword%' 更快的方法？

Answer 1

只是因为 MS 全文搜索不支持后缀搜索 - 仅忽略前缀，即“*dog *”前面的“*”。 Books Online中有明确规定顺便说一句。

CONTAINS can search for:

• A word or phrase.
• The prefix of a word or phrase.
• A word near another word.
• A word inflectionally generated from another (for example, the word drive is the inflectional stem of drives, drove, driving, and driven).
• A word that is a synonym of another word using a thesaurus (for example, the word metal can have synonyms such as aluminum and steel).

前缀术语的定义如下:

< prefix term > ::= { "word *" | "phrase *" }

因此，不幸的是:无法在全文搜索中发出 LIKE 搜索。