/*建立測試環境
WINXP(SP3)+SQL SERVER2005(SP3)*/
if object_id('tb') is not null
drop table tb
go
create table tb
(id int identity(1,1),
title varchar(200),
detail varchar(1000),
constraint pk_id primary key(id) –在建立全文索引時需要使用
)
insert into tb
select '火箭即將簽下新秀射手',' 據悉,巴丁格與火箭隊的合約談判是於昨天完成的,巴丁格將得到與泰勒一樣的合約。先前媒體曝光泰勒的合約為期四年,總價值萬美元,其中前兩年為保障合約。巴丁格預計在接下來幾天內正式宣布簽約加入火箭。 '
union all
select '韋弗被曝已與希臘豪門簽約','據國際籃球網報道,前火箭隊球員範-韋弗已經與希臘豪門奧林匹亞科斯隊簽訂了合約。韋弗得到一份為期兩年,總價值萬美元的合約。 '
union all
select '馬刺豪擲千金為對抗湖人','馬刺隊在今夏休賽期補充了幾員大將,主教練格雷格-維奇日前在接受Yahoo!體育採訪時透露,馬刺隊不惜繳納奢侈稅構建豪華陣容就是為了對抗湖人隊,爭取拿下第五個總冠軍。 '
union all
select '華萊士未曾想過離開汽車城','此前本-華萊士已經同意重返底特律活塞,並且以老將底薪和活塞簽下一份年萬美元的合同,而據《每日先驅報》專欄作家米克-麥格勞透露,這位當年叱吒NBA賽場的內線防守悍將甚至從來沒有考慮過要離開活塞隊。 '
union all
select '米勒竟好橫刀奪愛追求人妻','對於那些沒看過雷吉·米勒在步行者創造“米勒時間”的“後”們,該怎麼介紹這位前NBA球星呢?難道從前天洛杉磯馬里布海灘上空那架飛機拉的橫幅說起?恐怕沒有哪位家長願意這麼做。 '
union all
select '姚明:沒把上海當投資項目樂得生意做了好人當了','“姚蜜”說:不缺廣告效應的姚明收購瀕臨絕境的上海東方籃球俱樂部,說明他是真的想為曾經的母隊做點什麼。 '
union all
select '火箭不敵奇才終結年紀錄','此役姚麥組合狀態糟糕,姚明投中得到分個籃板次蓋帽,麥迪投中拿下分個籃板次助攻,兩人聯手竟不如得到分個籃板次助攻次蓋帽的賈米森。 '
第一步:啟用資料庫的全文索引
sp_fulltext_database enable --啟用資料庫的全文索引
go
第二步:建立全文目錄
create fulltext catalog tb_fulltext
in path N'D:Program FilesMicrosoft SQL Server2005MSSQL.1MSSQLFTData'
with accent_sensitivity =on --區分重音
authorization dbo;--全文目錄的擁有者
第三步:建立全文索引
create fulltext index on tb
(title,detail)
key index pk_id --指定索引列,為了提高效能,最好使用聚集索引
on tb_fulltext
with change_tracking auto --在關聯的表中修改了資料時,自動更新全文索引。
第四步:查詢範例:
select * from tb
where contains((title,detail),'姚明')
查詢的語法:
1、 搜尋特定詞:contains(detail,'姚明')
2、 搜尋特定短語:用「「將短語包含在雙引號內,contains(detail,'姚明上海')
3、 從多個欄位搜尋字詞和片語:contains((title,detail),'姚明上海')
4、搜尋以指定文字開頭的字或片語:contains(detail,'“姚明*“')
如果文字和星號不包含在雙引號內,則全文搜尋會將星號看做是一個字元。如果搜尋的是短語,則該短語內的每個單字都被視為一個前綴。 contains(detail,'「姚明上海*「')則傳回第一個字以姚明開頭第二個字以上海開頭的結果
5. 搜尋特定字的變形:contains(detail,'formsof(inflectional,ride)')
將返回表中含有ride,rides,riding,ridden的行
6.搜尋與另一個字或片語臨近的字或片語:contains(detail,'姚明near 上海')
7.使用加權值的字或片語:contains(description,'isabout(performance weight(.8),comfortable weight(.4),smooth weight(.2))')
Weight為每個字或片語指定一個0.0~1.0之間的加權值
8.使用多個搜尋條件: contains(detail,'”姚明” or “上海”')
contains(detail,'”姚明” and “上海”')
contains(detail,'”姚明” and not “上海”')
9.在contains中還可以使用變數
10.搜尋同義詞 contains(title,'formsof(thesaurus,上)')
【測試】
在上例中,執行
select * from tb
where contains(title,'上海')
結果:
id title detail
6 姚明:沒把上海當投資項目樂得生意做了好人當了「姚蜜」說:不缺廣告效應的姚明收購瀕臨絕境的上海東方籃球俱樂部,說明他是真的想為曾經的母隊做點事情。
但是執行:
select * from tb
where contains(title,'上')
結果為空集合。
原因:在進行全文索引查詢的時候,「上」為簡體中文的干擾詞,即查詢的時候會忽略掉;另外建立全文索引的時候一般會以一個詞組作為一個索引項,而不是單字。
如果還要進行此查詢,想查出第一個查詢的結果,可以修改全文索引的同義詞庫。
幹擾詞與同義詞檔案存在的路徑:
D:Program FilesMicrosoft SQL Server2005MSSQL.1MSSQLFTData,每個檔名都應該很好辨別。
簡單介紹下修改同義詞庫的方法:
1、 用記事本開啟tschs.xml,這是簡體中文的同義詞庫
<XML ID="Microsoft Search Thesaurus">
<!-- Commented out
<thesaurus xmlns="x-schema:tsSchema.xml">
<diacritics_sensitive>0</diacritics_sensitive>
<expansion>
<sub>Internet Explorer</sub>
<sub>IE</sub>
<sub>IE5</sub>
</expansion>
<replacement>
<pat>NT5</pat>
<pat>W2K</pat>
<sub>Windows 2000</sub>
</replacement>
<expansion>
<sub>run</sub>
<sub>jog</sub>
</expansion>
</thesaurus>
-->
</XML>
解釋:
<replacement>
<pat>NT5</pat>
<pat>W2K</pat>
<sub>Windows 2000</sub>
</replacement>
為替代詞,即查詢W2K時,會自動替換為Windows 2000進行查詢
<expansion>
<sub>run</sub>
<sub>jog</sub>
</expansion>
為同義詞庫,也就是查詢run的時候也會查詢jog
在本例中加入:
<expansion>
<sub>上</sub>
<sub>上海</sub>
</expansion>
2、 去掉開始於結尾的註解行:
<!-- Commented out
-->
3.最終形成的結果為:
<XML ID="Microsoft Search Thesaurus">
<thesaurus xmlns="x-schema:tsSchema.xml">
<diacritics_sensitive>0</diacritics_sensitive>
<expansion>
<sub>Internet Explorer</sub>
<sub>IE</sub>
<sub>IE5</sub>
</expansion>
<replacement>
<pat>NT5</pat>
<pat>W2K</pat>
<sub>Windows 2000</sub>
</replacement>
<expansion>
<sub>run</sub>
<sub>jog</sub>
</expansion>
<expansion>
<sub>上</sub>
<sub>上海</sub>
</expansion>
</thesaurus>
</XML>
3、 執行查詢語句:
select * from tb
where contains(title,'formsof(thesaurus,上)')
即可看到查詢結果與contains(title,'上海')相同