您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

MySQL 關鍵字相關度排序：兩種實用加權演算法實作

編輯：MySQL 資料庫相關知識

場景

小型專案需關鍵字搜尋並按相關度排序，引入 Sphinx/Elasticsearch 過重，直接用 MySQL ORDER BY 解決。

方法一：CASE WHEN 靜態加權（適用固定欄位、關鍵字少）

SELECT *
FROM articles
WHERE (title LIKE '%keywords%') OR (content LIKE '%helloworld%')
ORDER BY (
    (CASE WHEN title LIKE '%keywords%' THEN 2 ELSE 0 END) +
    (CASE WHEN content LIKE '%helloworld%' THEN 1 ELSE 0 END)
) DESC, dateline DESC;

標題命中權重 2，內文命中權重 1，分數高者優先。
缺點：關鍵字寫死在 SQL，動態關鍵字需拼接 SQL，防注入需參數化。

方法二：動態 PHP 產生加權 SQL（適用多關鍵字、多欄位、可配置權重）

關鍵字預處理

$kw = preg_replace('/[\s\r\n\t]+/', ' ', trim($kw)); // 合併空白
$kw = preg_replace('/[[:punct:]]/', '', $kw);        // 移除標點（視需求保留）
$q = explode(' ', $kw);                              // 拆分關鍵字陣列

欄位與權重設定

$fields = ['name', 'description'];   // 搜尋欄位
$weights = [4, 2];                   // 對應權重：名稱 4 分，描述 2 分

動態建構 WHERE 與 SCORE

$clauses = [];
$scores  = [];
$c = 0;

foreach ($q as $keyword) {
    foreach ($fields as $i => $field) {
        $clauses[] = "`$field` LIKE '%$keyword%'";
        $scores[]  = "IF(LOCATE('$keyword', `$field`), {$weights[$i]}, 0)";
    }
}

$sql = "
    SELECT id, name, description,
           (" . implode(' + ', $scores) . ") AS score
    FROM product
    WHERE (" . implode(' OR ', $clauses) . ")
    ORDER BY score DESC
";

生成 SQL 範例（關鍵字 "IBM 伺服器"）

SELECT id, name, description,
       (IF(LOCATE('IBM', `name`), 4, 0) + IF(LOCATE('IBM', `description`), 2, 0) +
        IF(LOCATE('伺服器', `name`), 4, 0) + IF(LOCATE('伺服器', `description`), 2, 0)) AS score
FROM product
WHERE (`name` LIKE '%IBM%' OR `description` LIKE '%IBM%' OR `name` LIKE '%伺服器%' OR `description` LIKE '%伺服器%')
ORDER BY score DESC;

效能優化建議

優化點	做法
避免全表掃描	在搜尋欄位建立全文索引 (FULLTEXT)，改用 `MATCH() AGAINST()`
減少 LOCATE 呼叫	單關鍵字單欄位只算一次，用子查詢或 CTE 預計算
限制候選集	先用 `WHERE MATCH(...) AGAINST(...)` 粗篩，再對前 N 筆精細加權
快取熱門查詢	Redis 快取關鍵字 → 結果 ID 列表，TTL 5-10 分鐘

進階：MySQL 8.0+ 內建相關度

SELECT *, MATCH(title, content) AGAINST('+IBM +伺服器' IN BOOLEAN MODE) AS relevance
FROM articles
WHERE MATCH(title, content) AGAINST('+IBM +伺服器' IN BOOLEAN MODE)
ORDER BY relevance DESC;

內建全文檢索已支援布林模式與自然語言模式，相關度計算更科學，優先考慮升級版本使用。