在SQL Server 2005中用存储过程实现搜索功能

简介:

现在很多网站都提供了站内的搜索功能,有的很简单在SQL语句里加一个条件如:where names like ‘%words%’就可以实现最基本的搜索了。

我们来看看功能强大一点,复杂一点的搜索是如何实现的(在SQL SERVER200/2005通过存储过程实现搜索算法)。

我们把用户的搜索可以分为以下两种:

1.精确搜索,就是把用户输入的各个词语当成一个整体,不分割搜索.

2.像百度,GOOGLE一样的,按空格把输入的每一个词分离,只要包含这些词语,而不管出现的顺序,称为ALL-Word Search.

3.对输入的词只要有一个出现就为匹配 称为Any-Word Search


一、对搜索结果进行排序的算法

在前面提到的LIKE语句最大的问题就是搜索的结果是没有经过排序的,我们不知道结果出现在的顺序是如何的,因为它是随机的。像百度,GOOGLE都会对结果用算法进行排序再显示的.好我们也来建立一个简单的排序法。一个很常见的算法是计算关键词在被搜索内容中出现的次数,次数最多的排在结果的第一位。我们的是在存储过程中实现这个算法的,而在SQLSERVER中没有提供计算关键词在被搜索内容中出现的次数这样的函数,我们要自己写一个UDFUser-Defined Functions,UDFSQLSERVER的内部函数,可以被存储过程调用或者被其他UDF调用。函数如下:

1 None.gif CREATE FUNCTION dbo.WordCount
2 None.gif
3 None.gif( @Word VARCHAR( 15),
4 None.gif
5 None.gif @Phrase VARCHAR( 1000))
6 None.gif
7 None.gif RETURNS SMALLINT
8 None.gif
9 None.gif AS
10 None.gif
11 None.gif BEGIN
12 None.gif
13 ExpandedBlockStart.gif ContractedBlock.gif /**/ /* 如果@Word 或者@Phrase 为空返回 0 */
14 None.gif
15 None.gif IF @Word IS NULL OR @Phrase IS NULL RETURN 0
16 None.gif
17 ExpandedBlockStart.gif ContractedBlock.gif /**/ /* @BiggerWord 比@Word长一个字符 */
18 None.gif
19 None.gif DECLARE @BiggerWord VARCHAR( 21)
20 None.gif
21 None.gif SELECT @BiggerWord = @Word + ' x '
22 None.gif
23 ExpandedBlockStart.gif ContractedBlock.gif /**/ /*在 @Phrase用@BiggerWord替换@Word */
24 None.gif
25 None.gif DECLARE @BiggerPhrase VARCHAR( 2000)
26 None.gif
27 None.gif SELECT @BiggerPhrase = REPLACE ( @Phrase, @Word, @BiggerWord)
28 None.gif
29 ExpandedBlockStart.gif ContractedBlock.gif /**/ /* 相减结果就是出现的次数了 */
30 None.gif
31 None.gif RETURN LEN( @BiggerPhrase) - LEN( @Phrase)
32 None.gif
33 None.gif END
34 None.gif

以上就是整个UDF,它用了一个很高效的方法来计算关键词出现的次数。

二、参数传递

用户输入的关键词从一个到多个不等,我们可以把参数固定为@word1~@word5,这样比较方面实现。当用户输入超过5个时,忽略不计,少于5个的地方视为空。其实GOOGLE也是这样做的,只是GOOGLE的最大词语限制是10个。

三、搜索的实现过程

假定我们对Product表进行搜索,Product字段有:Id,Name ,Descripton(产品描述),搜索要同时对Name Description进行。

Any-World Search实现如下:

1 None.gif SELECT Product.Name,
2 None.gif 3 * WordCount( @Word1, Name) + WordCount( @Word1, Description) +
3 None.gif
4 None.gif 3 * WordCount( @Word2, Name) + WordCount( @Word2, Description) +
5 None.gif
6 None.gif dot.gif
7 None.gif
8 None.gif AS Rank
9 None.gif FROM Product
10 None.gif
11 None.gif

这里对Name赋予权重为3Description1(大家根据实际情况赋予不同的权重),Rank是计算列,通过前面定义的UDF计算所关键词出现的次数乘上权重等到的。


同样的
All-Word Search实现如下:

1 None.gif SELECT Product.Name,
2 None.gif
3 None.gif ( 3 * WordCount( @Word1, Name) + WordCount( @Word1, Description)) *
4 None.gif
5 None.gif CASE
6 None.gif
7 None.gif WHEN @Word2 IS NULL THEN 1
8 None.gif
9 None.gif ELSE 3 * WordCount( @Word2, Name) + WordCount( @Word2, Description)
10 None.gif
11 None.gif END *
12 None.gif
13 None.gif dot.gif
14 None.gif
15 None.gif AS Rank
16 None.gif
17 None.gif FROM Product
18 None.gif


