20 SQL Server全文檢索搜尋 20-1 SQL Server全文檢索搜尋 20-2 全文檢索目錄的建立 20-3 建立全文檢索索引

Presentation on theme: "20 SQL Server全文檢索搜尋 20-1 SQL Server全文檢索搜尋 20-2 全文檢索目錄的建立 20-3 建立全文檢索索引"— Presentation transcript:

1

2 20 SQL Server全文檢索搜尋 20-1 SQL Server全文檢索搜尋 20-2 全文檢索目錄的建立 20-3 建立全文檢索索引
20-4 修改與刪除全文檢索目錄與索引 20-5 母體擴展的使用 20-6 使用全文檢索搜尋

3 20-1 SQL Server全文檢索搜尋 20-1-1 全文檢索搜尋的基礎 20-1-2 下載與安裝SQL Server範例資料庫

4 全文檢索搜尋的基礎-說明 全文檢索搜尋（Full-text Search）可以使用關鍵字尋找SQL Server資料庫中的文字內容資料，一種以特定語言規則為基礎（指分析過濾出文字資料中單字或片語的規則）的單字或片語搜尋。例如：Yahoo!或Google搜尋引擎的搜尋，就屬於一種全文檢索搜尋，可以執行特定單字或單字組合的快速搜尋。 全文檢索搜尋特別適用在各種商務案例，可以用來查詢大量非結構化文字資料。

5 20-1-1 全文檢索搜尋的基礎- 全文檢索目錄和索引
全文檢索目錄（Full-text Catalogs）：在全文檢索目錄包含0至多個全文檢索索引，這些索引資料從2008版開始是儲存在SQL Server資料庫。全文檢索目錄可以方便管理執行全文檢索搜尋所需的眾多全文檢索索引。 全文檢索索引（Full-text Indexes）：我們可以針對資料庫的資料表建立全文檢索索引，其內容是儲存單字或片語在指定記錄的位置資訊。簡單的說，SQL Server就是使用這些索引資訊來執行全文檢索搜尋。

6 全文檢索搜尋的基礎- 架構說明 SQL Server從7.0版開始支援全文檢索搜尋，2005版的全文檢索搜尋和SQL Server資料庫引擎是完全兩個不同的服務，換句話說，舊版全文檢索搜尋架構中，SQL Server資料庫引擎不負責處理全文檢索搜尋，它是由全文檢索搜尋引擎來執行搜尋，然後將結果傳送給資料庫引擎。 目前的「全文檢索搜尋引擎」（Full-text Query Engine）已經整合至SQL Server資料庫引擎，如此可以大幅減少服務之間的資料交換，使用更有效率的最佳化方式來執行全文檢索搜尋。

7 全文檢索搜尋的基礎-架構圖例

8 20-1-2 下載與安裝SQL Server範例資料庫
SQL Server安裝程式並不包含AdventureWorks範例資料庫，微軟已經將這些範例資料庫放在GitHub，使用者如果需要，可以自行從GitHub下載和安裝範例資料庫。 本章內容是使用SQL Server的AdventureWorks範例資料庫為例，我們需要先下載和安裝SQL Server範例資料庫。請啟動瀏覽器進入GitHub來下載範例資料庫，如下所示：

9 20-1-3 啟用SQL Server的全文檢索搜尋-SQL Server組態管理員
SQL Server需要在SQL Server組態管理員執行「篩選背景程式啟動器服務」（SQL Full-text Filter Daemon Launcher），才能啟動篩選背景程式主機來啟用SQL Server的全文檢索搜尋，如下圖所示：

10 20-1-3 啟用SQL Server的全文檢索搜尋-系統預存程序(語法)
當啟用SQL Server全文檢索搜尋後，SQL Server資料庫預設啟用全文檢索搜尋，如果發現資料庫沒有啟用用全文檢索搜尋，例如：附加舊版資料庫時，我們可以使用系統預存程序來啟用資料庫的全文檢索搜尋，其基本語法如下所示： EXEC sp_fulltext_database {'enable' | 'disable'} 上述系統預存程序的參數為enable，表示啟用全文檢索搜尋；disable是取消全文檢索搜尋。

