現(xiàn)在很多網(wǎng)站都提供了站內(nèi)的搜索功能，有的很簡(jiǎn)單在SQL語(yǔ)句里加一個(gè)條件如：where names like ‘%words%’就可以實(shí)現(xiàn)最基本的搜索了。

我們來(lái)看看功能強(qiáng)大一點(diǎn)，復(fù)雜一點(diǎn)的搜索是如何實(shí)現(xiàn)的（在SQL　ＳＥＲＶＥＲ２００／２００５通過(guò)存儲(chǔ)過(guò)程實(shí)現(xiàn)搜索算法）。

我們把用戶的搜索可以分為以下兩種：

1.精確搜索,就是把用戶輸入的各個(gè)詞語(yǔ)當(dāng)成一個(gè)整體,不分割搜索.

2.像百度,GOOGLE一樣的,按空格把輸入的每一個(gè)詞分離,只要包含這些詞語(yǔ),而不管出現(xiàn)的順序，稱為ALL-Word Search.

3.對(duì)輸入的詞只要有一個(gè)出現(xiàn)就為匹配 稱為Any-Word Search

一、對(duì)搜索結(jié)果進(jìn)行排序的算法

在前面提到的LIKE語(yǔ)句最大的問(wèn)題就是搜索的結(jié)果是沒(méi)有經(jīng)過(guò)排序的,我們不知道結(jié)果出現(xiàn)在的順序是如何的,因?yàn)樗请S機(jī)的。像百度,GOOGLE都會(huì)對(duì)結(jié)果用算法進(jìn)行排序再顯示的.好我們也來(lái)建立一個(gè)簡(jiǎn)單的排序法。一個(gè)很常見(jiàn)的算法是計(jì)算關(guān)鍵詞在被搜索內(nèi)容中出現(xiàn)的次數(shù),次數(shù)最多的排在結(jié)果的第一位。我們的是在存儲(chǔ)過(guò)程中實(shí)現(xiàn)這個(gè)算法的，而在ＳＱＬSERVER中沒(méi)有提供計(jì)算關(guān)鍵詞在被搜索內(nèi)容中出現(xiàn)的次數(shù)這樣的函數(shù)，我們要自己寫一個(gè)UDF（User-Defined Functions）,UDF是SQLSERVER的內(nèi)部函數(shù)，可以被存儲(chǔ)過(guò)程調(diào)用或者被其他UDF調(diào)用。函數(shù)如下：

1CREATE FUNCTION dbo.WordCount23(@Word VARCHAR(15), 45@Phrase VARCHAR(1000))67RETURNS SMALLINT89AS1011BEGIN1213/**//* 如果@Word 或者@Phrase 為空返回 0 */1415IF @Word IS NULL OR @Phrase IS NULL RETURN 01617/**//* @BiggerWord 比@Word長(zhǎng)一個(gè)字符 */1819DECLARE @BiggerWord VARCHAR(21)2021SELECT @BiggerWord = @Word + 'x'2223/**//*在 @Phrase用@BiggerWord替換@Word */2425DECLARE @BiggerPhrase VARCHAR(2000)2627SELECT @BiggerPhrase = REPLACE (@Phrase, @Word, @BiggerWord)2829/**//* 相減結(jié)果就是出現(xiàn)的次數(shù)了 */3031RETURN LEN(@BiggerPhrase) - LEN(@Phrase)3233END34

以上就是整個(gè)UDF，它用了一個(gè)很高效的方法來(lái)計(jì)算關(guān)鍵詞出現(xiàn)的次數(shù)。

二、參數(shù)傳遞

用戶輸入的關(guān)鍵詞從一個(gè)到多個(gè)不等，我們可以把參數(shù)固定為@word1~@word5，這樣比較方面實(shí)現(xiàn)。當(dāng)用戶輸入超過(guò)5個(gè)時(shí)，忽略不計(jì)，少于5個(gè)的地方視為空。其實(shí)GOOGLE也是這樣做的，只是GOOGLE的最大詞語(yǔ)限制是10個(gè)。

三、搜索的實(shí)現(xiàn)過(guò)程

假定我們對(duì)Product表進(jìn)行搜索，Product字段有：Id,Name ,Descripton(產(chǎn)品描述)，搜索要同時(shí)對(duì)Name 和 Description進(jìn)行。

Any-World Search實(shí)現(xiàn)如下：

1SELECT Product.Name, 2;;;;3 * WordCount(@Word1, Name) + WordCount(@Word1, Description) +34;;;;3 * WordCount(@Word2, Name) + WordCount(@Word2, Description) +56;;;;78;;;;AS Rank9FROM Product1011

這里對(duì)Name賦予權(quán)重為3，Description為1（大家根據(jù)實(shí)際情況賦予不同的權(quán)重），Rank是計(jì)算列，通過(guò)前面定義的UDF計(jì)算所關(guān)鍵詞出現(xiàn)的次數(shù)乘上權(quán)重等到的。

