本文示範如何使用 Classic ASP + Access (music.mdb) + HTML5/.m3u 播放清單,快速建構一個具備「在線試聽、排行榜、關鍵字搜尋、檔案下載、點擊統計」的音樂伺服器。架構輕量、部署簡單,適合中小型音樂分享站點或教學示範。
為簡化示範採用單表設計,實際專案建議正規化拆分為 Artists、Albums、Tracks、PlayLogs 等多表。
CREATE TABLE Music (
ID AUTOINCREMENT PRIMARY KEY,
MP3Name TEXT(255) NOT NULL, -- 歌曲名稱
Singer TEXT(100), -- 歌手
Album TEXT(100), -- 專輯
URL TEXT(255) NOT NULL, -- MP3 檔案相對/絕對 URL
ClickCount LONG DEFAULT 0, -- 總試聽次數
TodayClick LONG DEFAULT 0, -- 今日試聽次數 (每日歸零排程重置)
DownCount LONG DEFAULT 0, -- 總下載次數
AddDate DATETIME DEFAULT NOW()
);
CREATE INDEX IDX_Singer ON Music (Singer);
CREATE INDEX IDX_MP3Name ON Music (MP3Name);
play.asp。play.asp 依 ID 查詢資料庫,產生 listen.m3u 文字檔 (每行一個 MP3 絕對 URL)。Content-Type: audio/mpegurl 並 Response.Redirect 至該 .m3u。<audio>) 播放清單。<%
' play.asp
Option Explicit
Response.Buffer = True
Response.ContentType = "audio/mpegurl" ' 關鍵 MIME
Dim choose, path, conn, rs, sql, fso, m3uFile, idList, i
choose = "("
' 假設表單欄位名為 song_1, song_2... 或統一名稱 song_id
For Each idList In Request.Form
If Left(idList, 8) = "song_id_" Then
choose = choose & Request.Form(idList) & ","
End If
Next
If Len(choose) = 1 Then Response.Redirect "list.asp" : Response.End
choose = Left(choose, Len(choose)-1) & ")"
path = Server.MapPath("/temp/") ' 需給 IUSR 寫入權限
Set fso = Server.CreateObject("Scripting.FileSystemObject")
Set m3uFile = fso.CreateTextFile(path & "listen.m3u", True)
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("/db/music.mdb")
sql = "SELECT MP3Name, URL FROM Music WHERE ID IN " & choose
Set rs = conn.Execute(sql)
Do Until rs.EOF
m3uFile.WriteLine "https://" & Request.ServerVariables("HTTP_HOST") & rs("URL")
rs.MoveNext
Loop
' 更新統計
sql = "UPDATE Music SET ClickCount = ClickCount + 1, TodayClick = TodayClick + 1 WHERE ID IN " & choose
conn.Execute sql
rs.Close: Set rs = Nothing
conn.Close: Set conn = Nothing
m3uFile.Close: Set m3uFile = Nothing
Set fso = Nothing
Response.Redirect "/temp/listen.m3u"
Response.End
%>
IIS 管理員 → 網站屬性 → HTTP 標頭 → 檔案類型 → 新增:
.m3uaudio/mpegurl' 總試聽前 20
SELECT TOP 20 ID, MP3Name, Singer, ClickCount FROM Music ORDER BY ClickCount DESC
' 今日試聽前 20
SELECT TOP 20 ID, MP3Name, Singer, TodayClick FROM Music ORDER BY TodayClick DESC
' 總下載前 20
SELECT TOP 20 ID, MP3Name, Singer, DownCount FROM Music ORDER BY DownCount DESC
多表聯查 (JOIN) 版本:
SELECT m.MP3Name, a.ArtistName, m.ClickCount
FROM Music m INNER JOIN Artists a ON m.ArtistID = a.ID
ORDER BY m.ClickCount DESC
' search.asp
Dim keyword, condition, sql, conn, rs
keyword = Request("keyword") : condition = Request("condition") ' "exact" 或 "like"
sql = "SELECT ID, MP3Name, Singer, Album FROM Music WHERE 1=1 "
If keyword <> "" Then
Select Case condition
Case "exact": sql = sql & " AND Singer = '" & Replace(keyword, "'", "''") & "'"
Case "like": sql = sql & " AND (Singer LIKE '%" & Replace(keyword, "'", "''") & "%' OR MP3Name LIKE '%" & Replace(keyword, "'", "''") & "%')"
End Select
End If
sql = sql & " ORDER BY Singer, MP3Name"
' ... 執行 rs, 輸出 HTML 表格 ...
防注入:生產環境務必使用 Command.Parameters 參數化查詢,或至少雙重單引號轉義 Replace(input, "'", "''")。
<%
' download.asp
Dim id, conn, rs, sql, fileUrl
id = Request("id")
If Not IsNumeric(id) Then Response.End
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("/db/music.mdb")
sql = "SELECT URL FROM Music WHERE ID = " & id
Set rs = conn.Execute(sql)
If rs.EOF Then Response.End
fileUrl = "https://" & Request.ServerVariables("HTTP_HOST") & rs("URL")
' 更新下載計數
conn.Execute "UPDATE Music SET DownCount = DownCount + 1 WHERE ID = " & id
rs.Close: Set rs = Nothing
conn.Close: Set conn = Nothing
Response.Redirect fileUrl
Response.End
%>
<audio controls preload="metadata"><source src="..." type="audio/mpeg"></audio> 或 MediaElement.js 統一跨瀏覽器體驗。ASP 雖屬經典技術,配合 ADO、FileSystemObject、HTTP 重導向機制,仍能快速構建功能完整的音樂伺服器原型。核心在於資料庫設計正規化、MIME 類型正確設定、參數化查詢防注入、統計邏輯與業務解耦。若作為生產系統,請務必進行資料庫遷移、雲端儲存、HTML5 播放器現代化改造。