11 20-1-3 啟用SQL Server的全文檢索搜尋-系統預存程序(範例)
SQL指令碼檔：Ch20_1_3.sql 使用系統預存程序來啟用【教務系統】資料庫的全文檢索搜尋，如下所示： USE 教務系統 GO EXEC sp_fulltext_database 'enable'

12 20-2 全文檢索目錄的建立 20-2-1 使用Management Studio建立全文檢索目錄
使用T-SQL指令建立全文檢索目錄

13 20-2-1 使用Management Studio 建立全文檢索目錄
在「物件總管」視窗展開【資料庫】下【教務系統】資料庫的【儲存體】項目，可以看到【全文檢索目錄】項目。

14 20-2-2 使用T-SQL指令建立全文檢索目錄-語法
T-SQL語言是使用CREATE FULLTEXT CATALOG指令來建立全文檢索目錄，其基本語法如下所示： CREATE FULLTEXT CATALOG 全文檢索目錄名稱 [ AUHTORIZATION 擁有者名稱] [ AS DEFAULT ] [ WITH ACCENT_SENSITIVITY = { ON | OFF }]

15 20-2-2 使用T-SQL指令建立全文檢索目錄-範例
SQL指令碼檔：Ch20_2_2.sql 請在【AdventureWorks】資料庫建立名為【產品文件_全文檢索目錄】的全文檢索目錄，並且指定它為預設目錄，如下所示： USE AdventureWorks GO CREATE FULLTEXT CATALOG 產品文件_全文檢索目錄 AS DEFAULT

16 20-3 建立全文檢索索引 20-3-1 使用Management Studio建立全文檢索索引
使用T-SQL指令建立全文檢索索引

17 20-3 建立全文檢索索引 在資料庫建立全文檢索目錄後，就可以建立指定資料表的全文檢索索引，我們可以將資料庫中的一至多個資料表建立全文檢索索引。在資料表建立全文檢索索引需要滿足一些條件，如下所示： 資料表需要擁有主索引鍵，或唯一值索引鍵，不過，索引鍵不可以是欄位組合的複合鍵。 每一個資料表只能建立一個全文檢索索引，但是可以包含多個資料表欄位。 建立全文檢索索引的欄位型別只能是：char、varchar、nchar、nvarchar、text、ntext、image、xml和varbinary。

18 20-3-1 使用Management Studio 建立全文檢索索引
在Management Studio提供SQL Server全文檢索索引精靈來幫助我們建立指定資料表的全文檢索索引，如下圖所示：

19 20-3-2 使用T-SQL指令建立全文檢索索引-語法
T-SQL語言是使用CREATE FULLTEXT INDEX指令來建立全文檢索索引，其基本語法如下所示： CREATE FULLTEXT INDEX ON 資料表名稱 [ (欄位名稱 [ TYPE COLUMN 型態的欄位名稱] [ LANGUAGE 語言名稱] [, …n ])] KEY INDEX 索引名稱 [ ON 全文檢索目錄名稱 ] [ WITH { CHANGE_TRACKING {MANUAL | AUTO | OFF [, NO POPULATION]}} ]

20 20-3-2 使用T-SQL指令建立全文檢索索引-範例
SQL指令碼檔：Ch20_3_2.sql 在【AdventureWorks】資料庫的【Product.Document】資料表儲存的是產品文件，我們準備在【產品文件_全文檢索目錄】目錄建立DocumentSummary和Document欄位的全文檢索索引，如下所示： USE AdventureWorks GO CREATE FULLTEXT INDEX ON Production.Document (DocumentSummary, Document TYPE COLUMN FileExtension) KEY INDEX PK_Document_DocumentNode ON 產品文件_全文檢索目錄 WITH CHANGE_TRACKING AUTO

21 20-4 修改與刪除全文檢索目錄與索引 20-4-1 修改全文檢索目錄 20-4-2 修改與停用全文檢索索引 20-4-3 刪除全文檢索索引
刪除全文檢索目錄

22 20-4-1 修改全文檢索目錄- 使用Management Studio

