sql全文索引

基本知識

1
. SQL Server7 的 DeskTop 版中沒有全文本檢索。

2
. 一個表只能有一個全文本檢索。

3
. 被檢索的表必須有單列的唯一索引。

4
. 全文本的索引儲存在檔案系統中，而非資料庫中。

5
. 更新全文本索引的過程比常規索引要耗時，而且也不象常規索引那樣可以由資料庫系統立即更新。

6
. 全文本索引包含在全文本目錄(
Full
-
Text
Catalog )中，每個資料庫可以包含一個或多個目錄，但一個目錄不能屬於多個資料庫。

7
. 全文本檢索只能在真正的表上建立，不能是視圖，系統資料表，暫存資料表。

8
. 全文本檢索會忽略某些噪音字( noise words)，比如英文的 a，the，
and
，中文的
'
和
'
，
'
是
'
等等。

9
. 如果在查詢中包含 noise words ，就會引發錯誤，在應用程式中應去除這些 noise words。

啟動全文本檢索服務。
方法A：在企業管理器中開啟 Support Services 檔案夾，在
Full
-
Text
Search 的右鍵菜單中選擇 Start。
方法B：在 SQL Server Service Manager 的 Services 下拉式清單中選擇 Microsoft Search，並單擊 Start
/
Continue
按鈕。
方法C：使用 net start mssearch 的命令列方式。
使用全文本檢索嚮導(
Full
-
Text
Indexing Wizard )。
step1. 選擇被檢索的資料庫，在 Tools 的菜單中，選擇
Full
-
text
Indexing，進入歡迎( Welcome )的螢幕，單擊
next
。
step2. 選擇被檢索的表，單擊
next
。
step3. 選擇唯一索引，單擊
next
。
step4. 選擇被索引的列，單擊
Add
，該列顯示在右欄中。單擊
next
。
step5. 選擇目錄(選擇已存在的目錄，或建立新的目錄)，單擊
next
。
step6. 選擇或建立 population schedule(可選項)，單擊
next
。
step7. 單擊 finish。
使用 SQL
-
DMO (以 VB 為例)
step1. 在工程的引用中選擇 Microsoft SQLDMO Object Library。
step2. 建立 SQLServer 對象。
Dim objSQL
As
New SQLDMO.SQLServer
objSQL.Connect "localhost", "sa", ""
step3. 建立新的目錄，並加入到被索引的資料庫目錄中。
Dim objCatalog
As
New SQLDMO.FullTextCatalog

'
使 pubs 為全文本檢索的資料庫
objSQL.Databases("pubs").EnableFullTextCatalogs

'
建立新的目錄
objCatalog.Name
=
"ftcPubsTest"

'
將新目錄加入到目錄集合中
objSQL.Databases("pubs").FullTextCatalogs.Add objCatalog
step4. 在表上建立全文本索引。
Dim objTable As New SQLDMO.Table

'
指定被索引的表

Set
objTable
=
objSQL.Databases("pubs").Tables("authors")

'
指定目錄名和唯一索引名
objTable.FullTextCatalogName = "ftcPubsTest"
objTable.UniqueIndexForFullText = "UPKCL_auidind"
objTable.FullTextIndex = True

'
指定被索引的列
objTable.Columns("au_lname").FullTextIndex
=
True
objTable.Columns("au_fname").FullTextIndex
=
True

'
啟用該表上的全文本索引
objTable.FullTextIndexActive = True

step5. 啟動全文本目錄
objCatalog.Start SQLDMOFullText_Full
使用預存程序
step1. 使 pubs 為全文本檢索的資料庫
USE Pubs
go
sp_fulltext_database
'
enable
'

step2. 建立新的目錄
sp_fulltext_catalog
'
ftcPubsTest
'
,
'
create
'

step3. 指定被索引的表
sp_fulltext_table
'
authors
'
,
'
create
'
,
'
ftcPubsTest
'
,
'
UPKCL_auidind
'

step4. 指定被索引的列
sp_fulltext_column
'
authors
'
,
'
au_lname
'
,
'
add
'

sp_fulltext_column
'
authors
'
,
'
au_fname
'
,
'
add
'

step5. 啟用該表上的全文本索引
sp_fulltext_table
'
authors
'
,
'
activate
'

step6. 啟動全文本目錄
sp_fulltext_catalog
'
ftcPubsTest
'
,
'
start_full
'

CONTAINS 文法
我們通常在 WHERE 子句中使用 CONTAINS ，就象這樣：SELECT * FROM table_name WHERE CONTAINS(fullText_column,
'
search contents
'
)。

我們通過例子來學習，假設有表 students，其中的 address 是全文本檢索的列。
1. 查詢住址在北京的學生
SELECT student_id,student_name
FROM students
WHERE CONTAINS( address,
'
beijing
'
)
remark: beijing是一個單詞，要用單引號括起來。

2. 查詢住址在河北省的學生
SELECT student_id,student_name
FROM students
WHERE CONTAINS( address,
'
"HEIBEI province"
'
)
remark: HEBEI province是一個片語，在單引號裡還要用雙引號括起來。

