在SQL Server 2005顶用存储历程实现搜索功效[MSSQL防范]

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

   我们来看看功效强盛一点,复杂一点的搜索是若何实现的（在SQL　ＳＥＲＶＥＲ２００／２００５通过存储历程实现搜索算法）.

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

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

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

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

1、对搜索后果举行排序的算法

   在前面提到的LIKE语句最大的问题就是搜索的后果是没有经过排序的,我们不知道后果呈目前的次序是若何的,因为它是随机的.像百度,GOOGLE城市对后果用算法举行排序再显示的.好我们也来成立一个简单的排序法.一个很常见的算法是计算关键词在被搜索内容中呈现的次数,次数最多的排在后果的第一位.我们的是在存储历程中实现这个算法的,而在ＳＱＬSERVER中没有供应计算关键词在被搜索内容中呈现的次数这样的函数,我们要自己写一个UDF（User-Defined Functions）,UDF是SQLSERVER的内部函数,可以被存储历程调用大概被其他UDF调用.函数以下:

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

 

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

 

2、参数传送

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

3、搜索的实现历程

假定我们对Product表举行搜索,Product字段有:Id,Name ,Descripton(产品描写),搜索要同时对Name 和 Description举行.

Any-World Search实现以下:

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

 

这里对Name赋予权重为3,Description为1（大家按照实际情形赋予差别的权重）,Rank是计算列,通过前面定义的UDF计算所关键词呈现的次数乘上权重等到的.

一样的All-Word Search实现以下:

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

    这时把每个关键词呈现的次数相乘只要一个没呈现RANK就为0,为0就是搜索后果为空.

还可以这样实现:

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

对没呈现的关键词赋值-1000,这样Rank就必定为负数,负数表示搜索后果为空.

 

 

4、对后果举行分页

搜索的后果大概很多,对后果分页可以提高性能.我在如安在数据层分页以提高性能已经阐明了若何用存储历程举行分页了,这里就不在具体复述了.

历程简单来说就是成立一个暂时表,表中包含行号,读取时按行号来读取数据

5、完好代码

     经过前面的解析,完好代码以下:

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

 

至此一个简单的搜索算法就实现了. 　　以上是“在SQL Server 2005顶用存储历程实现搜索功效[MSSQL防范]”的内容，如果你对以上该文章内容感兴趣，你可以看看七道奇为您推荐以下文章：

在SQL Server中利用SQL语句查询一个存储历程被别的全部的存储历程引用的存储历程名

在SQL Server 2005中成立数据库镜像的步骤及事项

在SQL2005 轻松配置SSIS包

在SQL中删除反复记录(多种办法)

在SQL Server的存储历程中调用Com组件

在SQLServer中保存和输出图片

在SQLSERVER里写了一个Split函数

在SQL Server中快速删除反复记录

在SQL Server中保存和输出图片

如安在SQL Server数据库差别版本中存储数据

<b>在SQL Server中实现循环每一行做一定的操作</b>

在SQL Server数据库中成批导入数据四种办法