全文索引和查詢概念

(摘自)
[@more@]

一 全文索引及查詢概述
全文索引、查詢和同步化最主要的設計要求是，在註冊進行全文檢索的所有表上都有一個唯一的全文鍵列（或者單列主鍵）。全文索引對使用的重要字及其所在位置進行跟蹤。 例如，假定有一個對 DevTools 表的全文索引。全文索引可能指出在 Abstract 列的第 423 個和第 982 個單詞處找到了單詞 Microsoft，所在的行與 ProductID 6 關聯。該索引結構支援對所有包含被索引單詞的項進行有效檢索，以及高階檢索操作，如短語檢索和鄰近檢索。 為防止全文索引因包含很多對檢索沒有幫助的詞而變得臃腫，a、and、is 或 the 這類額外的詞都忽略不計。例如，指定"the products ordered during these summer months"與指定"products ordered during summer months"是一樣的。有這兩個字串的行都會被返回。 目錄 /Mssql/Ftdata/Sqlserver/Config 下提供了多種語言的干擾詞列表。在安裝帶有全文檢索支援的 Microsoft® SQL Server™ 時會建立這個目錄，並同時安裝干擾詞檔案。干擾詞檔案可以編輯。例如，高技術公司的系統管理員可以把單詞 computer 新增到他們的干擾詞表中去。（如果編輯干擾詞檔案，則必須在更改生效之前重新填充全文目錄。）下表顯示了干擾詞檔案及其相應的語言。 干擾詞檔案 語言
-------------- ---------
Noise.chs 簡體中文
Noise.cht 繁體中文
Noise.dat 語言中性
Noise.deu 德語
Noise.eng 英語（英國）
Noise.enu 英語（美國）
Noise.esn 西班牙語
Noise.fra 法語
Noise.ita 義大利語
Noise.jpn 日語
Noise.kor 韓文
Noise.nld 荷蘭語
Noise.sve 瑞典語 在處理全文查詢時，檢索引擎將滿足檢條件的行的鍵值返回給 Microsoft SQL Server。比如有一個 SciFi 表，其中 Book_No 列是主鍵列。

Book_No Writer Title
-------- ----------- --------------------------
A025 Asimov Foundation's Edge
A027 Asimov Foundation and Empire
C011 Clarke Childhood's End
V109 Verne Mysterious Island 假定想使用一個全文檢索查詢來查詢包含單詞 Foundation 的書名。在本例中，將從全文索引獲得值 A025 和 A027。然後 SQL Server 用這些鍵值和其它欄的資訊響應該查詢。 下表顯示了儲存全文索引資料所使用的語言。這些語言基於 SQL Server 安裝期間選擇的 Unicode 排序規則區域設定識別符號。

Unicode 排序規則區域設定識別符號	全文資料儲存所用的語言
中文注音符號（臺灣）	繁體中文
漢語拼音	簡體中文
中文筆畫	簡體中文
中文筆畫（臺灣）	繁體中文
荷蘭語	荷蘭語
英語（英國）	英語（英國）
法語	法語
通用 Unicode	英語（美國）
德語	德語
德文電話簿	德語
義大利語	義大利語
日語	日語
日語 Unicode	日語
韓文	韓文
韓文 Unicode	韓文
西班牙語（現代）	西班牙語
瑞典/芬蘭語	瑞典語

此列表中沒有的其它所有 Unicode 排序規則區域設定識別符號值都對映到使用空格分隔單詞的中性語言單詞的斷字元和詞幹分隔符。 說明 Unicode 排序規則區域設定識別符號設定用於所有可進行全文索引的資料型別（如 char、nchar 等）。如果為 char、varchar 或 text 型別列的排序次序設定的語言型別，不是 Unicode 排序規則區域設定識別符號語言，那麼在對 char、varchar 和 text 型別的列進行全文索引和查詢時，仍然使用 Unicode 排序規則區域設定識別符號值。


--------------------------------------------------------------- 建立全文索引(以索引image列為例,其他型別欄位大致一樣) 標題 全文索引image列，全攻略！ 作者 pengdali [原作] 關鍵字 全文索引 image 今天“百年不遇”的停電了，看了一天書。晚上搞了一下全文索引，決定把心得貼出來，我儘量寫的詳細，大家共同學習，歡迎指正！

1、啟動 Microsoft Search 服務 開始選單--&gtSQL程式組--&gt服務管理器--&gt下拉筐--&gtMicrosoft Search 服務--&gt啟動它

2、
../Microsoft SQL Server/MSSQL/FTDATA/SQLServer/Config/目錄裡建一個非空noise.chs檔案 非空noise.chs檔案，也有人說是空的noise.chs檔案，但我每次都往裡寫幾個沒用的字母。