这时把每个关键词出现的次数相乘只要一个没出现

RANK 就为 0 ,为 0 就是搜索结果为空。

还可以这样实现:


1 None.gif SELECT Product.Name,
2 None.gif CASE
3 None.gif WHEN @Word1 IS NULL THEN 0
4 None.gif ELSE ISNULL( NULLIF(dbo.WordCount( @Word1, Name + ' ' + Description), 0), - 1000)
5 None.gif END +
6 None.gif CASE
7 None.gif WHEN @Word2 IS NULL THEN 0
8 None.gif ELSE ISNULL( NULLIF(dbo.WordCount( @Word2, Name + ' ' + Description), 0), - 1000)
9 None.gif END +
10 None.gif dot.gif
11 None.gif AS Rank
12 None.gif FROM Product

对没出现的关键词赋值-1000,这样Rank就肯定为负数,负数表示搜索结果为空。

四、对结果进行分页

搜索的结果可能很多,对结果分页可以提高性能。我在如何在数据层分页以提高性能已经说明了如何用存储过程进行分页了,这里就不在详细复述了。

过程简单来说就是创建一个临时表,表中包含行号,读取时按行号来读取数据


五、完整代码

经过前面的分析,完整代码如下:

1 None.gif CREATE PROCEDURE SearchCatalog
2 None.gif(
3 None.gif @PageNumber TINYINT,
4 None.gif @ProductsPerPage TINYINT,
5 None.gif @HowManyResults SMALLINT OUTPUT,
6 None.gif @AllWords BIT,
7 None.gif @Word1 VARCHAR( 15) = NULL,
8 None.gif @Word2 VARCHAR( 15) = NULL,
9 None.gif @Word3 VARCHAR( 15) = NULL,
10 None.gif @Word4 VARCHAR( 15) = NULL,
11 None.gif @Word5 VARCHAR( 15) = NULL)
12 None.gif AS
13 ExpandedBlockStart.gif ContractedBlock.gif /**/ /* 创建临时表,保存搜索的结果(Sql Server2005适用,Sql Server2000见如何在数据层分页以提高性能) */
14 None.gif DECLARE @Products TABLE
15 None.gif(RowNumber SMALLINT IDENTITY ( 1, 1) NOT NULL,
16 None.gif ID INT,
17 None.gif Name VARCHAR( 50),
18 None.gif Description VARCHAR( 1000),
19 None.gifRank INT)
20 None.gif
21 ExpandedBlockStart.gif ContractedBlock.gif /**/ /* Any-words search */
22 None.gif IF @AllWords = 0
23 None.gif INSERT INTO @Products
24 None.gif SELECT ID, Name, Description,
25 None.gif 3 * dbo.WordCount( @Word1, Name) + dbo.WordCount( @Word1, Description) +
26 None.gif
27 None.gif 3 * dbo.WordCount( @Word2, Name) + dbo.WordCount( @Word2, Description) +
28 None.gif
29 None.gif 3 * dbo.WordCount( @Word3, Name) + dbo.WordCount( @Word3, Description) +
30 None.gif
31 None.gif 3 * dbo.WordCount( @Word4, Name) + dbo.WordCount( @Word4, Description) +
32 None.gif
33 None.gif 3 * dbo.WordCount( @Word5, Name) + dbo.WordCount( @Word5, Description)
34 None.gif
35 None.gif AS Rank
36 None.gif
37 None.gif FROM Product
38 None.gif ORDER BY Rank DESC
39 None.gif
40 ExpandedBlockStart.gif ContractedBlock.gif /**/ /* all-words search */
41 None.gif
42 None.gif IF @AllWords = 1
43 None.gif
44 None.gif INSERT INTO @Products
45 None.gif
46 None.gif SELECT ID, Name, Description,
47 None.gif
48 None.gif ( 3 * dbo.WordCount( @Word1, Name) + dbo.WordCount
49 None.gif
50 None.gif( @Word1, Description)) *
51 None.gif
52 None.gif CASE
53 None.gif
54 None.gif WHEN @Word2 IS NULL THEN 1
55 None.gif
56 None.gif ELSE 3 * dbo.WordCount( @Word2, Name) + dbo.WordCount( @Word2,
57 None.gif
58 None.gifDescription)
59 None.gif
60 None.gif END *
61 None.gif
62 None.gif CASE
63 None.gif
64 None.gif WHEN @Word3 IS NULL THEN 1
65 None.gif
66 None.gif ELSE 3 * dbo.WordCount( @Word3, Name) + dbo.WordCount( @Word3,
67 None.gif
68 None.gifDescription)
69 None.gif
70 None.gif END *
71 None.gif
72 None.gif CASE
73 None.gif
74 None.gif WHEN @Word4 IS NULL THEN 1
75 None.gif
76 None.gif ELSE 3 * dbo.WordCount( @Word4, Name) + dbo.WordCount( @Word4,
77 None.gif
78 None.gifDescription)
79 None.gif
80 None.gif END *
81 None.gif
82 None.gif CASE
83 None.gif
84 None.gif WHEN @Word5 IS NULL THEN 1
85 None.gif
86 None.gif ELSE 3 * dbo.WordCount( @Word5, Name) + dbo.WordCount( @Word5,
87 None.gif
88 None.gifDescription)
89 None.gif
90 None.gif END
91 None.gif
92 None.gif AS Rank
93 None.gif
94 None.gif FROM Product
95 None.gif
96 None.gif ORDER BY Rank DESC
97 None.gif
98 ExpandedBlockStart.gif ContractedBlock.gif /**/ /* 在外部变量保存搜索结果数 */
99 None.gif
100 None.gif SELECT @HowManyResults = COUNT( *)
101 None.gif
102 None.gif FROM @Products
103 None.gif
104 None.gif WHERE Rank > 0
105 None.gif
106 ExpandedBlockStart.gif ContractedBlock.gif /**/ /* 按页返回结果*/
107 None.gif
108 None.gif SELECT ProductID, Name, Description, Price, Image1FileName,
109 None.gif
110 None.gif Image2FileName, Rank
111 None.gif
112 None.gif FROM @Products
113 None.gif
114 None.gif WHERE Rank > 0
115 None.gif
116 None.gif AND RowNumber BETWEEN ( @PageNumber - 1) * @ProductsPerPage + 1
117 None.gif
118 None.gif AND @PageNumber * @ProductsPerPage
119 None.gif ORDER BY Rank DESC