23 20-4-1 修改全文檢索目錄- 使用T-SQL指令(語法)
T-SQL語言是使用ALTER FULLTEXT CATALOG指令來修改全文檢索目錄，其語法如下所示： ALTER FULLTEXT CATALOG 全文檢索目錄名稱 { REBUILD [WITH ACCENT_SENSITIVITY = {ON | OFF}] | REORGANIZE | AS DEFAULT }

24 20-4-1 修改全文檢索目錄- 使用T-SQL指令(範例)
SQL指令碼檔：Ch20_4_1.sql 在【教務系統】資料庫重建名為【教務_全文檢索目錄】的全文檢索目錄，如下所示： USE 教務系統 GO ALTER FULLTEXT CATALOG 教務_全文檢索目錄 REBUILD WITH ACCENT_SENSITIVITY = OFF

25 20-4-2 修改與停用全文檢索索引- 使用Management Studio(修改)
修改全文檢索索引 請啟動Management Studio，在「物件總管」視窗指定全文檢索目錄上，執行【右】鍵快顯功能表的【屬性】指令，可以看到「全文檢索目錄」對話方塊。選【資料表/檢視】頁面，就可以修改全文檢索索引。 換一種方式，我們也可以在指定資料表上，執行【右】鍵快顯功能表的「全文檢索索引>屬性」指令來修改全文檢索索引。

26 20-4-2 修改與停用全文檢索索引- 使用Management Studio(停用)
對於已經停用的全文檢索索引，在資料表上執行【右】鍵快顯功能表的「全文檢索索引>啟用全文檢索索引」指令即可再度啟用全文檢索索引。

27 20-4-2 修改與停用全文檢索索引- 使用T-SQL指令(語法)
T-SQL語言是使用ALTER FULLTEXT INDEX指令修改與停用全文檢索索引，其語法如下所示： ALTER FULLTEXT INDEX ON 資料表名稱 { ENABLE | DISABLE | SET CHANGE_TRACKING {MANUAL | AUTO | OFF} | ADD (欄位名稱 [ TYPE COLUMN 型態的欄位名稱] [ LANGUAGE 語言名稱] [, …n ]) [ WITH NO POPULATION] | DROP (欄位名稱 [, …n]) }

28 20-4-2 修改與停用全文檢索索引- 使用T-SQL指令(範例)
SQL指令碼檔：Ch20_4_2.sql 修改【員工】資料表的全文檢索索引，新增索引的欄位【身份證字號】，如下所示： USE 教務系統 GO ALTER FULLTEXT INDEX ON 員工 ADD (身份證字號)

29 20-4-3 刪除全文檢索索引- 使用Management Studio

30 20-4-3 刪除全文檢索索引- 使用T-SQL指令
T-SQL語言是使用DROP FULLTEXT INDEX指令來刪除全文檢索索引，其基本語法如下所示： DROP FULLTEXT INDEX ON 資料表名稱 SQL指令碼檔：Ch20_4_3.sql 在【教務系統】資料庫刪除【員工】資料表的全文檢索索引，如下所示： USE 教務系統 GO DROP FULLTEXT INDEX ON 員工

31 20-4-4 刪除全文檢索目錄- 使用Management Studio

32 20-4-4 刪除全文檢索目錄- 使用T-SQL指令
T-SQL語言是使用DROP FULLTEXT CATALOG指令來刪除全文檢索目錄，其語法如下所示： DROP FULLTEXT CATALOG 全文檢索目錄名稱 SQL指令碼檔：Ch20_4_4.sql 刪除【教務系統】資料庫名為【教務_全文檢索目錄】的全文檢索目錄，如下所示： USE 教務系統 GO DROP FULLTEXT CATALOG 教務_全文檢索目錄

33 20-5 母體擴展的使用 在SQL Server執行母體擴展 建立母體擴展排程

34 20-5 母體擴展的使用-說明 母體擴展（Population）就是將資料加入全文檢索索引的程序，換句話說，就是維護全文檢索索引的程序，即在新增或更新記錄後，維護全文檢索索引資料的方式。