3、建立環境 開啟查詢分析器--&gt執行下列指令碼：
--------------------------------------------
create database test ---建立test資料庫
use test ---選擇test資料庫
create table dali (ID int not null primary key,MyImage image,FileType varchar(255),FileNmae varchar(255)) ---建立dali表
--dali表中 Id,MyImage,FileType 三列是必須的，因為要對image列索引的話，必須要有一個主鍵列，一個image列,一個存放檔案型別的列
--我們知道在windows系統中檔案型別是靠副檔名來區分的所以FileType列也就是用來放 檔案的副檔名
--------------------------------------------

sp_fulltext_database 'enable' --為全文索引啟用資料庫
sp_fulltext_catalog 'My_FullDir', 'create' ---建立一個叫My_FullDif的全文目錄

declare @Key sysname ; select @Key=c.name from syscolumns a,sysconstraints b,sysobjects c where a.id=object_id('dali') and a.name='ID' and a.id=b.id and b.constid=c.id and c.name like 'PK%'
exec sp_fulltext_table 'dali','create','My_FullDir',@Key ----這兩句是為全文索引,對錶進行標記

sp_fulltext_column 'dali','MyImage','add',0x0804,'FileType' ---這句是指定MyImage列為全文索引列,FileType是型別列
------------------------------------------------
4、在c盤下放一個副檔名為doc的word檔案,一個副檔名為xls的excel檔案,一個副檔名為htm的網頁檔案,個副檔名為bmp的圖片 共4個，大家可根據實際情況放入！

5、插入資料 建立下面這個儲存過程
/* 這是使用textcopy工具將檔案插入到資料庫中，如果有前臺工具可以用前臺開發工具將檔案插入，這裡為了演示 */
/* 把C:Program FilesMicrosoft SQL ServerMSSQLBinn textcopy.exe 複製到C:下 */
--------------------------------------------------
CREATE PROCEDURE sp_textcopy

@srvname varchar (30),

@login varchar (30),

@password varchar (30),

@dbname varchar (30),

@tbname varchar (30),

@colname varchar (30),

@filename varchar (30),

@whereclause varchar (40),

@direction char(1)

AS

BEGIN

DECLARE @exec_str varchar (255)

SELECT @exec_str='C:textcopy /S '+@srvname+' /U '+@login+' /P '+@password+' /D '+@dbname+' /T'+@tbname+' /C '+@colname+' /W"'+@whereclause+'" /F"'+@filename+'" /'+@direction

EXEC master..xp_cmdshell @exec_str

END

GO

----------------------------------------------------

insert dali values(1,0x,'doc','大力的doc') ---其中第二列是 0x 它是一個16進位制數對應image列,是必須的,不要寫null（如果是text型別則為空字串''）,第三列是檔案型別，既副檔名

sp_textcopy '你的伺服器名','sa','你的密碼','test','dali','MyImage','c:/大力的doc.doc','where ID=1','I'
-------依次引數是：例項名,使用者名稱,密碼，資料庫名，表名，image列名，路徑及檔名，條件(你必須保證它只選擇一行)，I
---------------------------------------------------------------------------------------------------------------
insert dali values(2,0x,'bmp','圖片')
sp_textcopy '你的伺服器名','sa','你的密碼','test','dali','MyImage','c:/圖片.bmp','where ID=2','I' --注意條件是 ID=2

insert dali values(3,0x,'xls','Excel檔案')
sp_textcopy '你的伺服器名','sa','你的密碼','test','dali','MyImage','c:/Excel檔案.xls','where ID=3','I' --注意條件是 ID=3

insert dali values(4,0x,'htm','網頁')
sp_textcopy '你的伺服器名','sa','你的密碼','test','dali','MyImage','c:/網頁.htm','where ID=4','I' --注意條件是 ID=4

---------------------------------------------------------------------------------------------------------------上面的語句,要保證型別一樣，路徑正確，條件唯一正確應該就可以了

6、填充全文索引

sp_fulltext_table 'dali','start_full' ---第一個引數是表名，第二個引數是啟動表的全文索引的完全填充

7、可以開始你的實驗了

select * from dali where contains(MyImage,'J老師')

select * from dali where contains(MyImage,'海老師')

------END----------
--除錯環境：SQLServer2000企業版、Windows2000高階伺服器
8、索引更新問題
要使全文目錄中的內容與資料庫索引表的內容相一致，可透過Full Population或Incremental Population來實現：企業管理器中展開資料庫CoreDB,雙擊Full-Text Catalog,會看到有一個名為content的全文目錄，選中右擊，點Start Full Population或Start Incremental Population，此時即可使用索引功能。
以後資料庫中的索引表有更新時，都需要進行Population同步操作，全文查詢的內容才是新的資料表中的內容。因此我們可以設定sql server自動進行Population操作：右擊名為content的full text catalog,Schedules,New Catalog Schedules,輸入名字，選中Enabled選項，可以設定計劃任務的執行頻率,可設定每次啟動SQL server agent的時候啟動，也可以設定執行一次，或者設定重複執行，重複執行裡面可以設定執行頻率為月，周，日，可以設定每天若干小時執行一次。(這樣只要Service裡面啟動了sql server agent,population同步計劃都會自動執行)
--------------------------------------------------------------------------- 全文索引中的幾個問題:

