多欄位搜尋時的排序問題 : O3noBLOG

01月 25日

多欄位搜尋時的排序問題

有看過 usability 的相關文件的話，或許會有看到關於搜尋的欄位，不要給太多的選項讓使用者調整，像是 Amazon 或是 Google 那樣，在初始的搜尋介面只有一個欄位讓人填寫，其實可以有比較多的成功搜尋，最近就試著做了這樣的設計，結果沒想到還有些背後的東西需要顧慮，而且目前我還沒想到個好解法。

問題就是結果的排序該如何排，我做的是歌曲的搜尋，會希望歌名有符合關鍵字的放第一順位，演唱者的第二，然後才是其他的資訊，不過如果只是用 SQL 的 OR 來做多欄位搜尋，結果會怎樣排序不是我們能決定的，ORDER 只能針對單一欄位的內容來進行排序，而不能根據那個欄位有符合到關鍵字來決定順序，我想了很久才想到目前的解法：

SELECT DISTINCT id, title, artist, artist_id, program FROM (
    SELECT *, 1 AS o FROM songs WHERE id = 'keyword' UNION
    SELECT *, 2 AS o FROM songs WHERE title LIKE 'keyword' UNION
    SELECT *, 3 AS o FROM songs WHERE artist LIKE '%keyword%' UNION
    SELECT *, 4 AS o FROM songs WHERE program LIKE '%keyword%' UNION
    SELECT *, 5 AS o FROM songs WHERE utaidasi LIKE '%keyword%'
) s ORDER BY o

大量的子查詢，還好我的資料庫小，使用人數也少，不然應該一下系統就撐不下去了吧，而且這個方法還有個問題，就是只能處理一個關鍵字，如果有兩個以上的關鍵字，似乎就沒辦法只用 SQL 來做處理了，因為不同的關鍵字可能在不同欄位符合，要計算結果的優先順序會隨著關鍵字增加而變得越來越複雜，純用 SQL 來寫感覺就是會整個慢到，不知道 Amazon 是怎麼處理這個問題的架構的，我想應該是自己開發或修改過的資料庫系統吧XD。

單篇彙整