WHERE Name LIKE '%keyword%'。LIKE 查詢結果順序隨機,無法反映相關度。需自訂演算法:關鍵字在欄位中出現次數越多,排名越前。
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 迴圈效能差。
將使用者輸入 '關鍵字1 關鍵字2 關鍵字3' 拆解為資料列集合,可使用:
Tally Table) + SUBSTRING/CHARINDEX。CAST('' + REPLACE(@Input, ' ', ' ') + ' ' AS XML))。STRING_SPLIT (若相容)。
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 注入風險。 |
| 快取熱門查詢 | 應用層快取熱門關鍵字結果集,減少資料庫壓力。 |
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