实现根据文章的关键词搜索出相关的文章,并按相关性排序。

数据库版本是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