本文转自高海东博客园博客,原文链接:http://www.cnblogs.com/ghd258/archive/2006/11/17/563218.html,如需转载请自行联系原作者
相关实践学习
使用SQL语句管理索引
本次实验主要介绍如何在RDS-SQLServer数据库中,使用SQL语句管理索引。
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库,该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品:云数据库RDS SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能,强力支撑各种企业应用,同时也包含了微软的License费用,减少额外支出。 了解产品详情: https://www.aliyun.com/product/rds/sqlserver
相关文章
|
28天前
|
SQL 算法 数据库
【数据库SQL server】关系数据库标准语言SQL之数据查询
【数据库SQL server】关系数据库标准语言SQL之数据查询
57 0
|
28天前
|
SQL 算法 数据库
【数据库SQL server】关系数据库标准语言SQL之视图
【数据库SQL server】关系数据库标准语言SQL之视图
54 0
|
28天前
|
SQL 人工智能 算法
【数据库SQL server】传统运算符与专门运算符
【数据库SQL server】传统运算符与专门运算符
33 0
|
28天前
|
SQL 数据库 数据安全/隐私保护
【操作宝典】SQL巨擘:掌握SQL Server Management的终极秘籍!
【操作宝典】SQL巨擘:掌握SQL Server Management的终极秘籍!
48 0
|
2月前
|
SQL 流计算
Flink CDC 1.12版本引入了对SQL Server的支持
【1月更文挑战第26天】【1月更文挑战第124篇】Flink CDC 1.12版本引入了对SQL Server的支持
24 1
|
6天前
|
SQL 存储 数据安全/隐私保护
|
8天前
|
SQL 存储
SQL Server基本函数
SQL Server基本函数
|
11天前
|
SQL 测试技术 数据处理
百度搜索:蓝易云【软件测试|SQL中的null值,该如何理解?】
总之,NULL值在SQL中用于表示缺少值或未知值,需要特别注意在查询和条件判断中的处理,以确保结果的正确性。正确理解和处理NULL值可以提高数据库查询和数据处理的准确性和可靠性。 买CN2云服务器,免备案服务器,高防服务器,就选蓝易云。百度搜索:蓝易云
14 1
|
28天前
|
SQL 存储 数据库
【数据库SQL server】自学终极笔记
【数据库SQL server】自学终极笔记
75 0
|
28天前
|
SQL 算法 数据库
【数据库SQL server】关系数据库标准语言SQL之数据更新
【数据库SQL server】关系数据库标准语言SQL之数据更新
22 0