需求分析

使用者輸入「關鍵字1 關鍵字2 關鍵字3」，需在多個欄位中搜尋，**所有關鍵字都必須滿足 (AND)**，但單一關鍵字在**任一欄位符合即可 (OR)**。

邏輯：(kw1 在欄位A OR 欄位B OR ...) AND (kw2 在欄位A OR 欄位B OR ...) ...

核心實作：BuildSearchSQL

<%
Function BuildSearchSQL(baseSQL, keywords, searchFields)
    ' baseSQL: "SELECT * FROM Table WHERE Status=1"
    ' keywords: "關鍵字1 關鍵字2" (空白分隔)
    ' searchFields: Array("Title", "Content", "Author", "Tags")
    ' 回傳: 完整 SQL 字串
    
    Dim kwArr, kw, field, conditions, i, j, fieldCond, allCond
    kwArr = Split(Trim(keywords))
    allCond = ""
    
    For i = 0 To UBound(kwArr)
        kw = Trim(kwArr(i))
        If kw <> "" Then
            fieldCond = ""
            For j = 0 To UBound(searchFields)
                field = searchFields(j)
                ' 參數化佔位符 ?，實際值稍後綁定
                If fieldCond <> "" Then fieldCond = fieldCond & " OR "
                fieldCond = fieldCond & field & " LIKE ?"
            Next
            ' 每個關鍵字的 OR 群組用括號包起來
            If allCond <> "" Then allCond = allCond & " AND "
            allCond = allCond & "(" & fieldCond & ")"
        End If
    Next
    
    If allCond <> "" Then
        ' 判斷 baseSQL 是否已有 WHERE
        If InStr(1, baseSQL, "WHERE", vbTextCompare) > 0 Then
            BuildSearchSQL = baseSQL & " AND " & allCond
        Else
            BuildSearchSQL = baseSQL & " WHERE " & allCond
        End If
    Else
        BuildSearchSQL = baseSQL
    End If
End Function
%>

參數化執行範例

<!--#include file="conn.asp"-->
<%
Dim baseSQL, keywords, fields, finalSQL, cmd, paramVal, kwArr, i, j
baseSQL = "SELECT NewsID, Title, Summary FROM News WHERE IsPub=1"
keywords = Trim(Request("q"))  ' 使用者輸入
fields = Array("Title", "Content", "Author", "Tags")

finalSQL = BuildSearchSQL(baseSQL, keywords, fields)

Set cmd = Server.CreateObject("ADODB.Command")
cmd.ActiveConnection = conn
cmd.CommandText = finalSQL
cmd.CommandType = 1

' 綁定參數：每個關鍵字 × 每個欄位
kwArr = Split(Trim(keywords))
For i = 0 To UBound(kwArr)
    kw = Trim(kwArr(i))
    If kw <> "" Then
        For j = 0 To UBound(fields)
            paramVal = "%" & kw & "%"
            cmd.Parameters.Append cmd.CreateParameter("@p" & i & "_" & j, 200, 1, 255, paramVal)
        Next
    End If
Next

Set rs = cmd.Execute
' ... 輸出結果 ...
%>

進階：支援精確/排除/欄位指定語法

Function ParseAdvancedQuery(input)
    ' 支援: 關鍵字, "精確詞", -排除詞, 欄位:值
    ' 回傳 Dictionary: keywords, phrases, exclude, fieldFilters
    Dim dict, tokens, token, i, pos
    Set dict = Server.CreateObject("Scripting.Dictionary")
    dict.Add "keywords", Array()
    dict.Add "phrases", Array()
    dict.Add "exclude", Array()
    dict.Add "fields", Server.CreateObject("Scripting.Dictionary")
    
    ' 簡易 tokenizer (實際建議用正則)
    tokens = Split(input)
    For i = 0 To UBound(tokens)
        token = tokens(i)
        If Left(token, 1) = "-" Then
            ' 排除
            dict("exclude") = AppendArr(dict("exclude"), Mid(token, 2))
        ElseIf InStr(token, ":") > 0 Then
            ' 欄位指定 title:ASP
            pos = InStr(token, ":")
            dict("fields").Add Left(token, pos-1), Mid(token, pos+1)
        ElseIf InStr(token, """") > 0 Then
            ' 精確詞 (簡易處理)
            dict("phrases") = AppendArr(dict("phrases"), Replace(token, """", ""))
        Else
            dict("keywords") = AppendArr(dict("keywords"), token)
        End If
    Next
    Set ParseAdvancedQuery = dict
End Function

Function AppendArr(arr, val)
    Dim n : n = UBound(arr) + 1
    ReDim Preserve arr(n)
    arr(n) = val
    AppendArr = arr
End Function

效能優化建議

• 全文檢索引擎：資料量 > 10 萬筆請改用 SQL Server Full-Text Search、Elasticsearch、Lucene.NET
• 索引策略：搜尋欄位建立全文索引或 N-gram 索引
• 快取熱門查詢：Application 快取熱門關鍵字結果 5-10 分鐘
• 分頁優化：使用 `ROW_NUMBER() OVER` 或 `OFFSET/FETCH` (SQL Server 2012+) 避免大偏移

前端配合：搜尋建議 (Auto-complete)

<input type="text" id="searchBox" autocomplete="off">
<ul id="suggest" style="display:none;position:absolute;border:1px solid #ccc;background:#fff;"></ul>

<script>
$("#searchBox").on("input", function() {
    var q = $(this).val();
    if (q.length < 2) return $("#suggest").hide();
    $.getJSON("/api/suggest.asp?q=" + encodeURIComponent(q), function(data) {
        var html = "";
        for (var i=0; i";
        $("#suggest").html(html).show();
    });
});
</script>