一、概述
MySQL全文检索是利用查询关键字和查询列内容之间的相关度进行检索,可以利用全文索引来提高匹配的速度。
二、语法
1
2
|
MATCH (col1,col2,...) AGAINST (expr [search_modifier])
search_modifier: {
IN
BOOLEAN MODE |
WITH
QUERY EXPANSION }
|
例如:SELECT * FROM tab_name WHERE MATCH ('列名1,列名2...列名n') AGAINST('词1 词2 词3 ... 词m');
即:MATCH 相当于要匹配的列,而 AGAINST 就是要找的内容。
这里的table需要是MyISAM类型的表,col1、col2 必须是char、varchar或text类型,在查询之前需要在 col1 和 col2 上分别建立全文索引(FULLTEXT索引)。
三、检索方式
1、自然语言检索: IN NATURAL LANGUAGE MODE
2、布尔检索: IN BOOLEAN MODE
剔除一半匹配行以上都有的词,譬如说,每个行都有this这个字的话,那用this去查时,会找不到任何结果,这在记录条数特别多时很有用,原因是数据库认为把所有行都找出来是没有意义的,这时,this几乎被当作是stopword(中断词);但是若只有两行记录时,是啥鬼也查不出来的,因为每个字都出现50%(或以上),要避免这种状况,请用IN BOOLEAN MODE。
IN BOOLEAN MODE的特色:
·不剔除50%以上符合的row。
·不自动以相关性反向排序。
·可以对没有FULLTEXT index的字段进行搜寻,但会非常慢。
·限制最长与最短的字符串。
·套用Stopwords。
搜索语法规则:
+ 一定要有(不含有该关键词的数据条均被忽略)。
- 不可以有(排除指定关键词,含有该关键词的均被忽略)。
> 提高该条匹配数据的权重值。
< 降低该条匹配数据的权重值。
~ 将其相关性由正转负,表示拥有该字会降低相关性(但不像 - 将之排除),只是排在较后面权重值降低。
* 万用字,不像其他语法放在前面,这个要接在字符串后面。
" " 用双引号将一段句子包起来表示要完全相符,不可拆字。
1
2
3
4
5
6
7
8
9
10
11
12
|
-- + 表示AND,即必须包含。- 表示NOT,即必须不包含。即:返回记录必需包含 apple,且不能包含 banner。
SELECT
*
FROM
articles
WHERE
MATCH (title,content) AGAINST (
'+apple -banana'
IN
BOOLEAN MODE);
-- apple和banana之间是空格,空格表示OR。即:返回记录至少包含apple、banana中的一个。
SELECT
*
FROM
articles
WHERE
MATCH (title,content) AGAINST (
'apple banana'
IN
BOOLEAN MODE);
-- 返回记录必须包含apple,同时banana可包含也可不包含,若包含的话会获得更高的权重。
SELECT
*
FROM
articles
WHERE
MATCH (title,content) AGAINST (
'+apple banana'
IN
BOOLEAN MODE);
-- ~ 是我们熟悉的异或运算符。返回记录必须包含apple,若也包含了banana会降低权重。
-- 但是它没有 +apple -banana 严格,因为后者如果包含banana压根就不返回。
SELECT
*
FROM
articles
WHERE
MATCH (title,content) AGAINST (
'+apple ~banana'
IN
BOOLEAN MODE);
-- 返回必须同时包含“apple banana”或者必须同时包含“apple orange”的记录。
-- 若同时包含“apple banana”和“apple orange”的记录,则“apple banana”的权重高于“apple orange”的权重。
SELECT
*
FROM
articles
WHERE
MATCH (title,content) AGAINST (
'+apple +(>banana <orange)'
IN
BOOLEAN MODE);
|
3、查询扩展检索: WITH QUERY EXPANSION
四、MySQL全文检索的条件限制
1、在MySQL5.6以下,只有MyISAM表支持全文检索。在MySQL5.6以上Innodb引擎表也提供支持全文检索。
2、相应字段建立FULLTEXT索引
五、与全文检索相关的系统变量:
ft_min_word_len = 全文检索的最小许可字符(默认4,通过 SHOW VARIABLES LIKE 'ft_min_word_len' 可查看),中文通常是两个字就是一个词,所以做中文的话需要修改这个值为2最好。
六、总结事项
1、预设搜寻是不分大小写,若要分大小写,columne 的 character set要从utf8改成utf8_bin。
2、预设 MATCH...AGAINST 是以相关性排序,由高到低。
3、MATCH(title, content)里的字段必须和FULLTEXT(title, content)里的字段一模一样。
如果只要单查title或content一个字段,那得另外再建一个 FULLTEXT(title) 或 FULLTEXT(content),也因为如此,MATCH()的字段一定不能跨table,但是另外两种搜寻方式好像可以。
4、MySQL不支持中文全文索引,原因很简单:与英文不同,中文的文字是连着一起写的,中间没有MySQL能找到分词的地方,截至目前MySQL5.6版本是如此,但是有变通的办法,就是将整句的中文分词,并按urlencode、区位码、base64、拼音等进行编码使之以“字母+数字”的方式存储于数据库中。
七、实验部分
◆ 步骤1 配置my.ini,在my.ini末尾添加如下:
# 修改全文检索的最小许可字符为2个字符或汉字 ft_min_word_len = 2
完成后“重启 MySQL 服务”,并用 SHOW VARIABLES LIKE 'ft_min_word_len' 查询下是否得到了正确的结果值为2,如下图:
◆ 步骤2 创建数据库(视情况可跳过此步)
1
|
CREATE
DATABASE
search
DEFAULT
CHARACTER
SET
utf8
COLLATE
utf8_general_ci;
|
◆ 步骤3 创建数据表
1
2
3
4
5
6
7
8
9
10
11
12
|
CREATE
TABLE
`zzx_articles` (
`id`
int
(10) unsigned
NOT
NULL
auto_increment,
`title`
char
(254)
default
NULL
COMMENT
'标题'
,
`content` text COMMENT
'内容'
,
`author`
char
(60)
default
NULL
COMMENT
'作者'
,
`title_fc`
char
(254)
default
NULL
COMMENT
'标题的分词'
,
`content_fc` text COMMENT
'内容的分词'
,
PRIMARY
KEY
(`id`),
FULLTEXT
KEY
`zzx_title_fc` (`title_fc`),
FULLTEXT
KEY
`zzx_content_fc` (`content_fc`),
FULLTEXT
KEY
`zzx_title_con_fc` (`title_fc`,`content_fc`)
) ENGINE=MyISAM
DEFAULT
CHARSET=utf8
|
# 如果后期需要添全文加索引可以用如下语句:
1
2
3
|
alter
table
`zzx_articles`
add
fulltext zzx_title_fc(`title_fc`);
alter
table
`zzx_articles`
add
fulltext zzx_con_fc(`content_fc`);
alter
table
`zzx_articles`
add
fulltext zzx_title_con_fc(`title_fc`,`content_fc`);
|
◆ 步骤4 插入测试数据
1
2
3
4
5
6
7
8
9
10
11
|
INSERT
INTO
`zzx_articles` (title_fc,content_fc)
VALUES
(
'MySQL Tutorial Linux red'
,
'DBMS stands for DataBase ok'
),
(
'How To Use MySQL Well'
,
'After you went through blue'
),
(
'Optimizing MySQL ok'
,
'In this tutorial we will optimizing'
),
(
'MySQL vs this YourSQL blue red'
,
'1. Never run mysqld as root red'
),
(
'MySQL Tricks blue'
,
'In the following database'
),
(
'MySQL Security'
,
'When configured properly, MySQL'
),
(
'中华'
,
'中华人民共和国 '
),
(
'中华情 和谐'
,
'上海 和谐'
),
(
'污染之都'
,
'你好 我是 北京 人'
),
(
'北京精神'
,
'创新 爱国 包容 厚颜'
)
|
插入结果如下图:
◆ 步骤5 搜索语法规则、排序 实验
说明:匹配语句 MATCH (col1,col2,...) AGAINST (expr [search_modifier]) 匹配完成后,会返回此条数据的权重值(权重值1 ≈ 各个词的匹配结果权重值之和),我们利用此权重值“由高到低”排序可优化查询结果。
------------------------------------------------------------------------------------------------------------------------------
▶ 实验1:只对 title_fc 索引字段做全文检索,并显示每条数据的权重值
1
2
3
|
SELECT
*,MATCH (title_fc) AGAINST (
'optimizing ok red blue'
)
as
title_score
FROM
zzx_articles
WHERE
MATCH (title_fc) AGAINST (
'optimizing ok red blue'
IN
BOOLEAN MODE)
order
by
title_score
DESC
|
总结:
1.当没有加 + - 这样的过滤符号时,这些关键词是“或(or)”的关系,即:要么匹配optimizing,要么匹配ok,要么匹配red,要么匹配blue。
2.通过上面实验,发现当某条数据有多个关键词匹配时(如:red blue),此条数据的权重值会略高:此条数据权重值 ≈ optimizing权重值 + ok权重值 + red权重值 + blue权重值理论上来说,当一条数据能匹配上的关键词越多,则此条数据的权重值越高,排名越靠前。
------------------------------------------------------------------------------------------------------------------------------
▶ 实验2:过滤条件:必须包含"red"关键词
1
2
3
|
SELECT
*,MATCH (title_fc) AGAINST (
'optimizing ok red'
)
as
title_score
FROM
zzx_articles
WHERE
MATCH (title_fc) AGAINST (
'optimizing ok +red'
IN
BOOLEAN MODE)
order
by
title_score
DESC
|
总结:使用了过滤符号 + ,表示查询结果中,任一条数据都必须包含"red"这个词,不包含"red"这个词的行均被忽略。
------------------------------------------------------------------------------------------------------------------------------
▶ 实验3:过滤条件:必须包含"red"关键词,如果匹配到的行中还含有"blue"关键词,则会对此条数据增加权重:
1
2
3
|
SELECT
*,MATCH (title_fc) AGAINST (
'optimizing ok red blue'
)
as
title_score
FROM
zzx_articles
WHERE
MATCH (title_fc) AGAINST (
'optimizing ok +red blue'
IN
BOOLEAN MODE)
order
by
title_score
DESC
|
或下面写法:
1
2
3
|
SELECT
*,MATCH (title_fc) AGAINST (
'optimizing ok red >blue'
)
as
title_score
FROM
zzx_articles
WHERE
MATCH (title_fc) AGAINST (
'optimizing ok +red >blue'
IN
BOOLEAN MODE)
order
by
title_score
DESC
|
总结:与实验2比较,当包含了red的行中,若也包含blue关键词,权重确实增加了(如:id=4这条)。
------------------------------------------------------------------------------------------------------------------------------
▶ 实验4:过滤条件:必须包含"red"关键词,并且不能包含"blue"关键词
1
2
3
|
SELECT
*,MATCH (title_fc) AGAINST (
'optimizing ok red blue'
)
as
title_score
FROM
zzx_articles
WHERE
MATCH (title_fc) AGAINST (
'optimizing ok +red -blue'
IN
BOOLEAN MODE)
order
by
title_score
DESC
|
总结:可见 + - 这两个符号是表示“并且(and)”的意思,即:必须包含red关键词 and 不能包含blue关键词。
------------------------------------------------------------------------------------------------------------------------------
▶ 实验5:过滤条件:必须包含"red"关键词,如果匹配到的行中还包含"blue"关键词则降低此条数据权重
1
2
3
|
SELECT
*,MATCH (title_fc) AGAINST (
'optimizing ok +red ~blue'
)
as
title_score
FROM
zzx_articles
WHERE
MATCH (title_fc) AGAINST (
'optimizing ok +red ~blue'
IN
BOOLEAN MODE)
order
by
title_score
DESC
|
总结:这个实验没有看到明显效果,但 ~ 过滤符确实是降低此权重符
------------------------------------------------------------------------------------------------------------------------------
▶ 实验6:过滤条件:匹配包含单词“red”和“Linux” 的行,或包含“red” 和“blue”的行(无先后顺序)然而包含 “apple Linux”的行较包含“apple blue”的行有更高的权重值。
1
2
3
|
SELECT
*,MATCH (title_fc) AGAINST (
'+red +(>Linux <blue)'
)
as
title_score
FROM
zzx_articles
WHERE
MATCH (title_fc) AGAINST (
'+red +(>Linux <blue)'
IN
BOOLEAN MODE)
order
by
title_score
DESC
|
------------------------------------------------------------------------------------------------------------------------------
▶ 实验7:过滤条件:匹配关键词以 re 开头,或以 bl 开头的数据行
1
2
3
|
SELECT
*,MATCH (title_fc) AGAINST (
're* bl*'
)
as
title_score
FROM
zzx_articles
WHERE
MATCH (title_fc) AGAINST (
're* bl*'
IN
BOOLEAN MODE)
order
by
title_score
DESC
|
总结:注意 * 是写在后面哦,此时相当于 like 模糊匹配,没有权重值了
------------------------------------------------------------------------------------------------------------------------------
▶ 实验8:过滤条件:匹配查找字符串“To Use MySQL”关键词
1
2
3
|
SELECT
*,MATCH (title_fc) AGAINST (
'"To Use MySQL"'
)
as
title_score
FROM
zzx_articles
WHERE
MATCH (title_fc) AGAINST (
'"To Use MySQL"'
IN
BOOLEAN MODE)
order
by
title_score
DESC
|
总结:此时是把双引号内的的字符串看成一个关键词,若不用双引号则是将 To Use MySQL 三个关键词去分别匹配,两者有区别;
------------------------------------------------------------------------------------------------------------------------------
▶ 实验9:在实验1基础上,将blue的权重值忽视不要(注意与实验1比较)
1
2
3
|
SELECT
*,MATCH (title_fc) AGAINST (
'optimizing ok red'
)
as
title_score
FROM
zzx_articles
WHERE
MATCH (title_fc) AGAINST (
'optimizing ok red blue'
IN
BOOLEAN MODE)
order
by
title_score
DESC
|
总结:在实验1的基础上,此时去除select字段条件里的blue关键词,但在where里去仍保留blue关键词。我的本意是想正常匹配“optimizing ok red blue”这几个关键词,但不想得到blue的权重值(忽视blue的权重值)。
查询的结果是含有blue关键词的数据的权重值会略降低了。
通过“降重”——忽略某些关键词权重值的方式可使部分数据权重值减小,进而影响排序。
------------------------------------------------------------------------------------------------------------------------------
▶ 实验10:在实验9的基础上,在select字段条件里增加几个red关键词,where里的关键词保持不变(注意与实验1 实验9比较)。
1
2
3
|
SELECT
*,MATCH (title_fc) AGAINST (
'optimizing ok red red red'
)
as
title_score
FROM
zzx_articles
WHERE
MATCH (title_fc) AGAINST (
'optimizing ok red blue'
IN
BOOLEAN MODE)
order
by
title_score
DESC
|
总结:发现只要含有 red 关键词的数据的权重值都增加了,排序也发生了变化。说明通过“提重”——重复多次某些关键词权重值的方式可使部分数据权重值增加,进而影响排序。
------------------------------------------------------------------------------------------------------------------------------
▶ 实验11:同时对 title_fc 和 content_fc 两字段做全文检索
1
2
3
|
SELECT
*,MATCH (title_fc) AGAINST (
'optimizing ok red blue'
)
as
title_score,MATCH (content_fc) AGAINST (
'optimizing ok red blue'
)
as
content_score
FROM
zzx_articles
WHERE
MATCH (title_fc,content_fc) AGAINST (
'optimizing ok red blue'
IN
BOOLEAN MODE)
order
by
title_score
DESC
,content_score
DESC
|
总结:通过实验发现,又成功的取到了 content_fc 字段匹配的权重值,排序方式是首要按title字段权重降序排序,次要按 content_fc 权重降序排序。另外我发现 ok 这个关键词在对“title_fc char(254)”字段匹配时得到匹配值为2.1xxxxxxx,但对“content_fc text”字段匹配时权重值去为0,这是MySQL对各英文单词权重值的给予有自己的算法,我们无权干涉。所以当我们发现有些单词的权重值为零甚至为负时,不用过于纠结,因为MySQL有自己的算法。
关于排序,首要按 title_score 字段权重降序排序,次要按 content_score 权重降序排序,这样的排序规则看起来更科学了,但我想优秀的搜索引擎绝不至于简单如此吧,继续下面的实验:
-----------------------------------------------------------------------------------------------------------------------------
▶ 实验12:进一步优化 排序规则看一个SQL语句原型,查询“字段1,字段2”两字段,同时将每条数据的“字段1”与“字段2”的求和作为“字段3”字段:
1
2
3
|
select
字段1,字段2,字段1 + 字段2
as
字段3
from
表名
where
.....
|
下面将 title_fc 和 content_fc 两权重值求和,放入新字段 score1 中,并按 score1 首要排序,title_score 次之,content_score再次之:
1
2
3
|
SELECT
*,MATCH (title_fc) AGAINST (
'optimizing ok red blue'
)
as
title_score,MATCH (content_fc) AGAINST (
'optimizing ok red blue'
)
as
content_score,MATCH (title_fc) AGAINST (
'optimizing ok red blue'
) + MATCH (content_fc) AGAINST (
'optimizing ok red blue'
)
as
score1
FROM
zzx_articles
WHERE
MATCH (title_fc,content_fc) AGAINST (
'optimizing ok red blue'
IN
BOOLEAN MODE)
order
by
score1
DESC
,title_score
DESC
,content_score
DESC
|
总结:相对而言,如果 title_fc 和 content_fc 都匹配上了,应给予靠前的排名吧。所以首要按其 title_fc 和 content_fc 两权重值之和排名,次要再考虑 title_fc、content_fc 排序。
------------------------------------------------------------------------------------------------------------------------------
▶ 实验13:另一个角度看排序看一个SQL语句原型,如果性别字段值为“1”显示“男”否则显示“女”
1
|
select
*,IF(sex=1,
"男"
,
"女"
)
as
ssva
from
表名
where
id = 1
|
我的新排序思路:如果 title_fc 和 content_fc 同时匹配上的行做首要排序,然后对只匹配上 title_fc 的做次要排序,只匹配上 content_fc 的再次要排序。 (对于实验5的排序不科学之处在于:如果有一个对content_fc关键词的匹配权重很高,导致了求和后 score1 的值也高,但对title_fc的匹配权重去为0,由于score1值高却排到了前面这不一定科学吧?)
1
2
3
|
SELECT
*,MATCH (title_fc) AGAINST (
'optimizing ok red blue'
)
as
title_score,MATCH (content_fc) AGAINST (
'optimizing ok red blue'
)
as
content_score,IF(MATCH (title_fc) AGAINST (
'optimizing ok red blue'
) > 0
AND
MATCH (content_fc) AGAINST (
'optimizing ok red blue'
) > 0,1,0)
as
score1
FROM
zzx_articles
WHERE
MATCH (title_fc,content_fc) AGAINST (
'optimizing ok red blue'
IN
BOOLEAN MODE)
order
by
score1
DESC
,title_score
DESC
,content_score
DESC
|
总结:如果 title_fc 和 content_fc 都匹配上了,做为优先排序理所当然,但也应考虑局部权重值过高的问题哦。
------------------------------------------------------------------------------------------------------------------------------
▶ 实验14:优化实验13,可支持更复杂的条件排序看一个SQL语句原型,CASE WHEN THEN END 结构:
1
2
3
4
5
6
7
|
CASE
<单值表达式>
WHEN
<表达式值>
THEN
<SQL语句或者返回值>
WHEN
<表达式值>
THEN
<SQL语句或者返回值>
...
WHEN
<表达式值>
THEN
<SQL语句或者返回值>
ELSE
<SQL语句或者返回值>
END
|
有一表查询:大于或等于80表示显示为“优秀”,大于或等于60显示为“及格”,小于60分显示为“不及格”。
1
2
3
4
|
select
(
CASE
WHEN
语文>=80
THEN
'优秀'
WHEN
语文>=60
THEN
'及格'
ELSE
'不及格'
END
)
as
语文,
(
CASE
WHEN
数学>=80
THEN
'优秀'
WHEN
数学>=60
THEN
'及格'
ELSE
'不及格'
END
)
as
数学,
(
CASE
WHEN
英语>=80
THEN
'优秀'
WHEN
英语>=60
THEN
'及格'
ELSE
'不及格'
END
)
as
英语
from
table_name
|
# 实例测试一下
1
2
3
|
select
*,(
CASE
WHEN
id<8
THEN
'5'
WHEN
id=8
THEN
'4'
ELSE
'0'
END
)
as
newfield
from
zzx_articles
where
id>5
|
我的新排序思路:如果 title_fc 和 content_fc 的权重值“同时大于0且相等”为首要排序,“同时大于0且不相等”的为次要排序,“title_fc 大于0的再次要排序”,如果用 IF() 貌似不好实现,看下面语句:
1
2
3
4
|
SELECT
*,MATCH (title_fc) AGAINST (
'optimizing ok red blue'
)
as
title_score,MATCH (content_fc) AGAINST (
'optimizing ok red blue'
)
as
content_score,
(
CASE
WHEN
MATCH (title_fc) AGAINST (
'optimizing ok red blue'
) > 0
AND
MATCH (title_fc) AGAINST (
'optimizing ok red blue'
) = MATCH (content_fc) AGAINST (
'optimizing ok red blue'
)
THEN
'3'
WHEN
MATCH (title_fc) AGAINST (
'optimizing ok red blue'
) > 0
AND
MATCH (title_fc) AGAINST (
'optimizing ok red blue'
) <> MATCH (content_fc) AGAINST (
'optimizing ok red blue'
)
THEN
'2'
WHEN
MATCH (title_fc) AGAINST (
'optimizing ok red blue'
) > 0
THEN
'1'
ELSE
'0'
END
)
AS
score1
FROM
zzx_articles
WHERE
MATCH (title_fc,content_fc) AGAINST (
'optimizing ok red blue'
IN
BOOLEAN MODE)
order
by
score1
DESC
,title_score
DESC
,content_score
DESC
|
总结:本实验的排序未必合乎科学,但引出一个更复杂规则的排序方式、角度,多种排序结合使用才能做出更合理的排序,才能使你的搜索引擎更加智能。抛砖引玉,或许你有更好的排序,请也分享给我。
------------------------------------------------------------------------------------------------------------------------------
▶ 实验15:中文全文检索
MySQL不支持中文全文检索,因为中文一句话是连着写的,不像英文单词间有空格分隔。解决办法就是中文分词(关于中文分词请参阅其它文章),如果你的MySQL是安装在Windows平台上的,可以不用转码直接存储中文就可以使用全文索引,如本例。但是如果你的MySQL是安装在Linux上的则需要进行转编码(urlencode / base64_encode / json_encode / 区位 / 拼音)等方案,具体方案参看其它博文。
1
2
3
|
SELECT
*,MATCH (title_fc) AGAINST (
'中华 北京 和谐 security'
)
as
title_score,MATCH (content_fc) AGAINST (
'中华 北京 和谐 security'
)
as
content_score,(
case
when
MATCH (title_fc) AGAINST (
'中华 北京 和谐 security'
) > 0
and
MATCH (content_fc) AGAINST (
'中华 北京 和谐 security'
) > 0
then
'5'
when
MATCH (title_fc) AGAINST (
'中华 北京 和谐 security'
) > 0
and
MATCH (content_fc) AGAINST (
'中华 北京 和谐 security'
) = 0
then
'4'
else
'0'
end
)
as
score1
FROM
zzx_articles
WHERE
MATCH (title_fc,content_fc) AGAINST (
'中华 北京 和谐 security'
IN
BOOLEAN MODE)
order
by
score1
DESC
,title_score
DESC
,content_score
DESC
|