實現根據文章的關鍵詞搜索出相關的文章,並按相關性排序。

數據庫版本是5.6.x,還不能像5.7那樣支持中文的全文檢索,所以另外創建一列保存關鍵詞的編碼。方法為base64轉碼並去掉「%」符號:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
<?php
/**
* 轉換關鍵詞到全文檢索的格式
*
* @param mixed $keywords 關鍵詞數組或英文逗號分隔的字符串
* @return string
*/
function encode_keywords($keywords)
{
$encoder = function ($keyword) {
return str_replace('%', '', urlencode(trim($keyword)));
};
if (!is_array($keywords)) {
$keywords = explode(',', $keywords);
}
return implode(' ', array_map($encoder, $keywords));
}

對該列創建全文檢索的索引:

1
CREATE FULLTEXT INDEX idx_post_keywords ON posts (keywords_ft);

查詢語句:

1
2
3
4
5
6
7
8
9
10
11
SELECT
id,
title,
keywords,
(MATCH (keywords_ft) AGAINST ('E697B6E5B09A E5A8B1E4B990E59C88' IN NATURAL LANGUAGE MODE)) AS `score`
FROM
posts
WHERE
MATCH (keywords_ft) AGAINST ('E697B6E5B09A E5A8B1E4B990E59C88' IN NATURAL LANGUAGE MODE)
ORDER BY score DESC
LIMIT 10