35 20-5 母體擴展的使用-種類 完整母體擴展（Full Population）：全部更新重建全文檢索索引。當建立過完整母體擴展後，就可以只執行遞增母體擴展或變更追蹤母體擴展來更新全文檢索索引。 遞增母體擴展（Incremental Population）：只更新上一次執行母體擴展後記錄有變動的部分，不過，當使用此類型的母體擴展，建立索引的資料表需要擁有timestamp型別的欄位，以判斷記錄是否更新。 變更追蹤母體擴展（Update Population）：使用全文檢索服務的自動追蹤變更功能執行母體擴展，它會自動追蹤資料表的變更，並且將它記錄下來，我們只需執行指令套用變更記錄，即可執行全文檢索索引的變更追蹤母體擴展。

36 20-5-1 在SQL Server執行母體擴展- 使用Management Studio

37 20-5-1 在SQL Server執行母體擴展- 使用T-SQL指令(語法)
T-SQL語言是使用ALTER FULLTEXT INDEX指令來執行母體擴展，其基本語法如下所示： ALTER FULLTEXT INDEX ON 資料表名稱 { START {FULL | INCREMENTAL | UPDATE} POPULATION | STOP POPULATION }

38 20-5-1 在SQL Server執行母體擴展- 使用T-SQL指令(範例)
SQL指令碼檔：Ch20_5_1.sql 請在【AdventureWorks】資料庫的【Production.Document】資料表啟動完整母體擴展（因為有啟用自動變更追蹤功能，我們需要先停用變更追蹤，才能成功執行完整母體擴展），如下所示： USE AdventureWorks GO ALTER FULLTEXT INDEX ON Production.Document START FULL POPULATION

39 建立母體擴展排程 SQL Server除了可以自行執行T-SQL指令來手動母體擴展全文檢索索引外，也可以在全文檢索目錄建立母體擴展排程，使用排程在排程到達時，自動執行此目錄下所有全文檢索索引的母體擴展，例如：建立排程在下班時間來母體擴展全文檢索索引。

40 20-6 使用全文檢索搜尋 20-6-1 使用FREETEXT執行搜尋 20-6-2 使用CONTAINS執行單字搜尋
全文檢索的符合程度搜尋

41 20-6 使用全文檢索搜尋-說明 當建立好全文檢索目錄與索引後，我們就可以使用T-SQL語言的SELECT指令來執行全文檢索搜尋，主要是在HAVING或WHERE子句建立全文檢索的搜尋條件，或在FROM子句取得合併查詢所需的記錄。 在本節範例的全文檢索搜尋是搜尋英文字，因為英文字搜尋包含衍生字，例如：單複數和不同時式等，但是中文字並沒有衍生字，因為之前建立全文檢索索引時，並沒有指定斷詞工具的語言，所以預設是繁體中文，此時需要將它改為English，才能執行衍生字搜尋。

42 20-6 使用全文檢索搜尋-指定斷詞工具 請在Management Studio開啟【產品文件_全文檢索目錄】的屬性對話方塊，如右圖所示：

43 使用FREETEXT執行搜尋-語法 FREETEXT指令敘述可以在指定搜尋條件的句子中，分開執行句子中每一個單字的搜尋，包含完全符合和衍生字，其基本語法如下所示： FREETEXT ({欄位名稱 | 欄位名稱清單 | *}, '搜尋字串') 上述語法的第1個參數是欲搜尋的欄位名稱、欄位名稱清單，「*」代表全文檢索索引的所有欄位。第2個參數是使用單引號括起的搜尋字串，可以是完整句子或空白分隔的多個單字。

44 20-6-1 使用FREETEXT執行搜尋-範例 SQL指令碼檔：Ch20_6_1.sql
請在【AdventureWorks】資料庫的【Production.Document】資料表使用全文檢索搜尋來搜尋包含'replace pedal'各別單字和衍生字的記錄，如下所示： USE AdventureWorks GO SELECT DocumentNode, DocumentSummary FROM Production.Document WHERE FREETEXT (DocumentSummary, 'replace pedal')

45 20-6-2 使用CONTAINS執行單字搜尋-語法
上述語法的第1個參數是欲搜尋的欄位名稱、欄位名稱清單，「*」代表全文檢索索引的所有欄位。第2個參數是使用單引號括起的搜尋條件。

