SQL2005 全文索引

 最近做了一个搜索功能用到了全文索引，感觉还不错，速度很快，很有效率。整理一下，作为备忘。

 

1。介绍
       首先，介绍利用系统存储过程创建全文索引的具体步骤： 

    1) 启动数据库的全文处理功能                (sp_fulltext_database)

    2) 建立全文目录                          (sp_fulltext_catalog)

    3) 在全文目录中注册需要全文索引的表          (sp_fulltext_table)

    4) 指出表中需要全文索引的列名               (sp_fulltext_column)

    5) 为表创建全文索引                       (sp_fulltext_table)

    6) 设定自动跟踪                          (sp_fulltext_table)

    7) 填充全文目录                          (sp_fulltext_catalog)

 

 

2。示例

use Db_Temp --打开数据库 go --1. 检查数据库Db_Temp是否支持全文索引，如果不支持 --则使用sp_fulltext_database 打开该功能 if(select databaseproperty('Db_Temp','isfulltextenabled'))=0 execute sp_fulltext_database 'enable' --2. 建立全文目录FT_Temp execute sp_fulltext_catalog 'FT_Temp','create' --3. 为T_Product表建立全文索引数据元 execute sp_fulltext_table 'T_Product','create','FT_Temp','PK_T_Product' --4. 设置全文索引列名 execute sp_fulltext_column 'T_Product','pro_name','add' execute sp_fulltext_column 'T_Product','pro_summary','add' --5. 建立全文索引 --activate，是激活表的全文检索能力，也就是在全文目录中注册该表 execute sp_fulltext_table 'T_Product','activate' --6.'更改跟踪' 设为 '自动',跟踪表数据的改变，即时更新到全文索引 execute sp_fulltext_table T_Product, 'Start_background_updateindex' --为多个表创建全文索引重复3-6的步骤。 --7.填充全文索引目录 execute sp_fulltext_catalog 'FT_Temp','start_full' go --8. 检查全文目录填充情况 While fulltextcatalogproperty('FT_Temp ','populateStatus')<>0 begin --如果全文目录正处于填充状态，则等待30秒后再检测一次 waitfor delay '0:0:30' end --9. 填充完成,全文索引创建成功 select * from T_Product where contains(pro_name,'product and test') --在表字段（pro_name）中检索包含'product'和'test'的产品. 如果用"*"则表示在当前表中所有建有全文索引的字段中检索需要的词.

 

3. 其它

--全文索引目录 (Status) --0 = 空闲 --1 = 正在进行完全填充 --2 = 暂停 --3 = 已中止 --4 = 正在恢复 --5 = 关闭 --6 = 正在进行增量填充 --7 = 生成索引 --8 = 磁盘已满。已暂停 --9 = 更改跟踪 sp_help_fulltext_catalogs --全文索引列 sp_help_fulltext_columns --全文索引表 sp_help_fulltext_tables --删除全文索引表 drop fulltext index on T_product --删除索引目录 drop fulltext catalog FT_Temp

 

 

4. 程序应用 (C#)

4.1 清除干扰词

public string DeleteWord(string key) { string[] arr ={"-?-", "-about-", "-$-", "-1-", "-2-", "-3-", "-4-", "-5-", "-6-", "-7-", "-8-", "-9-", "-0-", "-_-", "-a-", "-b-", "-c-", "-d-", "-e-", "-f-", "-g-", "-h-", "-i-", "-j-", "-k-", "-l-", "-m-", "-n-", "-o-", "-p-", "-q-", "-r-", "-s-", "-t-", "-u-", "-v-", "-w-", "-x-", "-y-", "-z-", "-after-", "-all-", "-also-", "-an-", "-and-", "-another-", "-any-", "-are-", "-as-", "-at-", "-be-", "-because-", "-been-", "-before-", "-being-", "-between-", "-both-", "-but-", "-by-", "-came-", "-can-", "-come-", "-could-", "-did-", "-do-", "-each-", "-for-", "-from-", "-get-", "-got-", "-had-", "-has-", "-have-", "-he-", "-her-", "-here-", "-him-", "-himself-", "-his-", "-how-", "-if-", "-in-", "-into-", "-is-", "-it-", "-like-", "-make-", "-many-", "-me-", "-might-", "-more-", "-most-", "-much-", "-must-", "-my-", "-never-", "-now-", "-of-", "-on-", "-only-", "-or-", "-other-", "-our-", "-out-", "-over-", "-said-", "-same-", "-see-", "-should-", "-since-", "-some-", "-still-", "-such-", "-take-", "-than-", "-that-", "-the-", "-their-", "-them-", "-then-", "-there-", "-these-", "-they-", "-this-", "-those-", "-through-", "-to-", "-too-", "-under-", "-up-", "-very-", "-was-", "-way-", "-we-", "-well-", "-were-", "-what-", "-where-", "-which-", "-while-", "-who-", "-with-", "-would-", "-you-", "-your-", "-的-", "-一-", "-不-", "-在-", "-人-", "-有-", "-是-", "-为-", "-以-", "-于-", "-上-", "-他-", "-而-", "-后-", "-之-", "-来-", "-及-", "-了-", "-因-", "-下-", "-可-", "-到-", "-由-", "-这-", "-与-", "-也-", "-此-", "-但-", "-并-", "-个-", "-其-", "-已", "-无-", "-小-", "-我-", "-们-", "-起-", "-最-", "-再-", "-今-", "-去-", "-好-", "-只-", "-又-", "-或-", "-很-", "-亦-", "-某-", "-把-", "-那-", "-你-", "-乃-", "-它-"}; key = "-" + key + "-"; foreach (string str in arr) { key = key.Replace(str, "-"); } key = key.TrimEnd('-').TrimStart('-'); return key; }

 

4.2  合并关键词

keyword=DeleteWord(key).TrimEnd('-').TrimStart('-').Replace("-"," and "); --keyword 就是合并后的检索字符串

 

 

ok, 搞定..