萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 網路程式設計 >> ASP 程式設計

ASP 多關鍵字動態查詢實作:陣列分割與 SQL 組合技巧

需求場景

使用者在搜尋框輸入多個關鍵字 (以空格分隔),需對多個欄位進行模糊匹配,且關鍵字間為 AND 關係 (皆須滿足)。

核心邏輯

  1. 以空格 `Split` 關鍵字字串為陣列
  2. 過濾空白關鍵字
  3. 對每個關鍵字產生一組 `OR` 條件 (多欄位任一匹配)
  4. 各關鍵字條件組用 `AND` 串接 (所有關鍵字皆需滿足)
  5. 動態建構 SQL WHERE 子句

完整實作代碼 (VBScript)

<%
Function BuildMultiKeywordFilter(keywordStr, fieldArray)
    ' keywordStr: "關鍵字1 關鍵字2 ..."
    ' fieldArray: Array("field1", "field2", "field3") 要搜尋的欄位
    Dim keywords, kw, i, j, conditions, cond, field, sqlWhere
    
    If Trim(keywordStr) = "" Then
        BuildMultiKeywordFilter = ""
        Exit Function
    End If
    
    keywords = Split(keywordStr, " ")
    ReDim conditions(-1) ' 動態陣列
    
    For i = 0 To UBound(keywords)
        kw = Trim(keywords(i))
        If kw <> "" Then
            ' 為此關鍵字建立 OR 條件組
            ReDim Preserve conditions(UBound(conditions) + 1)
            cond = ""
            For j = 0 To UBound(fieldArray)
                field = fieldArray(j)
                If cond <> "" Then cond = cond & " OR "
                cond = cond & field & " LIKE '%" & Replace(kw, "'", "''") & "%'"
            Next
            conditions(UBound(conditions)) = "(" & cond & ")"
        End If
    Next
    
    If UBound(conditions) >= 0 Then
        sqlWhere = " AND (" & Join(conditions, " AND ") & ")"
    Else
        sqlWhere = ""
    End If
    
    BuildMultiKeywordFilter = sqlWhere
End Function

' ===== 使用範例 =====
Dim baseSql, searchFields, whereClause, finalSql
baseSql = "SELECT id, title, content, author, category FROM articles WHERE status = 1"
searchFields = Array("title", "content", "author", "category") ' 要搜尋的欄位

whereClause = BuildMultiKeywordFilter(Request.QueryString("q"), searchFields)
finalSql = baseSql & whereClause & " ORDER BY id DESC"

Response.Write "SQL: " & Server.HTMLEncode(finalSql) & "
" ' 輸出範例: ' SELECT id, title, content, author, category FROM articles WHERE status = 1 ' AND ((title LIKE '%asp%' OR content LIKE '%asp%' OR author LIKE '%asp%' OR category LIKE '%asp%') ' AND (title LIKE '%教學%' OR content LIKE '%教學%' OR author LIKE '%教學%' OR category LIKE '%教學%')) ' ORDER BY id DESC %>

進階:參數化版本 (防注入、效能更好)

<%
Function BuildParamMultiKeywordFilter(keywordStr, fieldArray, ByRef cmd)
    Dim keywords, kw, i, j, conditions, cond, field, paramName, paramIdx
    paramIdx = 0
    
    If Trim(keywordStr) = "" Then Exit Function
    
    keywords = Split(keywordStr, " ")
    ReDim conditions(-1)
    
    For i = 0 To UBound(keywords)
        kw = Trim(keywords(i))
        If kw <> "" Then
            ReDim Preserve conditions(UBound(conditions) + 1)
            cond = ""
            For j = 0 To UBound(fieldArray)
                field = fieldArray(j)
                If cond <> "" Then cond = cond & " OR "
                paramName = "@kw" & paramIdx
                paramIdx = paramIdx + 1
                cond = cond & field & " LIKE " & paramName
                cmd.Parameters.Append cmd.CreateParameter(paramName, 200, 1, 255, "%" & kw & "%")
            Next
            conditions(UBound(conditions)) = "(" & cond & ")"
        End If
    Next
    
    If UBound(conditions) >= 0 Then
        BuildParamMultiKeywordFilter = " AND (" & Join(conditions, " AND ") & ")"
    Else
        BuildParamMultiKeywordFilter = ""
    End If
End Function

' 使用
Dim cmd, sql, whereClause
Set cmd = Server.CreateObject("ADODB.Command")
cmd.ActiveConnection = conn
sql = "SELECT * FROM articles WHERE status = 1"
whereClause = BuildParamMultiKeywordFilter(Request("q"), Array("title","content"), cmd)
cmd.CommandText = sql & whereClause & " ORDER BY id DESC"
cmd.CommandType = 1
Set rs = cmd.Execute
%>

效能優化建議

  • 全文檢索引擎:資料量 > 10萬筆建議改用 SQL Server Full-Text Search、Elasticsearch、Lucene.NET
  • 索引策略:對搜尋欄位建立全文索引或一般索引 (LIKE '%kw%' 無法用索引,需前綴 LIKE 'kw%')
  • 關鍵字長度限制:限制單一關鍵字長度 (如 2-20 字)、總關鍵字數 (如最多 10 個)
  • 快取熱門搜尋:Application 變數快取熱門關鍵字結果,減少 DB 壓力
  • 非同步搜尋建議:AJAX 即時提示,降低完整搜尋頻率

安全性檢查清單

  • [ ] 所有關鍵字經 `Replace(kw, "'", "''")` 轉義 (非參數化版)
  • [ ] 限制關鍵字特殊字元 (移除 `%` `_` `[` `]` 等 LIKE 萬用字元,或轉義)
  • [ ] 參數化版本優先 (Command 物件)
  • [ ] 記錄慢查詢 SQL 供優化參考

動態多關鍵字查詢是搜尋功能的核心,掌握陣列分割、條件組合、參數化三大技巧,即可構建靈活安全的搜尋引擎。

copyright © 萬盛學電腦網 all rights reserved