46 20-6-2 使用CONTAINS執行單字搜尋-範例
SQL指令碼檔：Ch20_6_2_01.sql 請在【AdventureWorks】資料庫的【Production.Document】資料表使用全文檢索搜尋來搜尋包含單字bicycle的記錄，如下所示： USE AdventureWorks GO SELECT DocumentNode, DocumentSummary FROM Production.Document WHERE CONTAINS (DocumentSummary, 'bicycle')

47 20-6-2 使用CONTAINS執行單字搜尋- 使用邏輯運算子(說明)
如果CONTAINS指令敘述搜尋的單字不只一個，我們可以使用AND或OR邏輯運算子來連接，其連接的每一個單字都需要使用雙引號括起。

48 20-6-2 使用CONTAINS執行單字搜尋- 使用邏輯運算子(範例)
SQL指令碼檔：Ch20_6_2_02.sql 請在【AdventureWorks】資料庫的【Production.Document】資料表使用全文檢索搜尋來搜尋包含單字bicycle和pedals的記錄，如下所示： USE AdventureWorks GO SELECT DocumentNode, DocumentSummary FROM Production.Document WHERE CONTAINS (DocumentSummary, '"bicycle" AND "pedals"')

49 20-6-2 使用CONTAINS執行單字搜尋- 使用萬用字元執行搜尋(說明)
在CONTAINS指令敘述的搜尋條件也可以在英文字首後使用萬用字元「*」來代表任何以此字首開始的單字。例如：pa*表示所有pa字首的單字。

50 20-6-2 使用CONTAINS執行單字搜尋- 使用萬用字元執行搜尋(範例)
SQL指令碼檔：Ch20_6_2_03.sql 請在【AdventureWorks】資料庫的【Production.Document】資料表使用全文檢索搜尋來搜尋包含pa字首單字的記錄，如下所示： USE AdventureWorks GO SELECT DocumentNode, DocumentSummary FROM Production.Document WHERE CONTAINS (DocumentSummary, '"pa*"')

51 20-6-2 使用CONTAINS執行單字搜尋- 執行衍生字搜尋(說明)
對於英文單字來說，單字擁有現在式、過去式、未來式和單複數等多種衍生字，CONTAINS指令敘述如果需要同時搜尋所有的衍生字，我們需在搜尋條件使用FORMSOF來執行衍生字搜尋，如下所示： CONTAINS (DocumentSummary, 'FORMSOF (INFLECTIONAL, replace)')

52 20-6-2 使用CONTAINS執行單字搜尋- 執行衍生字搜尋(範例)
SQL指令碼檔：Ch20_6_2_04.sql 請在【AdventureWorks】資料庫的【Production.Document】資料表使用全文檢索搜尋來搜尋包含replace衍生字的記錄，如下所示： USE AdventureWorks GO SELECT DocumentNode, DocumentSummary FROM Production.Document WHERE CONTAINS (DocumentSummary, 'FORMSOF (INFLECTIONAL, replace)')

53 20-6-2 使用CONTAINS執行單字搜尋- 位置接近的字串搜尋(說明)
CONTAINS指令敘述的搜尋條件可以搜尋位置接近的兩個單字，例如：在oil單字附近有grease單字，此時是使用NEAR運算子。

54 20-6-2 使用CONTAINS執行單字搜尋- 位置接近的字串搜尋(範例)
SQL指令碼檔：Ch20_6_2_05.sql 請在【AdventureWorks】資料庫的【Production.Document】資料表使用全文檢索搜尋來搜尋包含guidelines單字附近有recommendations單字的記錄，如下所示： USE AdventureWorks GO SELECT DocumentNode, DocumentSummary FROM Production.Document WHERE CONTAINS (DocumentSummary, 'guidelines NEAR recommendations')

55 全文檢索的符合程度搜尋-說明 符合程度搜尋（Ranked Searching）的搜尋結果會傳回系統自動產生的符合程度等級。在SQL Server可以使用FREETEXTTABLE()和CONTAINSTABLE()函數來建立全文檢索的符合程度搜尋。 不同於本節前的全文檢索搜尋是在WHERE子句建立搜尋條件，FREETEXTTABLE()和CONTAINSTABLE()函數是使用在FROM子句，可以取回滿足搜尋條件的記錄資料。