1.搜尋時出現錯誤: 伺服器: 訊息 7619，級別 16，狀態 1，行 2 查詢子句只包含被忽略的詞 這種情況修改 /Mssql/Ftdata/Sqlserver/Config 下對應語言的干擾詞列表檔案

2.修改了干擾詞檔案,查詢中文時仍然出現上述問題
a.首先檢查你的SQL有沒有安裝最新的補丁,檢查的方法是在查詢分析器中執行:
select @@version 如果出來的版本號是8.00.760以下,則表明你未安裝sp3的補丁,要裝上.

SQL補丁下載：

使用 Microsoft SQL Server 2000 的全文搜尋功能構建 Web 搜尋應用程式：

二 全文索引的使用
1. 建立全文索引

在進行全文檢索之前，必須先建立和填充資料庫全文索引。為了支援全文索引操作，SQL Server 7.0新增了一些儲存過程和Transact-SQL語句。使用這些儲存過程建立全文索引的具體步驟如下（括號內為呼叫的儲存過程名稱）：

1. 啟動資料庫的全文處理功能(sp_fulltext_

database);；

2. 建立全文檢索目錄(sp_fulltext_catalog);

3.在全文檢索目錄中註冊需要全文索引的表(sp_fulltext_table);

4. 指出表中需要全文檢索的列名(sp_fulltext_

column);；

5. 為表建立全文索引(sp_fulltext_table);；

6. 填充全文檢索目錄(sp_fulltext_catalog)。

下面舉例說明如何建立全文索引，在本例中，對Test資料庫Book表中Title列和Notes列建立全文索引。

use test //開啟資料庫

//開啟全文索引支援，啟動SQL Server的全文搜尋服務

execute sp_fulltext_database ‘enable’

//建立全文檢索目錄ft_test

execute sp_fulltext_catalog ‘ft_test’, ‘create’

為Title列建立全文索引資料元，pk_title為Book表中由主鍵所建立的唯一索引，這個引數是必需的。

execute sp_fulltext_table ‘book’，‘create’, ‘ft_test’，‘pk_title’

//設定全文索引列名

execute sp_fulltext_column ‘book’, ‘title’, ‘add’

execute sp_fulltext_column ‘book’，‘notes’, ‘add’

//建立全文索引

execute sp_fulltext_table ‘book’, ‘activate’

//填充全文索引目錄

execute sp_fulltext_catalog ‘ft_test’, ‘start_full’

至此，全文索引建立完畢。

2. 進行全文檢索

SQL Server 2000提供的全文檢索語句主要有CONTAINS和FREETEXT。CONTAINS語句的功能是在表的所有列或指定列中搜尋：一個字或短語；一個字或短語的字首；與一個字相近的另一個字；一個字的派生字；一個重複出現的字。

CONTAINS語句的語法格式為：

CONTAINS({column | *}),
_condition>)

其中，column是搜尋列，使用“*”時說明對錶中所有全文索引列進行搜尋。Contains_search_

condition 說明CONTAINS語句的搜尋內容，其語法格式為：

{||||}[{{AND|AND NOT|OR}}] [...n]

下面就simple_term和prefix_term引數做簡要說明：

simple_term是CONTAINS語句所搜尋的單字或短語，當搜尋的是一個短語時，必須使用雙引號作為定界符。其格式為：

{‘word’|“ phrase”}

prefix_term說明CONTAINS語句所搜尋的字或短語字首，其格式為：

{“word*” | “phrase*”}

例如，下面語句檢索Book表的Title列和Notes列中包含“database”或“computer”字串的圖書名稱及其註釋資訊：

select title, notes

from book

where contains(tilte, ‘database’) or contains(notes，‘database’)

or contains(title，‘computer’) or contains(notes，‘computer’)

FREETEXT語句的功能是在一個表的所有列或指定列中搜尋一個自由文字格式的字串，並返回與該字串匹配的資料行。所以，FREETEXT語句所執行的功能又稱做自由式全文查詢。

FREETEXT語句的語法格式為：FREETEXT({column | * }，‘freetext_string’)

其中，column是被搜尋列，使用“*”時說明對錶中的所有全文索引列進行搜尋。Freetext_string引數指出所搜尋的自由文字格式字串。

例如，下面語句使用FREETEXT語句搜尋Book表中包含“Successful Life”字串的資料行：

select title, notes

from book

where freetext(*，‘Successful Life’)