你所在位置:首页Sql资源 → Sql Server全文索引实例

Sql Server全文索引实例

发布时间:2019-07-12


Sql Server全文索引实例

 

create   table   testIndex 

          id   int   identity(1,1)   primary   key, 

          nm   varchar(100)   unique   not   null, 

          sex   varchar(10) 

createUNIQUEindex UQ__testIndex__0DAF0CB0

on testindex(nm)

  www.350job.com  

insert   into   testindex   

          select   'aaabbb','m'   union   all 

          select   'bbb','w'   union   all 

          select   'ccc','w'   union   all 

          select   'ddd','m' 

 

insert   into   testindex   

          select   '麦蒂未伤愈中途退出训练复出时间再度成疑','北京'  

go

--创建全文目录 

sp_fulltext_catalog   'abc','create'     

go

--创建全文索引(‘表名‘,’创建/删除‘,’全文目录名‘,’约束名‘) 

sp_fulltext_table   'testindex','create','abc','UQ__testIndex__0DAF0CB0' 

go

--添加列到全文索引(‘表名‘,’列名‘,’添加/删除‘) 

sp_fulltext_column   'testindex','nm','add' 

 

go

--建立全文索引

--activate,是激活表的全文检索能力,也就是在全文目录中注册该表

execute sp_fulltext_table 'testindex','activate'

go  www.350job.com  

--填充全文索引目录

execute sp_fulltext_catalog 'abc','start_full'

go

 

--检查全文目录填充情况

Whilefulltextcatalogproperty('abc','populateStatus')<>0

begin

 

--如果全文目录正处于填充状态,则等待30秒后再检测一次

waitfor delay '0:0:30'

end

 

--全文目录填充完成后,即可使用全文目录检索

 

SELECT   *   FROM   testindex   WHERE   CONTAINS(nm,   '麦蒂') 

 

/*

id          nm                                                                                                 sex        

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

5           麦蒂未伤愈中途退出训练复出时间再度成疑                                                             北京

 

(所影响的行数为 1 行)

*/  www.350job.com  

insert   into   testindex   

          select   '麦蒂未伤愈中途退出训练复出时间再度成疑12121','北京'  

go

SELECT   *   FROM   testindex   WHERE   CONTAINS(nm,   '麦蒂')

-----No result

/*

 

id          nm                                                                                                 sex        

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

5           麦蒂未伤愈中途退出训练复出时间再度成疑                                                             北京

 

(所影响的行数为 1 行)

*/

go

 

--填充全文索引目录

execute sp_fulltext_catalog 'abc','start_full'

go

--检查全文目录填充情况

Whilefulltextcatalogproperty('abc','populateStatus')<>0

begin  www.350job.com  

 

--如果全文目录正处于填充状态,则等待30秒后再检测一次

waitfor delay '0:0:30'

end

 

SELECT   *   FROM   testindex   WHERE   CONTAINS(nm,   '麦蒂')

 

go

/*

 

id          nm                                                                                                   sex        

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

6           麦蒂未伤愈中途退出训练复出时间再度成疑12121                                                                             北京

5           麦蒂未伤愈中途退出训练复出时间再度成疑                                                                                  北京

(所影响的行数为 2 行)

 

*/

sp_fulltext_table  'testindex','drop'

go

sp_fulltext_catalog   'abc','drop'  

go    www.350job.com  

droptable testIndex

 

-------------开启全文索引和创建全文索引目录 

exec sp_fulltext_database 'enable' 

--exec sp_fulltext_catalog 'gf_ft','drop' 

exec sp_fulltext_catalog 'gf_ft','create' 

 

-------------为news表创建全文索引 可索引列为 title,abstract,text 

 

exec sp_fulltext_table 'product','create','gf_ft','pk_product' 

exec sp_fulltext_column 'product','productname','add' 

 

-------------激活索引 

exec sp_fulltext_table 'product','activate' 

exec sp_fulltext_table 'product','start_full' 

 

--检查全文目录填充情况 

Whilefulltextcatalogproperty('gf_ft','populateStatus') <>0 

begin 

 

--如果全文目录正处于填充状态,则等待5秒后再检测一次 

waitfor delay '0:0:5' 

END   www.350job.com  

 

exec sp_fulltext_catalog 'gf_ft','start_full' 

 

--检查全文目录填充情况 

Whilefulltextcatalogproperty('gf_ft','populateStatus') <>0 

begin 

 

--如果全文目录正处于填充状态,则等待5秒后再检测一次 

waitfor delay '0:0:5' 

END 

 

--------------测试------------------ 

SELECT*FROM product WHERECONTAINS(productname,'美赞臣') 

 

-----------------------卸载------------------ 

 

EXEC sp_fulltext_table 'product','deactivate' 

exec sp_fulltext_column 'product', 'productname', 'drop' 

EXEC sp_fulltext_table 'product', 'drop' 

EXEC sp_fulltext_catalog 'gf_ft', 'stop' 

EXEC sp_fulltext_catalog 'gf_ft', 'drop' 

 

******************************************************************

基本知识  www.350job.com  

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。  www.350job.com  

 

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"

将新柯技尤氲侥柯技现/p>

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

   www.350job.com  

激活该表上的全文本索引

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. 启动全文本目录  www.350job.com  

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  www.350job.com  

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' 等字样的地址。

          记住是*,不是%。

   www.350job.com  

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 等等。

 

以上例子都使用英文,不使用中文是因为有的查询方式中文不支持,而且我的计算机是英文系统

   www.350job.com  

叮憾浴度募焖鞯弥室伞罚/p>

 

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 语句中我们可以把它当作一个普通的表来使用。

   www.350job.com  

我们看一个例子,比较这两种表的不同。

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。我们通常把它作为表连接的条件。

   www.350job.com  

在某些网站搜索时,结果中会出现表示匹配程度的数字,RANK 与此类似。它的值在~1000之间,标识每一行与查询条件的匹配程度,程度越高,RANK 的值大,通常情况下,按照RANK 的降序排列。
 

FREETEXT 语法

FREETEXT 与CONTAINS 类似,只是没有CONTAINS 的精度高。在CONTAINS 中,对查询条件的写法有很多要求,而FREETEXT 就没有,可以是任意的单词,词组或句子。看下面的例子:

 

SELECT CategoryName

FROM Categories

WHERE FREETEXT (Description, 'sweetest candy bread and dry meat' )

   www.350job.com  

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") & "' )"

   www.350job.com  

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 查询一样。


上一篇:新装好SQL2005时SA无法登陆的解决办法
下一篇:关于SQL的执行顺序