|
第一版:
?
create?? table?? testIndex
(
????????? id?? int?? identity(1,1)?? primary?? key,
????????? nm?? varchar(100)?? unique?? not?? null,
????????? sex?? varchar(10)
)
create UNIQUE index UQ__testIndex__0DAF0CB0
on testindex(nm)
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
--填充全文索引目录
execute sp_fulltext_catalog 'abc','start_full'
go
--检查全文目录填充情况
While fulltextcatalogproperty('abc','populateStatus')<>0
begin
--如果全文目录正处于填充状态，则等待30秒后再检测一次
waitfor delay '0:0:30'
end
--全文目录填充完成后，即可使用全文目录检索
SELECT?? *?? FROM?? testindex?? WHERE?? CONTAINS(nm,?? '麦蒂')
/*
id????????? nm???????????????????????????????????????????????????????????????????????????????????????????????? sex???????
----------- --------------------------------------------- ------------------------------------------------ ----------
5?????????? 麦蒂未伤愈中途退出训练复出时间再度成疑???????????????????????????????????????????????????????????? 北京
（所影响的行数为 1 行）
*/
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
--检查全文目录填充情况
While fulltextcatalogproperty('abc','populateStatus')<>0
begin
--如果全文目录正处于填充状态，则等待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?
drop table testIndex
?
?
第二版:
?
/*
在库TEST上建立全文索引
*/
use test
create table poofly(id int not null,name varchar(10))
go
/* 首先创建一个唯一索引，以便全文索引利用*/
create unique clustered? index un_ky1 on poofly(id)
/*创建全文目录*/
create FULLTEXT CATALOG FT1 AS DEFAULT
/*C创建全文索引*/
create FULLTEXT INDEX ON poofly(NAME) key index un_ky1 ON? FT1
/*修改全文目录*/
alter FULLTEXT CATALOG FT1? REBUILD
/*删除全文目录FT(含有全文索引时候不能删除)*/
drop fulltext catalog ft
/*查看数据库所有的全文目录*/
select* from sys.fulltext_catalogs
/*
fulltext_catalog_id name???????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????? path???????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????? is_default is_accent_sensitivity_on data_space_id file_id???? principal_id is_importing
------------------- -------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------- ------------------------ ------------- ----------- ------------ ------------
5?????????????????? test???????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????? NULL???????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????? 0????????? 1??????????????????????? NULL????????? NULL??????? 1??????????? 0
11????????????????? FT1????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????? NULL???????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????? 1????????? 1??????????????????????? NULL????????? NULL??????? 1??????????? 0
*/
/* 查看所有用到全文索引的表*/
exec sp_help_fulltext_tables
/*
TABLE_OWNER????????????????????????????????????????????????????????????????????????????????????????????????????????????????????? TABLE_NAME?????????????????????????????????????????????????????????????????????????????????????????????????????????????????????? FULLTEXT_KEY_INDEX_NAME????????????????????????????????????????????????????????????????????????????????????????????????????????? FULLTEXT_KEY_COLID FULLTEXT_INDEX_ACTIVE FULLTEXT_CATALOG_NAME
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------------------ --------------------- --------------------------------------------------------------------------------------------------------------------------------
dbo????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????? poofly?????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????? un_ky1?????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????? 1????????????????? 1???????????????????? FT1
*/
/*
在库TEST上建立全文索引
*/
use test
create table poofly(id int not null,name varchar(10))
go
/* 首先创建一个唯一索引，以便全文索引利用*/
create unique clustered? index un_ky1 on poofly(id)
/*创建全文目录*/
create FULLTEXT CATALOG FT1 AS DEFAULT
/*C创建全文索引*/
create FULLTEXT INDEX ON poofly(NAME) key index un_ky1 ON? FT1
/*修改全文目录*/
alter FULLTEXT CATALOG FT1? REBUILD
/*删除全文目录FT(含有全文索引时候不能删除)*/
drop fulltext catalog ft
---------------------------------------------------
--使用contains关键字进行全文索引
--1.前缀搜索
select name from tb where contains(name,'"china*"')
/*--注意这里的* 返回结果会是 chinax chinay chinaname china?
--返回前缀是china的name
--如果不用“”隔开 那么系统会都城 contains(name,'china*') 与china* 匹配*/
--2.使用派生词搜索
select name from tb where contains(name,'formsof(inflectional,"foot")')
/* 出来结果可能是 foot feet (所有动词不同形态 名词单复数形式)*/
--3.词加权搜索
select value from tb where contains(value,'ISABOUT(performance weight(.8))')
/*全值用0-1的一个数字表示 表示每个词的重要程度*/
--4.临近词搜素
select * from tb where contains(document,'a near b')
/* 出来的结果是“a”单词与“b”单词临近的document
可以写成 contains(document,'a ~ b')
*/
--5.布尔逻辑搜素
select * from tb where contains(name,'"a" and "b"')
/*返回既包含A 又包含 B单词的行
当然 这里的AND 关键字还有换成 OR,AND NOT 等
*/
--------------------------------------------------
--你还可以使用RREETEXT 进行模糊搜索
--任意输入文本 全文索引自动识别重要单词 然后构造一个查询
use test
go
select * from tb where freetext(wendang,'zhubajie chi xi gua !')
--============================================================
--对全文索引性能影响因素很多 包括硬件资源方面 还有SQL 自身性能 和MSFTESQL服务的效率等方面
--它的搜索性能有2方面 : 全文索引性能 和 全文查询性能
第三版:
create?? table?? testIndex
(
????????? id?? int?? identity(1,'drop'?
go?
drop table 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','gf_ft','pk_product'
exec sp_fulltext_column 'product','productname','add'
-------------激活索引
exec sp_fulltext_table 'product','activate'
exec sp_fulltext_table 'product','start_full'
--检查全文目录填充情况
While fulltextcatalogproperty('gf_ft','populateStatus') <>0
begin
--如果全文目录正处于填充状态，则等待5秒后再检测一次
waitfor delay '0:0:5'
END
exec sp_fulltext_catalog 'gf_ft','populateStatus') <>0
begin
--如果全文目录正处于填充状态，则等待5秒后再检测一次
waitfor delay '0:0:5'
END
--------------测试------------------
SELECT * FROM product WHERE CONTAINS(productname,'美赞臣')
-----------------------卸载------------------
EXEC sp_fulltext_table 'product','deactivate'
exec sp_fulltext_column 'product','drop'
EXEC sp_fulltext_table 'product','drop'
EXEC sp_fulltext_catalog 'gf_ft','stop'
EXEC sp_fulltext_catalog 'gf_ft','drop'
2.
先了解一下全文索引是如何创建和使用的
????? 创建全文索引:
????? 在MS SQL SERVER 2005里,全文索引是一个单独的服务项,默认是启动的,但是没有允许数据库启用全文索引,如果要在某个数据库中创建全文索引,先要启用数据库的全文索引.
????? 启用方法:
????? 1.数据库->属性->常规->启用全文索引
????? 2.在查询中执行:sp_.........忘了,如果没有启用就执行创建索引目录,会提示.
?????
????? 启用了索引了,就可以创建索引目录了
????? 在查询中执行以下语句:
????? CREATE FULLTEXT CATALOG 索引目录名
????? 执行以后,就会在SQL SERVER的安装目录里看到一个同名的目录名了,路径为MSSQL.1/MSSQL/FTDATA/索引目录名
?????
????? 每个表只可以有一个全文索引,存放在指定的索引目录里,可以通过向导创建,也可以通过SQL创建
????? 基本语法:
????? CREATE FULLTEXT INDEX ON tablename
????? （索引列1,索引列2...）
????? KEY INDEX indexname ON 索引目录名
????? indexname是指已存在的基于指定表的唯一索引名.而不是唯一索引列名.如果索引不存在,需要先创建唯一索引.
?????
????? 使用全文索引:
????? 主要使用CONTAINS,FREETEXT进行查询
????? 假设已有一个表music,已为字段memo创建全文索引,那么要查询含有周杰伦或者jay的所有记录的语句为:
????? SELECT * FROM music WHERE CONTAINS（memo,'"周杰伦" or "jay"'）
（编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
