為龐大內容站點提供站內搜索,核心是建立索引數據庫並實現高效模糊匹配。本文提供基於 Access + ADO 的完整實現,涵蓋建表、錄入、搜索頁、結果頁、XSS 防護等全鏈路代碼。
' Access 建表 SQL
CREATE TABLE search_index (
id AUTOINCREMENT PRIMARY KEY,
title TEXT(255), -- 文檔標題
keywords MEMO, -- 關鍵詞,空格/逗號分隔
url TEXT(255), -- 訪問鏈接
content MEMO, -- 可選:全文內容(大文本建議單獨表)
addtime DATETIME DEFAULT NOW()
);
CREATE INDEX idx_title ON search_index (title);
CREATE INDEX idx_keywords ON search_index (keywords);
<!-- add.asp --%>
<form method="post" action="add_ok.asp">
標題: <input name="title" size="60" required><br>
關鍵詞: <input name="keywords" size="60" placeholder="空格分隔"><br>
鏈接: <input name="url" size="60" value="https://"><br>
內容: <textarea name="content" rows="10" cols="60"></textarea><br>
<input type="submit" value="加入索引">
</form>
<!--#INCLUDE file="ADOVBS.inc"-->
<%
Dim conn, rs, sql, keyword, safeKeyword, pageSize, pageNum, totalRecords, totalPages
keyword = Trim(Request.Form("word"))
If keyword = "" Then Response.Redirect "search_form.asp"
' 安全過濾:防注入
safeKeyword = Replace(keyword, "'", "''")
safeKeyword = Replace(safeKeyword, "%", "[%]") ' 逃逸 LIKE 通配符
safeKeyword = Replace(safeKeyword, "_", "[_]")
safeKeyword = Replace(safeKeyword, "[", "[[]")
' 連接數據庫
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("db/search.mdb")
' 統計總數
sql = "SELECT COUNT(*) AS cnt FROM search_index WHERE title LIKE '%" & safeKeyword & "%' OR keywords LIKE '%" & safeKeyword & "%'"
Set rs = conn.Execute(sql)
totalRecords = rs("cnt")
rs.Close
' 分頁參數
pageSize = 10
pageNum = CInt(Request.QueryString("page"))
If pageNum < 1 Then pageNum = 1
totalPages = Int((totalRecords + pageSize - 1) / pageSize)
If pageNum > totalPages Then pageNum = totalPages
' 分頁查詢(Access 不支持 LIMIT/OFFSET,用 TOP + NOT IN 模擬)
If pageNum = 1 Then
sql = "SELECT TOP " & pageSize & " id, title, url FROM search_index WHERE title LIKE '%" & safeKeyword & "%' OR keywords LIKE '%" & safeKeyword & "%' ORDER BY addtime DESC"
Else
Dim offset : offset = (pageNum - 1) * pageSize
sql = "SELECT TOP " & pageSize & " id, title, url FROM search_index WHERE id NOT IN (SELECT TOP " & offset & " id FROM search_index WHERE title LIKE '%" & safeKeyword & "%' OR keywords LIKE '%" & safeKeyword & "%' ORDER BY addtime DESC) AND (title LIKE '%" & safeKeyword & "%' OR keywords LIKE '%" & safeKeyword & "%') ORDER BY addtime DESC"
End If
Set rs = conn.Execute(sql)
%>
<!DOCTYPE html>
<html><head><meta charset="gb2312"><title>搜索結果: <%=Server.HTMLEncode(keyword)%></title>
<style>.highlight{background:yellow;font-weight:bold;} .pager a{margin:0 3px;} .pager .current{font-weight:bold;color:red;}</style></head>
<body>
<h2>搜索「<%=Server.HTMLEncode(keyword)%>」共找到 <%=totalRecords%> 條結果(第 <%=pageNum%>/<%=totalPages%> 頁)</h2>
<table border="1" cellpadding="5" cellspacing="0" width="100%">
<tr><th>ID</th><th>標題</th><th>操作</th></tr>
<%
Dim highlightRegex, highlightedTitle
Set highlightRegex = New RegExp
highlightRegex.Global = True
highlightRegex.IgnoreCase = True
highlightRegex.Pattern = "(" & EscapeRegExp(keyword) & ")"
Do While Not rs.EOF
highlightedTitle = highlightRegex.Replace(rs("title"), "$1")
%>
<tr>
<td><%=rs("id")%></td>
<td><%=highlightedTitle%></td>
<td><a href="<%=rs("url")%>" target="_blank">查看</a></td>
</tr>
<%
rs.MoveNext
Loop
rs.Close
%>
</table>
<div class="pager">
<%
Dim p
For p = 1 To totalPages
If p = pageNum Then
Response.Write "" & p & " "
Else
Response.Write "" & p & " "
End If
Next
%>
</div>
<%
conn.Close
Set conn = Nothing
Function EscapeRegExp(s)
EscapeRegExp = Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(s, "\", "\\"), ".", "\."), "*", "\*"), "+", "\+"), "?", "\?"), "[", "\["), "]", "\]"), "(", "\("), ")", "\)"), "{", "\{"), "}", "\}"), "|", "\|"), "^", "\^"), "$", "\$")
End Function
%>
</body></html>
<!-- search_form.asp --%>
<form method="post" action="search.asp">
<input type="text" name="word" size="40" placeholder="輸入關鍵詞" required>
<input type="submit" value="搜索">
</form>
CONTAINS/FREETEXT 或整合 Lucene.NET/Elasticsearch。word_doc 反向索引表(詞 -> 文檔 ID 列表),查詢時取交集,毫秒級響應。Server.HTMLEncode 防 XSS。此方案適合中小型站點快速上線,大規模場景請遷移專業搜索引擎。