同樣的All-Word Search實(shí)現(xiàn)如下：

1SELECT Product.Name, 23;;;;(3 * WordCount(@Word1, Name) + WordCount(@Word1, Description)) *45;;;;CASE 67; WHEN @Word2 IS NULL THEN 1 89;ELSE 3 * WordCount(@Word2, Name) + WordCount(@Word2, Description)1011;;;;END *1213;;;;1415;;;;AS Rank1617FROM Product18

這時(shí)把每個(gè)關(guān)鍵詞出現(xiàn)的次數(shù)相乘只要一個(gè)沒(méi)出現(xiàn)RANK就為0，為0就是搜索結(jié)果為空。

還可以這樣實(shí)現(xiàn)：

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 Rank12FROM Product對(duì)沒(méi)出現(xiàn)的關(guān)鍵詞賦值-1000，這樣Rank就肯定為負(fù)數(shù)，負(fù)數(shù)表示搜索結(jié)果為空。

四、對(duì)結(jié)果進(jìn)行分頁(yè)

搜索的結(jié)果可能很多，對(duì)結(jié)果分頁(yè)可以提高性能。我在如何在數(shù)據(jù)層分頁(yè)以提高性能已經(jīng)說(shuō)明了如何用存儲(chǔ)過(guò)程進(jìn)行分頁(yè)了，這里就不在詳細(xì)復(fù)述了。

過(guò)程簡(jiǎn)單來(lái)說(shuō)就是創(chuàng)建一個(gè)臨時(shí)表，表中包含行號(hào)，讀取時(shí)按行號(hào)來(lái)讀取數(shù)據(jù)

五、完整代碼

經(jīng)過(guò)前面的分析，完整代碼如下：

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)12AS13/**//* 創(chuàng)建臨時(shí)表，保存搜索的結(jié)果（Sql Server2005適用，Sql Server2000見(jiàn)如何在數(shù)據(jù)層分頁(yè)以提高性能） */14DECLARE @Products TABLE15(RowNumber SMALLINT IDENTITY (1,1) NOT NULL,16 ID INT,17 Name VARCHAR(50),18 Description VARCHAR(1000),19Rank INT)2021/**//* Any-words search */22IF @AllWords = 0 23INSERT INTO @Products24SELECT ID, Name, Description,25;;;;;3 * dbo.WordCount(@Word1, Name) + dbo.WordCount(@Word1, Description) +2627;;;;;3 * dbo.WordCount(@Word2, Name) + dbo.WordCount(@Word2, Description) +2829;;;;;3 * dbo.WordCount(@Word3, Name) + dbo.WordCount(@Word3, Description) +3031;;;;;3 * dbo.WordCount(@Word4, Name) + dbo.WordCount(@Word4, Description) +3233;;;;;3 * dbo.WordCount(@Word5, Name) + dbo.WordCount(@Word5, Description) 3435; AS Rank3637FROM Product38ORDER BY Rank DESC3940/**//* all-words search */4142IF @AllWords = 14344INSERT INTO @Products4546SELECT ID, Name, Description,4748; (3 * dbo.WordCount(@Word1, Name) + dbo.WordCount4950(@Word1, Description)) *5152; CASE 5354WHEN @Word2 IS NULL THEN 1 5556ELSE 3 * dbo.WordCount(@Word2, Name) + dbo.WordCount(@Word2, 5758Description)5960; END *6162; CASE 6364WHEN @Word3 IS NULL THEN 1 6566ELSE 3 * dbo.WordCount(@Word3, Name) + dbo.WordCount(@Word3, 6768Description)6970; END *7172; CASE 7374WHEN @Word4 IS NULL THEN 1 7576ELSE 3 * dbo.WordCount(@Word4, Name) + dbo.WordCount(@Word4, 7778Description)7980; END *8182; CASE 8384WHEN @Word5 IS NULL THEN 1 8586ELSE 3 * dbo.WordCount(@Word5, Name) + dbo.WordCount(@Word5, 8788Description)8990; END9192; AS Rank9394FROM Product9596ORDER BY Rank DESC9798/**//* 在外部變量保存搜索結(jié)果數(shù) */99100SELECT @HowManyResults = COUNT(*) 101102FROM @Products 103104WHERE Rank > 0105106/**//* 按頁(yè)返回結(jié)果*/107108SELECT ProductID, Name, Description, Price, Image1FileName,109110 Image2FileName, Rank111112FROM @Products113114WHERE Rank > 0115116; AND RowNumber BETWEEN (@PageNumber-1) * @ProductsPerPage + 1 117118;AND @PageNumber * @ProductsPerPage119ORDER BY Rank DESC

至此一個(gè)簡(jiǎn)單的搜索算法就實(shí)現(xiàn)了。