56 20-6-3 全文檢索的符合程度搜尋- 建立測試的全文檢索索引
在執行本節的範例前，請先執行Ch20_6_3_01.sql的SQL指令碼檔案，可以建立Production.ProductModel資料表的全文檢索索引，欄位包含Name、CatalogDescription和Instructions，後2個欄位的資料類型是xml。

57 20-6-3 全文檢索的符合程度搜尋- FREETEXTTABLE()函數(說明)
FREETEXTTABLE()函數除了可以傳回滿足搜尋條件的記錄資料外，還會額外傳回KEY和RANK欄位，KEY欄位是全文檢索索引的唯一索引鍵值；RANK欄位則是系統自動產生的符合程度等級（值是0~1000）。其基本語法如下所示： FREETEXTTABLE (資料表名稱, {欄位名稱 | 欄位名稱清單 | *}, '搜尋字串', 語言) 上述語法和FREETEXT類似，只是多了第1個參數的資料表名稱，這就是全文檢索索引所在的資料表，最後一個參數是斷詞工具的語言，以英文來說，就是LANGUAGE 1033。

58 20-6-3 全文檢索的符合程度搜尋- FREETEXTTABLE()函數(範例1a)
SQL指令碼檔：Ch20_6_3_02.sql 請在【AdventureWorks】資料庫【Production.ProductModel】資料表使用合併查詢來進行FREETEXTTABLE()函數的符合程度搜尋，以便搜尋包含'washer weld polish'各別單字的記錄，查詢結果是以RANK欄位進行排序，如下所示： USE AdventureWorks GO SELECT R_Table.RANK,L_Table.ProductModelID, L_Table.Name

59 20-6-3 全文檢索的符合程度搜尋- FREETEXTTABLE()函數(範例1b)
FROM Production.ProductModel L_Table INNER JOIN FREETEXTTABLE(Production.ProductModel, Instructions, 'washer weld polish', LANGUAGE 1033) R_Table ON L_Table.ProductModelID = R_Table.[KEY] ORDER BY R_Table.RANK DESC

60 20-6-3 全文檢索的符合程度搜尋- CONTAINSTABLE()函數(說明)
CONTAINSTABLE()函數的功能和FREETEXTTABLE()函數相當，可以執行符合程度搜尋，其語法和CONTAINS類似，筆者就不重複說明。 不過，CONTAINSTABLE()函數和CONTAINS指令敘述都可以替搜尋條件加上權值（Weight）來執行符合程度搜尋，其指定的權值會影響符合程度的等級，換句話說，CONTAINSTABLE()函數符合程度的搜尋結果是可以透過權值來調整的。

61 20-6-3 全文檢索的符合程度搜尋- CONTAINSTABLE()函數(語法)
在CONTAINSTABLE()函數和CONTAINS指令敘述替個別單字加上權值是使用ISABOUT指令，其語法如下所示： ISABOUT {搜尋單字} [WEIGHT (權值)] [, …n] 上述語法可以指定各別搜尋單字的權值，權值範圍是在0.0至1.0之間，如果單字不只一個，請使用「,」逗號分隔。

62 20-6-3 全文檢索的符合程度搜尋- CONTAINSTABLE()函數(範例1a)
SQL指令碼檔：Ch20_6_3_03.sql 請在【AdventureWorks】資料庫【Production.ProductModel】資料表使用合併查詢進行CONTAINSTABLE()函數的符合程度搜尋，並且使用ISABOUT來替washer、weld和polish單字加上權值，如下所示： USE AdventureWorks GO SELECT R_Table.RANK,L_Table.ProductModelID, L_Table.Name FROM Production.ProductModel L_Table

63 20-6-3 全文檢索的符合程度搜尋- CONTAINSTABLE()函數(範例1b)
INNER JOIN CONTAINSTABLE(Production.ProductModel, Instructions, 'ISABOUT (washer WEIGHT (1.0), weld WEIGHT (0.5), polish WEIGHT (0.1))') R_Table ON L_Table.ProductModelID = R_Table.[KEY] ORDER BY R_Table.RANK DESC