Dotjum@點部落

筆記自己的想法與記錄

2009-08-01

[SQL]使用SQL2005全文檢索功能

紀錄簡易設定SQL 2005 全文檢索及SQL 2005 全文檢索查詢語法、同義字詞庫設定及查詢。


紀錄簡易設定SQL 2005 全文檢索及SQL 2005 全文檢索查詢語法、同義字詞庫設定及查詢。

一.設定全文檢索功能

1.開啟使用全文檢索

image 


2.對要使用全文檢索的資料表建立全文檢索索引，
image 

 

3.選取要全文檢索的欄位
image 

4.依實際狀況設定全文檢索累加增資料的排程時間（有點類似定時爬文）

 

二.程式查詢TSQL運用
設定全文檢索並以建立索引後，就可以透過條件 WHERE CONTAINS(欄位,搜尋的字)的方式，
來做全文檢索的查詢方式，下面範例程式碼， 依序介紹平常使用Like及全文檢索下CONTAINS用法。

 


DECLARE @Keyword varchar(100)
DECLARE @Keyword2 varchar(100)
SET @Keyword = '無線'
SET @Keyword2 = '機'
-- 平常使用Like方式
SELECT * FROM dbo.PostContent
WHERE Title Like '%'+@Keyword+'%' AND  Title Like '%'+@Keyword2+'%'   

-- 1.單一欄位查詢
SELECT * FROM dbo.PostContent
WHERE CONTAINS(Title,@Keyword )
-- 2.單一欄位下OR查詢 
SELECT * FROM dbo.PostContent
WHERE CONTAINS(Title,'"無線" OR "機"')
-- 沒辦法使用變數方式
--WHERE CONTAINS(Title,@Keyword OR @Keyword1)
-- 3.單一欄位下AND查詢 
SELECT * FROM dbo.PostContent
WHERE CONTAINS(Title,'"無線" AND "機"')
-- 4.多欄位查詢
SELECT * FROM dbo.PostContent
WHERE CONTAINS((Title,Supplier),@Keyword )
-- 5.全部欄位全文檢索欄位
SELECT * FROM dbo.PostContent
WHERE CONTAINS(*,@Keyword )

 

三.定義同義字(關連詞)
何謂同義字查詢：(引用SQL同義字組態 說明）
在 SQL Server 中，全文檢索查詢可以透過使用同義字 (Thesaurus) 搜尋使用者指定之詞彙的同義字 (Synonym)。
SQL Server「同義字」(Thesaurus) 會針對特定語言定義一組同義字 (Synonym)。
系統管理員可以定義兩種同義字形式：展開集和取代集。
透過開發符合全文檢索資料的同義字，您可以有效地擴大針對該資料進行全文檢索查詢的範圍。
同義字比對只會針對指定 FORMSOF THESAURUS 子句的 CONTAINS 和 CONTAINSTABLE 查詢以及 FREETEXT 和 FREETEXTABLE 查詢進行。

要使用同義自查詢，依序要完成以下三個步驟

1.同義字(關連詞)設定檔位置
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\FTData
資料夾下方有各語系定義的關連詞設定檔。

image

2.編輯 tsCHT.xml

詳細完整說明可以參考SQL同義字組態 這邊就直接介紹使用，
預設tsCHT.xml 的內容如下
image
主要有兩個重要的XML元素<expansion>及<replacement>可以延伸增加。
<expansion> 展開集：
在<expansion> 下的<sub>是具有延伸互通性，
例如當在 tsCHT.xml
<expansion>
            <sub>神腦</sub>
            <sub>電腦</sub>
</expansion>

當查詢「神腦」 的時候，除了會傳回資料符合「神腦」的資料外，
也會傳回資料符合「電腦」的資料。
在同一個<expansion></expansion>不限定範例中只有兩個<sub>。

<replacement>取代集：
在<replacement>具有取代字換成指定的查詢，
例如當在 tsCHT.xml
<replacement>
            <pat>NB</pat>
            <pat>NoteBook</pat>
            <sub>筆記型電腦</sub>
            <sub>電腦</sub>
</replacement>

當查詢指令下的查詢資料是「 NB」或「NoteBook」 ，向資料庫查詢時，
就會被至換為「筆記型電腦」「電腦」查詢，傳回符合「筆記型電腦」「電腦」的資料。

3.查詢命令
透過 'FORMSOF(THESAURUS,"查詢的字")' 能就能查詢同義字相關連的資料。

 


--單一欄位透過同義字查詢 'FORMSOF(THESAURUS,"查詢的字")'
SELECT * FROM dbo.PostContent
WHERE CONTAINS(Supplier,'FORMSOF(THESAURUS,"NB")')
--多欄位透過同義字查詢  'FORMSOF(THESAURUS,"查詢的字")'
SELECT * FROM dbo.PostContent
WHERE CONTAINS((Title,Supplier),'FORMSOF(THESAURUS,"NB")')

 

 

總結：
在設定完要使用全文檢索的資料表後，在依實際狀況設定建立索引的排程頻率及同義字字庫，
最後透過全文檢索支援的語法就能快速的使用全文檢索功能。
而使用全文檢索方式搜尋，就能透過簡單的語法，做到同時查詢很多欄位及同義詞查詢，不需要自己在串許多like的語法方式，效能方面，這個範例中並無實際比較，有興趣可以參考 DB World 資料庫專家電子雜誌 SQL Server 2005 全文檢索新增的功能(中) 文章編號：S070306102   ，有提到使用全文檢索效能的比較。
而中文斷詞問題，此篇範例中並沒實做，有興趣的可以參考 胡百敬 老師的這篇  SQL Server 2005/2008 斷字詞 DLL 。
至於SQL 2005 Express能否使用全文檢索，一開始先看到SQL Server 2005 功能比較 全文檢索功能欄位沒有支援，
但參考其他相關資料，有提到可以安裝 SQL Server 2005 Express Edition With Advanced Services SP2 ，
就有支援全文檢索功能，這部分目前還沒測試 SQL 2005 Express 支援全文檢索功能。

參考資源：
SQL同義字組態
胡百敬老師 DB World 資料庫專家電子雜誌 
SQL Server 2005 Express 安裝心得

 

 

系列文章