sqlserver中全文检索与csw(测试版)实现分词查询测试
来源：互联网 发布：吉首大学网络交费中心 编辑：程序博客网 时间：2024/05/16 08:50
准备工作
一、将数据
1,2020,1,海口君澜商贸有限公司 ,海南省海口市机场路群芳花园４号 ,089866734896
2,2020,2,海口多达工贸有限公司 ,海南省海口市凤翔东路海南省电石厂３幢３０３室 ,013322088106
3,2020,3,洋浦权陈土建安装有限公司 ,海南省洋浦经济开发区洋浦港商住小区２０２Ｅ房 ,013907656161
4,2020,4,海口共创装饰工程有限公司 ,海南省海口市和平北路新桥大厦底层２号铺面 ,089868531315
5,2020,5,澄迈金黄牛果菜运销有限公司 ,海南省澄迈县福山镇立新路３号 ,013322060448
6,2020,6,北京中理通商标事务所有限公司海口分公司 ,海南省海口市坡博路化工总公司住宅小区临街铺面 ,089866268073
7,2020,7,中铁快运有限公司海口第一营业部 ,海南省海口市海府路１１１号一楼Ａ栋、第６、７间铺面 ,089865220237
8,2020,8,兰州宏业防火门有限责任公司海南分公司 ,海南省海口市南沙路５９号瑞丰公寓Ａ座１５０５室 ,013907558196
9,2020,9,三亚市崖州民歌协会 ,海南省三亚市新好景大酒店 ,013627508580
10,2020,10,海口康舒达健康咨询有限公司 ,海南省海口市国贸路１６号金福城Ｃ－２３１－Ａ房 ,013005001860
11,2020,11,海口蜘蛛计算机网络服务有限公司 ,海南省海口市大同路１１号１栋５０２房 ,089868168658
12,2020,12,海南诚信地产发展有限公司 ,海南省海口市机场东路３０号天福新村１－５０１室 ,089866781907
13,2020,13,五指山阿陀岭旅游开发有限责任公司 ,海南省五指山市农林路旅游宾馆 ,089886639219
14,2020,14,保亭林峰农林业技术开发有限公司 ,海南省保亭黎族苗族自治县红毛丹街粮食局住宿楼４幢首层 ,013307551785
15,2020,15,海口婴之初婴幼儿智力开发咨询有限公司 ,海南省海口市国贸路ＣＭＥＣ大厦二楼中区 ,013876820100
16,2020,16,三亚诚海石油销售有限责任公司 ,海南省三亚市南岛农场 ,013086063688
17,2020,17,海口华尔居装饰工程有限公司 ,海南省海口市国贸路国联大厦Ａ座６０１房 ,013078989488
18,2020,18,三亚天一物业管理有限公司 ,海南省三亚市凤凰镇海坡开发区 ,089988330668
存为t_list.txt,并将t_list.txt存在e根目录下
二、在sqlserver端执行建表语句
CREATE TABLE [dbo].[T_List](
[id] [decimal](18, 0) IDENTITY(1,1) NOT NULL,
[T_Listindexno] [decimal](18, 0) NULL,
[T_Listno] [decimal](18, 0) NULL,
[T_ListEPname] [varchar](100) COLLATE Chinese_PRC_CI_AS NULL,
[T_ListEPAdress] [varchar](100) COLLATE Chinese_PRC_CI_AS NULL,
[T_ListEPPhone] [varchar](100) COLLATE Chinese_PRC_CI_AS NULL,
CONSTRAINT [PK_T_List] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
三、sqlserver执行
EXEC master..xp_cmdshell 'bcp test.dbo.T_List in e:/t_list.txt -c -q -U "sa" -P "pwd" -t","' 将数据导入表中
四、--添加全文索引
exec sp_fulltext_database 'enable'
execute sp_fulltext_catalog 'ft_titles', 'create'--警告: 全文目录 'ft_titles' 使用 FAT 卷。该目录不支持安全设置和差异备份。
execute sp_fulltext_table 'T_List','create', 'ft_titles', 'PK_T_List'
execute sp_fulltext_column 'T_List','t_listepname', 'add'
execute sp_fulltext_table 'T_List','start_change_tracking'
execute sp_fulltext_table 'T_List','Start_background_updateindex'
--测试
select * from T_List where contains(t_listepname, '"洋浦权"')--选出符合条件内容
--如果当前没有搜索条件，快速找出所有行数的语句（indid=1为聚集索引时，indid=0为非聚集索引时）
select rows from sysindexes where object_name(id)='t_list' and indid=1
!,&)=|问题需解决
五、规范数据，将字段内容中的全角数字及字母转为半角
CREATE FUNCTION dbo.fun_sbc_dbc(@s VARCHAR(800))
/*************************************/
--全角转半角
/*************************************/
RETURNS VARCHAR(800)
AS
BEGIN
DECLARE @i INT
SET @i=1
WHILE @i<=LEN(@s)
BEGIN
IF UNICODE(SUBSTRING(@s,@i,1)) BETWEEN 65296 AND 65370
SET @s=STUFF(@s,@i,1,NCHAR(UNICODE(SUBSTRING(@s,@i,1))-65248))
SET @i=@i+1
END
RETURN @s
END
GO
引用
update T_List set T_ListEPAdress=dbo.fun_sbc_dbc(T_ListEPAdress)
六、
建立分页存储过程:
CREATE PROCEDURE [dbo].[PRO_PageView]
@PageCurrent int=1, --页码
@PageSize int=15, --每页的记录数默认为15
@str varchar(4000), --加权字符串
@conditions varchar(200), --条件
@PageCount int OUTPUT --总页数
/*
TANGWF 2007-05-11
分词查询分页存储过程
*/
AS
SET NOCOUNT ON
DECLARE @sql nvarchar(4000)
SET @sql=N''
IF ISNULL(@PageCurrent,0)<1 SET @PageCurrent=1
IF ISNULL(@PageSize,0)<1 SET @PageSize=15
--设置页数
IF @PageCount IS NULL
BEGIN
--SET @sql=N'SELECT @PageCount=COUNT(*) from containstable(T_List,(t_listepname),'' ISABOUT ('+@str+')'') c'
SET @sql=N'SELECT @PageCount=COUNT(*) from T_List where contains((t_listepname,T_ListEPAdress),''ISABOUT ('+@str+')'')'+@conditions
EXEC sp_executesql @sql,N'@PageCount int OUTPUT',@PageCount OUTPUT
SET @PageCount=(@PageCount+@PageSize-1)/@PageSize
--print @sql
END
DECLARE @TopN varchar(20),@TopN1 varchar(20)
SELECT @TopN=@PageSize,@TopN1=@PageCurrent*@PageSize
--第一页直接显示
IF @PageCurrent=1
BEGIN
print N'select a.* from T_List a inner join (select top '+@TopN+N' * from containstable(T_List,(t_listepname,T_ListEPAdress),'' ISABOUT ('+ @str+N')'') c ORDER BY C.RANK DESC,C.[key]) b on a.id=b.[key] '+@conditions+' order by b.rank desc,b.[key]'
EXEC(N'select a.* from T_List a inner join (select top '+@TopN+N' * from containstable(T_List,(t_listepname,T_ListEPAdress),'' ISABOUT ('+ @str+N')'') c ORDER BY C.RANK DESC,C.[key]) b on a.id=b.[key] '+@conditions+' order by b.rank desc,b.[key]')
END
ELSE
BEGIN
SELECT @PageCurrent=@TopN1,
@sql=N'SELECT @n=@n-1,@s=CASE WHEN @n<'+@TopN
+N' THEN @s+N'',''+QUOTENAME(RTRIM(CAST(c.[key]'
+N' as varchar(8000))),N'''''''') ELSE N'''' END FROM T_List a inner join '
+N' containstable(T_List,(t_listepname,T_ListEPAdress),'' ISABOUT ('+ @str
+N')'') c on a.id=c.[key] '+@conditions
+N' order by c.rank desc,c.[key]'
SET ROWCOUNT @PageCurrent
EXEC sp_executesql @sql,
N'@n int,@s nvarchar(4000) OUTPUT',
@PageCurrent,@sql OUTPUT
SET ROWCOUNT 0
IF @sql=N''
EXEC(N'SELECT TOP 0'
+N' *'
+N' FROM T_List')
ELSE
BEGIN
SET @sql=STUFF(@sql,1,1,N'')
--执行查询
EXEC(N'SELECT TOP '+@TopN
+N' a.* '
+N' FROM '
+N' T_List a inner join (select [key],rank from containstable(T_List,(t_listepname,T_ListEPAdress),'' ISABOUT ('+ @str
+N')'') c) b on a.id=b.[key] and b.[key] IN('+@sql
+N') '
+N' order by b.rank desc,b.[key]'
)
END
SET NOCOUNT Off
END
分词前台程序:
一、index.asp
<!--#include file="conn.asp"-->
<HTML>
<HEAD>
<TITLE>电话号码列表</TITLE>
<META http-equiv=Content-Type content="text/html; charset=gb2312">
<META content="MSHTML 6.00.2900.2802" name=GENERATOR>
<style type="text/css">
<!--
.xiate {
font-family: "宋体";
font-size: 12px;
color: #FFFFFF;
text-decoration: none;
border-bottom-width: 1px;
border-bottom-style: solid;
border-bottom-color: #0099CC;
}
.xuxian {
border: 1px dotted #006699;
font-family: "宋体";
font-size: 12px;
color: #006699;
text-decoration: none;
}
.te {
font-family: "宋体";
font-size: 12px;
color: #666666;
text-decoration: none;
}
.quantable {
border: 1px solid #006699;
}
.TXT {
FONT-SIZE: 12px; COLOR: #000000}
.kk {
BORDER-RIGHT: #1b9fd0 1px solid; BORDER-TOP: #1b9fd0 1px solid; FONT-SIZE: 12px; BORDER-LEFT: #1b9fd0 1px solid; CURSOR: hand; COLOR: #000000; BORDER-BOTTOM: #1b9fd0 1px solid; BACKGROUND-COLOR: #eaf8fd
}
.te1 {
font-family: "方正粗宋简体";
font-size: 18px;
color: #FF6600;
text-decoration: none;
font-weight: normal;
font-style: normal;
}
-->
</style>
<script language="JavaScript" type="text/JavaScript">
<!--
function MM_preloadImages() { //v3.0
var d=document; if(d.images){ if(!d.MM_p) d.MM_p=new Array();
var i,j=d.MM_p.length,a=MM_preloadImages.arguments; for(i=0; i<a.length; i++)
if (a[i].indexOf("#")!=0){ d.MM_p[j]=new Image; d.MM_p[j++].src=a[i];}}
}
function chkkeyword()
{
if (document.form1.keyword.value=="")
{
alert("请输入您要搜索的内容！");
document.form1.keyword.focus();
return;
}
document.form1.submit();
}
//-->
</script>
<%
dim curpage '当前页
curpage = SafeRequest("page",1)
indexno =SafeRequest("indexno",1)
keyword = SafeRequest("keyword",0)
paths=Server.mappath("/")
PageSize=15
if (curpage = "" or isnull(curpage)) then
curpage = 1
end If
if (indexno = "" or isnull(indexno)) then
s1=" and 1=1"
Else
s1=" and t_listindexno = "&indexno
end If
if keyword <> "" Then
'无条件时对应以后的扩展功能可以加入
'首先进行分词:
dim obj
dim SearchChar
dim sptstr
SearchChar=trim(replace(replace(keyword,"(",""),"'",""))
SearchChar=trim(replace(SearchChar,")",""))
SearchChar=trim(replace(SearchChar,",",""))
SearchChar=trim(replace(SearchChar,"&",""))
SearchChar=trim(replace(SearchChar,"=",""))
SearchChar=trim(replace(SearchChar,"|",""))
Set obj = Server.CreateObject("CSW.SplitWord")
sptstr= obj.split(cstr(SearchChar),cint("0"),paths) // 在此处修改您CSW组件所在的路径
set obj=nothing
Dim MyArray
Dim RepArray
RepArray=Split(sptstr, " ", -1, 1)
MyArray = Split(sptstr, " ", -1, 1)
For i = 0 To UBound (MyArray)
if trim(MyArray(i))<>"" then
MyArray(i)=MyArray(i)+ " weight(."&cstr(UBound (MyArray)-i)&"),"
end if
Next
resultvalue=join (MyArray)
rult=Replace(sptstr," ","")&" weight(."&cstr(UBound (MyArray))&"),"&left(trim(resultvalue),len(trim(resultvalue))-1)
'这些值在 VB 中是预定义常量，可以直接调用，但在 VBScript 中没有预定义
adCmdSPStoredProc = 4
adParamReturnValue = 4
adParaminput = 1
adParamOutput = 2
adInteger = 3
adVarChar = 200
''建一个command对象
set CmdSP = Server.CreateObject("ADODB.Command")
''建立连结
CmdSP.ActiveConnection =conn' "Driver={SQL Server};server=(local);Uid=sa;Pwd=;Database=Pubs"
''定义command 对象调用名称
ctext = "PRO_PageView"
CmdSP.CommandText=ctext
''设置command调用类型是存储过程 (adCmdSPStoredProc = 4)
CmdSP.CommandType = adCmdSPStoredProc
'要求将SQL命令先行编译
CmdSP.Prepared = true
''往command 对象中加参数
''定义存储过程有直接返回值，并且是个整数，省缺值是4
'CmdSP.Parameters.Append CmdSP.CreateParameter("RETURN_VALUE", adInteger, adParamReturnValue, 4)
''定义一个整型输入参数
'CmdSP.Parameters.Refresh
CmdSP.Parameters.Append CmdSP.CreateParameter("@PageCurrent", adInteger, adParamInput,,curpage)
CmdSP.Parameters.Append CmdSP.CreateParameter("@PageSize", adInteger, adParamInput,,PageSize)
''定义一个字符型输入参数
CmdSP.Parameters.Append CmdSP.CreateParameter("@rult",adVarChar,adParaminput,4000,rult)
CmdSP.Parameters.Append CmdSP.CreateParameter("@s1",adVarChar,adParaminput,200,s1)
''定义一个整型输出参数
CmdSP.Parameters.Append CmdSP.CreateParameter("@PageCount",adInteger,adParamOutput)
''运行存储过程，并得到返回记录集
Set adoRS = CmdSP.Execute
end if
%>
</HEAD>
<BODY bgcolor="#DBE0E4" leftmargin="0" topmargin="0" marginwidth="0" marginheight="0">
<form method="post" action="indext.asp" name="form1">
<div align=center>
<table width="780" border="0" cellpadding="0" cellspacing="0">
<tr>
<td width="23%" height="328" align="center" valign="top">
<table width="95%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td height="15"></td>
</tr>
<tr>
<td width="15%" height="25" class="te1">
<div align="right" class="te">
<div align="center"><strong><font size="2">输入任意条件:</font></strong>
</div>
</div> </td>
<td width="26%" height="25" align="center" valign="middle">
<div align="center">
<INPUT name="keyword" class=main_kuang id="keyword" value="<%=keyword%>">
<inupt name ="sect" value ="<%=rult%>">
</div></td>
<td width="59%" valign="bottom" align=left><img src="images/001A.gif" width="65" height="19" onClick="javascript:chkkeyword()" style="cursor:hand">
</td>
<td width="26%" height="25" align="center" valign="middle">
<div align="center">
<INPUT type = "hidden" name="indexno" class=main_kuang id="indexno" value=<%=indexno%>>
</div></td>
</tr>
</table>
<% if keyword <> "" Then %>
<!------------------------------------------------------------------------>
<table width="95%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td align="left" valign="top" class="te">
<TABLE borderColor=#7495c0 cellSpacing=0
cellPadding=3 width="99%" align=center
border=1>
<TBODY>
<TR align="center" borderColor=#ffffff bgColor=#e7eff5 class=TXT valign="top" height="30">
<TD width="30%" height=30> <DIV align=center class="te">公司名称</DIV></TD>
<!-- <TD width="14%" height=30> <DIV class="te">部门名称</DIV></TD> -->
<TD width="15%" class="te">电话</TD>
<TD width="42%" class="te">地址</TD>
<TD width="13%" class="te">操作</TD>
</TR>
<%
if adoRS.eof and adoRS.bof then
response.write "<tr><td colspan=4>没有查询到任何相关记录</td></tr>"
else
dim idarr(15),unitarr(15),addressarr(15),regdatearr(15),idarr1(15)
for i=1 to PageSize
idarr(i)=adoRS("t_listepname")
idarr1(i)=adoRS("t_listepname")
unitarr(i)=adoRS("t_listepphone")
addressarr(i)=adoRS("t_listepadress")
adoRS.movenext
if adoRS.eof then
i = i + 1
exit for
End If
next
adoRS.close()
set adoRS=nothing
For j = 0 To UBound (RepArray)-1
if trim(RepArray(j))<>"" then
for i=1 to UBound(idarr)
idarr(i)=Replace(idarr(i),trim(RepArray(j)),"<font color='#FF0000'>"&trim(RepArray(j))&"</font>")
unitarr(i)=Replace(unitarr(i),trim(RepArray(j)),"<font color='#FF0000'>"&trim(RepArray(j))&"</font>")
addressarr(i)=Replace(addressarr(i),trim(RepArray(j)),"<font color='#FF0000'>"&trim(RepArray(j))&"</font>")
next
end if
Next
for i=1 to UBound(idarr)
if (idarr(i) = "" or isnull(idarr(i))) Then
else
%>
<TR class=TXT borderColor=#ffffff bgColor=#e7eff5 valign="top" height="30">
<TD height=30><%=idarr(i)%> </TD>
<TD><div align="center"><a href="call:<%=unitarr(i)%>"><%=unitarr(i)%></a> </TD>
<TD><%=addressarr(i)%> </TD>
<TD><div align="center">
<a href="save:<%=idarr1(i)%>,<%=unitarr(i)%>">加入通讯录</a> </TD>
</TR>
<%
end if
next
totalrowcount = CmdSP.Parameters("@PageCount").value
Set CmdSP.ActiveConnection = nothing
Set CmdSP = nothing
%>
<tr valign="bottom" align="center">
<td height="10" colspan="4"></td></tr>
<tr align="center">
<td colspan="4" class=TXT>
<% = ExportPageInfo(rs, curpage, i, "indext.asp?indexno="&indexno&"&keyword="&keyword&"&",totalrowcount) %>
</td>
</tr>
</table></td>
</tr>
<% End If %>
</table>
<table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td align="center"> </td>
</tr>
</table></td>
</tr>
</table>
</tr>
</table>
</form>
</div>
</BODY>
</HTML>
<%
end if
'rs.close
'set rs=nothing
'conn.close
'set conn=nothing
%>
二、pages.asp
<%
Function ExportPageInfo(ByRef rs,curpage,i,LinkFile,rowcount)
Dim retval, j, pageNumber, BasePage,pagecount1
retval ="总计" & rowcount & "页 "
'If rowcount Mod 15 <> 0 Then
'abc = Int(rowcount / 15) + 1
'Else
'abc = rowcount / 15
'End If
retval = retval & " 第"&curpage&"页 共"&rowcount&"页"
If curpage = 1 Then
retval = retval & " 首页 前页 "
Else
retval = retval & " <a href='" &LinkFile& "page=1'>首页</a> <a href='" &LinkFile& "page=" &cstr(curpage - 1)& "'>前页</a> "
End If
If curpage = rowcount Then
retval = retval & "后页 末页"
Else
retval = retval & "<a href='" &LinkFile& "page=" &cstr(curpage + 1)& "'>后页</a> <a href='" &LinkFile& "page=" & CStr(rowcount) &"'>末页</a>"
End if
retval = retval & "<br>"
ExportPageInfo = retval
End Function
%>
三、conn.asp
<%@ LANGUAGE = VBScript CodePage = 936%>
<%
'Option Explicit
Response.Buffer = True
on error resume next'tangwf2006-11-08 屏闭
Dim ConnStr
Set conn = Server.CreateObject("ADODB.Connection")
conn.open "driver={SQL Server};server=192.168.0.25;uid=sa;pwd=pwd;database=test"
If Err Then
err.Clear
Set Conn = Nothing
Response.Write "数据库连接出错，请检查连接字串。"'注释，需要把这几个字翻译成英文。
Response.End
End If
%>
<!--#include file="safe.asp"-->
<!--#include file="pages.asp"-->
四、safe.asp
<%
Function SafeRequest(ParaName,ParaType)
'--- 传入参数 ---
'ParaName:参数名称-字符型
'ParaType:参数类型-数字型(1表示以上参数是数字，0表示以上参数为字符)
Dim ParaValue
ParaValue=Request(ParaName)
If ParaType=1 Then
If ParaValue = "" Or IsNull(ParaValue) Then
else
If not isNumeric(ParaValue) then
Response.write "参数" & ParaName & "必须为数字型！"
Response.end
End If
End if
elseIf Instr(LCase(ParaValue),"select ") > 0 or Instr(LCase(ParaValue),"insert ") > 0 or Instr(LCase(ParaValue),"delete from") > 0 or Instr(LCase(ParaValue),"count(") > 0 or Instr(LCase(ParaValue),"drop table") > 0 or Instr(LCase(ParaValue),"update ") > 0 or Instr(LCase(ParaValue),"truncate ") > 0 or Instr(LCase(ParaValue),"asc(") > 0 or Instr(LCase(ParaValue),"mid(") > 0 or Instr(LCase(ParaValue),"char(") > 0 or Instr(LCase(ParaValue),"xp_cmdshell") > 0 or Instr(LCase(ParaValue),"exec master") > 0 or Instr(LCase(ParaValue),"net localgroup administrators") > 0 or Instr(LCase(ParaValue)," and ") > 0 or Instr(LCase(ParaValue),"net user") > 0 or Instr(LCase(ParaValue)," or ") > 0 then
Response.End()
else
ParaValue=trim(ParaValue)
ParaValue=replace(ParaValue,"'","‘")
ParaValue=replace(ParaValue,";","；")
ParaValue=replace(ParaValue,"<","《")
ParaValue=replace(ParaValue,"""","“")
ParaValue=replace(ParaValue,">","》")
End if
SafeRequest=ParaValue
End function
%>
- sqlserver中全文检索与csw(测试版)实现分词查询测试
- 全文检索Sql测试
- 全文检索：分词，索引
- 在sqlserver中建全文检索
- B/S全文检索的测试
- Oracle 全文检索测试案例.txt
- JSP中实现全文检索
- JSP中实现全文检索
- sqlserver 全文检索
- Sqlserver全文检索
- PHP 中文分词及全文检索的实现
- SqlServer 全文索引创建及测试
- 全文检索之中文分词
- sqlserver视图查询效率测试
- Solr与数据库结合实现全文检索
- MS sqlserver全文管理与全文查询结合的示例
- 基于java的全文检索接口lucene2.4测试一
- Sphinx 全文检索引擎的安装和测试
- 是时候了解下CMake了
- 什么是WEB服务器、应用服务器
- 開放原碼架構設計：Glib 小談 (1)
- Groovy Tip 31 Map拾遗
- CSDN
- sqlserver中全文检索与csw(测试版)实现分词查询测试
- SQL SERVER DBCC命令解释
- asp.net为控件动态添加事件
- 莫名其妙 错误 xfire web service
- 面向对象之自动绑定数据源
- C实现的动态字符串操作
- Flex资源
- 我所理解的SOA知识体系
- 破解xp登录口令