3. 查詢住址在河北省或北京的學生
SELECT student_id,student_name
FROM students
WHERE CONTAINS( address,
'
"HEIBEI province"
OR
beijing
'
)
remark: 可以指定邏輯操作符(包括 AND ，AND NOT，OR )。

4. 查詢有
'
南京路
'
字樣的地址
SELECT student_id,student_name
FROM students
WHERE CONTAINS( address,
'
nanjing NEAR road
'
)
remark: 上面的查詢將返回包含
'
nanjing road
'
，
'
nanjing east road
'
，
'
nanjing west road
'
等字樣的地址。
          A NEAR B，就表示條件： A 靠近 B。

5. 查詢以
'
湖
'
開頭的地址
SELECT student_id,student_name
FROM students
WHERE CONTAINS( address,
'
"hu
*
"
'
)
remark: 上面的查詢將返回包含
'
hubei
'
，
'
hunan
'
等字樣的地址。
          記住是 *，不是 %。

6. 類似加權的查詢
SELECT student_id,student_name
FROM students
WHERE CONTAINS( address,
'
ISABOUT (city weight (.
8
), county wright (.
4
))
'
)
remark: ISABOUT 是這種查詢的關鍵字，weight 指定了一個介於 0~1之間的數，類似係數(我的理解)。表示不同條件有不同的側重。

7. 單詞的多態查詢
SELECT student_id,student_name
FROM students
WHERE CONTAINS( address,
'
FORMSOF (INFLECTIONAL,street)
'
)
remark: 查詢將返回包含
'
street
'
，
'
streets
'
等字樣的地址。
         對於動詞將返回它的不同的時態，如：dry，將返回 dry，dried，drying 等等。

以上例子都使用英文，不使用中文是因為有的查詢方式中文不支援，而且我的電腦是英文系統

付：對《全文檢索索引1得質疑》：

5. 更新全文本索引的過程比常規索引要耗時，而且也不象常規索引那樣可以由資料庫系統立即更新。
可以立即更新的
9. 如果在查詢中包含 noise words ，就會引發錯誤，在應用程式中應去除這些 noise words。
不對，查詢時會自己過濾掉noise word，只有查詢的內容全是noise words時才會出現錯誤

CONTAINSTABLE 文法
我們通常在 FROM 子句中使用 CONTAINSTABLE ，就象這樣：SELECT * FROM table_name, CONTAINTABLE(fulltext_table,fullText_column,
'
search condition
'
) WHERE ......。

CONTAINSTABLE 在查詢方式上與 CONTAINS 幾乎一樣，所以就不用贅述了。CONTAINSTABLE 返回的是符合查詢條件的表，在 SQL 陳述式中我們可以把它當作一個普通的表來使用。

我們看一個例子，比較這兩種表的不同。
SELECT FT_TBL.student_name,FT_TBL.student_score,KEY_TBL.RANK
FROM report AS FT_TBL INNER JOIN
     CONTAINSTABLE( student,address,
    
'
ISABOUT (city weight (.
8
), county wright (.
4
))
'
) AS KEY_TBL
     ON FT_TBL.student_id = KEY_TBL.[KEY]
ORDER BY KEY_TBL.RANK
CONTAINSTABLE 返回的表包含有特殊的兩列：KEY，RANK。
在第一部分裡我們就強調了：被全文索引的表必須有唯一索引。這個唯一的索引列在返回的表中就成為 KEY。我們通常把它作為表串連的條件。

在某些網站搜尋時，結果中會出現表示匹配程度的數字，RANK 與此類似。它的值在0~1000之間，標識每一行與查詢條件的匹配程度，程度越高，RANK 的值大，通常情況下，按照 RANK 的降序排列。
FREETEXT 文法
FREETEXT 與 CONTAINS 類似，只是沒有 CONTAINS 的精度高。在 CONTAINS 中，對查詢條件的寫法有很多要求，而 FREETEXT 就沒有，可以是任意的單詞，片語或句子。看下面的例子：
SELECT CategoryName
FROM Categories
WHERE FREETEXT (Description,
'
sweetest candy bread
and
dry meat
'
)

FREETEXTTABLE 文法
和 CONTAINSTABLE 一樣，FREETEXTTABLE 返回帶有 KEY，RANK 的表。舉例說明：
SELECT FT_TBL.CategoryName,
    FT_TBL.Description,
    KEY_TBL.RANK
FROM Categories AS FT_TBL INNER JOIN
    FREETEXTTABLE(Categories, Description,
   
'
sweetest candy bread
and
dry meat
'
) AS KEY_TBL
    ON FT_TBL.CategoryID = KEY_TBL.[KEY]
在 ASP 中使用全文本檢索
Dim cnn
Dim rs
Dim strSQL

strSQL = "SELECT book_name " &_
         "FROM books " &_
         "WHERE CONTAINS( description,
'
"
&
Request("search_condition")
&
"
'
)"

Set cnn = Server.CreateObject("ADODB.Connection")
Set rs = Server.CreateObject("ADODB.RecordSet")

cnn.Open "provider=sqloledb;datasource=.;initial catalog=books;user id=sa;pasword=;"
rs.Open strSQL,cnn

上面的例子十分簡單，僅為示意。只要掌握了 CONTAINS 和 CONTAINSTABLE 的文法，在使用上和一般的 ADO 查詢一樣。