AJAX 数据库
AJAX 查库返回列表、搜索建议、分页与参数化。
Key PointsAJAX 查库与普通页面查库一样，只是把结果编码成 JSON 而不是拼 HTML。
搜索建议
ASP
<%-- suggest.asp --%>
<%
Response.ContentType = "application/json"
Response.Charset = "UTF-8"
Dim kw, cmd, rs, first
kw = Request.QueryString("kw")
If Len(kw) < 1 Then
Response.Write "[]"
Response.End
End If
Set cmd = Server.CreateObject("ADODB.Command")
Set cmd.ActiveConnection = conn
cmd.CommandText = "SELECT TOP 10 name FROM products WHERE name LIKE ? ORDER BY name"
cmd.Parameters.Append cmd.CreateParameter("kw", 200, 1, 100, "%" & kw & "%")
Set rs = cmd.Execute
first = True
Response.Write "["
Do While Not rs.EOF
If Not first Then Response.Write ","
Response.Write """" & JsonEsc(rs("name")) & """"
first = False
rs.MoveNext
Loop
Response.Write "]"
%>JavaScript
const input = document.getElementById('q');
let timer = null;
input.addEventListener('input', () => {
clearTimeout(timer);
timer = setTimeout(async () => {
const kw = input.value.trim();
if (!kw) return render([]);
const res = await fetch(`suggest.asp?kw=${encodeURIComponent(kw)}`);
render(await res.json());
}, 250); // 停手 250ms 才发请求
});Output
["笔记本电脑","笔记本支架","笔记本内存条"]Debounce the input: one request per keystroke means five queries for a single word, hammering the server and database for nothing. 250ms is a common value.
参数化的必要性
ASP
<%-- 危险：搜索词直接拼进 SQL --%>
sql = "SELECT * FROM products WHERE name LIKE '%" & kw & "%'"
<%-- 输入 ' UNION SELECT username, password FROM users-- 就能拖库 --%>
<%-- 安全：参数化，% 拼在值里而不是 SQL 里 --%>
cmd.CommandText = "SELECT * FROM products WHERE name LIKE ?"
cmd.Parameters.Append cmd.CreateParameter("kw", 200, 1, 100, "%" & kw & "%")Put the % wildcards in the parameter value, never in the SQL text. In the SQL they defeat parameterisation and injection returns.
分页加载
ASP
<%
Dim page, size, offset
page = CLng("0" & Request.QueryString("page"))
If page < 1 Then page = 1
size = 20
offset = (page - 1) * size
cmd.CommandText = "SELECT id, name, price FROM products " & _
"ORDER BY id OFFSET ? ROWS FETCH NEXT ? ROWS ONLY"
cmd.Parameters.Append cmd.CreateParameter("off", 3, 1, , offset)
cmd.Parameters.Append cmd.CreateParameter("size", 3, 1, , size)
Set rs = cmd.Execute
%>JavaScript
let page = 1;
let loading = false;
window.addEventListener('scroll', async () => {
if (loading) return;
if (window.innerHeight + window.scrollY < document.body.offsetHeight - 200) return;
loading = true;
const res = await fetch(`list.asp?page=${page + 1}`);
const rows = await res.json();
if (rows.length) { append(rows); page++; }
loading = false;
});Infinite scroll needs a loading flag, or fast scrolling fires several requests at once and duplicates rows. A 200px threshold ahead of the bottom feels smoother.
性能要点
- 只查需要的列 ——
SELECT *在大表上会拖慢很多 - 给搜索列建索引 ——
LIKE '%x%'用不上索引，考虑全文索引 - 限制返回条数 ——
TOP 10比返回几千行再前端截断好得多 - 连接复用 —— 每个请求新建连接，几十并发就把数据库连接数打满
LIKE '%kw%' with leading wildcards disables indexes entirely — every query is a full scan on a million-row table. For large data use full-text indexes (SQL Server Full-Text, MySQL FULLTEXT) or a search engine.缓存热点结果
ASP
<%
Dim cacheKey, cached
cacheKey = "suggest:" & kw
If Not IsEmpty(Application(cacheKey)) Then
Response.Write Application(cacheKey)
Response.End
End If
' ... 查库并生成 json ...
Application.Lock
Application(cacheKey) = json
Application.Unlock
%>Caching results in Application cuts database load significantly, but invalidation matters: a renamed product still shows the old name. Stamp the cache with a timestamp and refresh on expiry.
搜索建议
ASP
<%-- suggest.asp --%>
<%
Response.ContentType = "application/json"
Response.Charset = "UTF-8"
Dim kw, cmd, rs, first
kw = Request.QueryString("kw")
If Len(kw) < 1 Then
Response.Write "[]"
Response.End
End If
Set cmd = Server.CreateObject("ADODB.Command")
Set cmd.ActiveConnection = conn
cmd.CommandText = "SELECT TOP 10 name FROM products WHERE name LIKE ? ORDER BY name"
cmd.Parameters.Append cmd.CreateParameter("kw", 200, 1, 100, "%" & kw & "%")
Set rs = cmd.Execute
first = True
Response.Write "["
Do While Not rs.EOF
If Not first Then Response.Write ","
Response.Write """" & JsonEsc(rs("name")) & """"
first = False
rs.MoveNext
Loop
Response.Write "]"
%>JavaScript
const input = document.getElementById('q');
let timer = null;
input.addEventListener('input', () => {
clearTimeout(timer);
timer = setTimeout(async () => {
const kw = input.value.trim();
if (!kw) return render([]);
const res = await fetch(`suggest.asp?kw=${encodeURIComponent(kw)}`);
render(await res.json());
}, 250); // 停手 250ms 才发请求
});Output
["笔记本电脑","笔记本支架","笔记本内存条"]输入框必须防抖。每敲一个字母发一次请求，输入「笔记本电脑」就是五次查询，服务端和数据库都被无谓地打。250ms 是常见取值。
参数化的必要性
ASP
<%-- 危险：搜索词直接拼进 SQL --%>
sql = "SELECT * FROM products WHERE name LIKE '%" & kw & "%'"
<%-- 输入 ' UNION SELECT username, password FROM users-- 就能拖库 --%>
<%-- 安全：参数化，% 拼在值里而不是 SQL 里 --%>
cmd.CommandText = "SELECT * FROM products WHERE name LIKE ?"
cmd.Parameters.Append cmd.CreateParameter("kw", 200, 1, 100, "%" & kw & "%")LIKE 的通配符 % 要拼在参数值里，不能拼在 SQL 文本里。拼在 SQL 里，参数化的意义就没了——攻击者仍然能注入。
分页加载
ASP
<%
Dim page, size, offset
page = CLng("0" & Request.QueryString("page"))
If page < 1 Then page = 1
size = 20
offset = (page - 1) * size
cmd.CommandText = "SELECT id, name, price FROM products " & _
"ORDER BY id OFFSET ? ROWS FETCH NEXT ? ROWS ONLY"
cmd.Parameters.Append cmd.CreateParameter("off", 3, 1, , offset)
cmd.Parameters.Append cmd.CreateParameter("size", 3, 1, , size)
Set rs = cmd.Execute
%>JavaScript
let page = 1;
let loading = false;
window.addEventListener('scroll', async () => {
if (loading) return;
if (window.innerHeight + window.scrollY < document.body.offsetHeight - 200) return;
loading = true;
const res = await fetch(`list.asp?page=${page + 1}`);
const rows = await res.json();
if (rows.length) { append(rows); page++; }
loading = false;
});无限滚动要加 loading 标志，否则用户快速滚动会同时发好几个请求，结果重复插入同一批数据。滚动阈值留 200px 提前量，体验更顺。
性能要点
- 只查需要的列 ——
SELECT *在大表上会拖慢很多 - 给搜索列建索引 ——
LIKE '%x%'用不上索引，考虑全文索引 - 限制返回条数 ——
TOP 10比返回几千行再前端截断好得多 - 连接复用 —— 每个请求新建连接，几十并发就把数据库连接数打满
LIKE '%kw%' 前后都有通配符时索引完全失效，百万行表上每次查询都是全表扫描。数据量大时改用全文索引（SQL Server 的 Full-Text、MySQL 的 FULLTEXT），或引入专门的搜索引擎。缓存热点结果
ASP
<%
Dim cacheKey, cached
cacheKey = "suggest:" & kw
If Not IsEmpty(Application(cacheKey)) Then
Response.Write Application(cacheKey)
Response.End
End If
' ... 查库并生成 json ...
Application.Lock
Application(cacheKey) = json
Application.Unlock
%>把搜索结果缓存在 Application 里能显著减少数据库压力，但要考虑失效策略——商品改名后缓存还是旧的。给缓存加时间戳，过期就重查。