注意这里是 MySQL,不是 ES,搜索引擎做这种搜索需求当然得天独厚,但是这种在 MySQL 中进行权重搜索的需求也不是没有,业务初期数据量还不大的时候大概率不会考虑上 ES,这时候在 MySQL 中先简单跑通逻辑才是最重要的。
思考下该如何做?
模糊匹配
首先模糊匹配大家最常用的就是 like
:
SELECT * FROM test
WHERE t1 LIKE '%%'
or t2 LIKE '%内容%'
or t3 LIKE '%注释%''
当只需要简单的模式匹配时 like
确实往往是更好的选择。而在需要进行复杂匹配,如同一字段中包含多个模式,进行分组匹配等,REGEXP
则表现更为突出,Mysql 支持对列的正则表达式方式查询,使用方式如下:
SELECT * FROM test
WHERE t1 regexp ''
or t2 regexp '内容'
or t3 regexp '注释'
权重搜索
权重搜索涉及到几个 Mysql 函数。
LOCATE('', test.t1)
: 查询 "" 在 test.t1 列出现的位置,0 表示未找到。否则返回 坐标位置,坐标位置从 1 开始。IF( 表达式, 1, 0)
:判断表达式结果,TRUE 则返回 1,FALSE 则返回 0
下面我们来看如何基于这两个函数来实现文章开头的需求:
SELECT *, (
IF(LOCATE('',test.t1), 1, 0)
+ IF(LOCATE('内容',test.t2) , 1, 0)
+ IF(LOCATE('注释',test.t3) , 1, 0)
) AS weight
FROM test
WHERE test.t1 regexp ''
or test.t2 regexp '内容'
or test.t3 regexp '注释'
order by weight desc
上面的查询中,每个关键词的权重都是 1,所以,在这 t1\t2\t3 三列中,关键词出现次数最多的记录将出现在搜索结果的第一位。
如果权重增加,那么权重高的关键词将会影响排序规则。如下例子,将 t1 列的搜索权重改为 7:
SELECT *, (
IF(LOCATE('',test.t1), 7, 0)
+ IF(LOCATE('内容',test.t2) , 2, 0)
+ IF(LOCATE('注释',test.t3) , 1, 0)
) AS weight
FROM test
WHERE test.t1 regexp ''
or test.t2 regexp '内容'
or test.t3 regexp '注释'
order by weight desc