萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> SQL Server 教學

SQL Server 2005 存儲程序實作智慧搜尋:三種模式與關鍵字頻率排序演算法

搜尋需求分層

  1. 精確搜尋:使用者輸入視為單一詞彙,不拆分 — WHERE Name LIKE '%keyword%'。
  2. ALL-Word Search (類 Google):以空白分詞,所有詞彙必須出現,順序不拘。
  3. Any-Word Search:以空白分詞,任一詞彙出現即符合。

核心挑戰:結果相關性排序

LIKE 查詢結果順序隨機,無法反映相關度。需自訂演算法:關鍵字在欄位中出現次數越多,排名越前。

步驟一:建立關鍵字計數 UDF


CREATE FUNCTION dbo.WordCount
(
    @Word VARCHAR(15),
    @Phrase VARCHAR(1000)
)
RETURNS SMALLINT
AS
BEGIN
    IF @Word IS NULL OR @Phrase IS NULL RETURN 0;

    DECLARE @BiggerWord VARCHAR(21);
    SELECT @BiggerWord = @Word + 'x';  -- 附加不會出現在資料中的字元

    DECLARE @BiggerPhrase VARCHAR(2000);
    SELECT @BiggerPhrase = REPLACE(@Phrase, @Word, @BiggerWord);

    -- 長度差異即為出現次數
    RETURN LEN(@BiggerPhrase) - LEN(@Phrase);
END

原理:將目標詞彙替換為「詞彙+特殊字元」,長度增加量 = 出現次數 × 1。避免 CHARINDEX 迴圈效能差。

步驟二:分詞邏輯 (T-SQL 字串分割)

將使用者輸入 '關鍵字1 關鍵字2 關鍵字3' 拆解為資料列集合,可使用:

  • 數字表 (Tally Table) + SUBSTRING/CHARINDEX。
  • XML 解析 (CAST('' + REPLACE(@Input, ' ', '') + '' AS XML))。
  • SQL 2016+ STRING_SPLIT (若相容)。

步驟三:整合存儲程序 — 以 ALL-Word 為例


CREATE PROCEDURE dbo.Search_AllWord
    @SearchText NVARCHAR(4000),
    @TopN INT = 50
AS
BEGIN
    SET NOCOUNT ON;

    -- 1. 分詞暫存表
    DECLARE @Words TABLE (Word NVARCHAR(50) PRIMARY KEY);
    -- 此處插入分詞邏輯,假設已填入 @Words

    -- 2. 計算每筆資料的總符合分數
    SELECT TOP (@TopN)
        T.*,
        SUM(dbo.WordCount(W.Word, T.SearchableColumn)) AS RelevanceScore
    FROM TargetTable T
    CROSS JOIN @Words W
    WHERE T.SearchableColumn LIKE '%' + W.Word + '%'  -- 初步篩選
    GROUP BY T.PrimaryKey, T.SearchableColumn, ... (其他需輸出欄位)
    ORDER BY RelevanceScore DESC;
END

效能優化關鍵點

技巧說明
全文檢索大量資料建議改用 Full-Text Search (FTS),內建斷詞器、詞幹分析、排名函數。
索引支援搜尋欄位建立全文索引或 CHARINDEX 無法用索引;LIKE 'word%' 可用一般索引。
暫存表/表值參數分詞結果用表值參數傳入 SP,避免動態 SQL 注入風險。
快取熱門查詢應用層快取熱門關鍵字結果集,減少資料庫壓力。

進階:動態 SQL 實現任意欄位搜尋


DECLARE @SQL NVARCHAR(MAX) = N'
    SELECT TOP (@TopN) * FROM ' + QUOTENAME(@TableName) + '
    WHERE ' + @WhereClause + '
    ORDER BY RelevanceScore DESC';
EXEC sp_executesql @SQL, N'@TopN INT', @TopN = @TopN;

⚠️ 必須嚴格驗證 @TableName、@WhereClause 來源,防止 SQL 注入。

結語

自訂 WordCount UDF + 分詞邏輯 + 聚合排序,即可在 SQL Server 2005 實現類 Google 的相關性排序。生產環境建議:小資料量用此法、大資料量上 Full-Text Search、極高併發加應用層快取。

關鍵字: SQL Server 2005、存儲程序、搜尋功能、關鍵字頻率排序、WordCount UDF、REPLACE 技巧、分詞、Full-Text Search、動態 SQL、sp_executesql

copyright © 萬盛學電腦網 all rights reserved