图片 13

MySQL中文全文检索,搜索之简单应用

2.如果没有SQL_MODE,就添加,有就修改一下

Records: 3  Duplicates: 0  Warnings: 0

六、总结事项
     1、预设搜寻是不分大小写,若要分大小写,columne 的 character
set要从utf8改成utf8_bin。

They have five members and they burn their instruments when they play in concerts. 

     2、预设 MATCH…AGAINST 是以相关性排序,由高到低。

sql_mode="STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION"

3 rows in set (0.00 sec)

# 如果后期需要添全文加索引可以用如下语句:
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`);

经过排查,原来是MySQL的配置问题,在wamp下,MySQL
5.7里是没有设置 SQL_MODE 的。

|  3 | Have you heard the new band Hotter Than… |

  

   
   ▶
实验5:过滤条件:必须包含”red”关键词,如果匹配到的行中还包含”blue”关键词则降低此条数据权重

   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

   总结:这个实验没有看到明显效果,但 ~ 过滤符确实是降低此权重符

3.重启MySQL;

| Table   | Column_name   | Packed | Null | Index_type | Comment |

七、实验部分

1.my.ini文件中找到 [mysqld]

+———+—————+——–+——+————+———+

五、与全文检索相关的系统变量:
     ft_min_word_len = 全文检索的最小许可字符(默认4,通过 SHOW
VARIABLES LIKE ‘ft_min_word_len’ 可查看),
     中文通常是两个字就是一个词,所以做中文的话需要修改这个值为2最好。

在给一个表添加字段的时候,忽然发现会报一个date类型的字段的默认值错误,郁闷~

+—-+——————————————–+

一、概述
     
MySQL全文检索是利用查询关键字和查询列内容之间的相关度进行检索,可以利用全文索引来提高匹配的速度。

+—-+

  

   ▶ 实验11:同时对 title_fc 和 content_fc 两字段做全文检索

   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

   图片 2

   总结:通过实验发现,又成功的取到了 content_fc
字段匹配的权重值,排序方式是首要按title字段权重降序排序,次要按
content_fc 权重降序排序。
           另外我发现 ok 这个关键词在对“title_fc
char(254)”字段匹配时得到匹配值为2.1xxxxxxx,但对“content_fc
text”字段匹配时权重值去为0,
          
这是MySQL对各英文单词权重值的给予有自己的算法,我们无权干涉。所以当我们发现有些单词的权重值为零甚至为负时,不用过于纠结,
           因为MySQL有自己的算法。

           关于排序,首要按 title_score 字段权重降序排序,次要按
content_score
权重降序排序,这样的排序规则看起来更科学了,但我想优秀的搜索引擎绝不至于简单如此吧,继续下面的实验:

|  3 |               1.3862514533815 |

插入结果如下图:
图片 3

您还可以使用这种方法来查找至少一个传递到AGAINST的参数中,以下的例子查找了至少包含单词“hell”和“rocks”中的一个的记录:

◆ 步骤5 搜索语法规则、排序 实验
   说明:匹配语句 MATCH (col1,col2,…) AGAINST (expr
[search_modifier]) 匹配完成后,会返回此条数据的权重值(权重值1 ≈
各个词的匹配结果权重值之和),我们利用此权重值“由高到低”排序可优化查询结果。

One of the hottest tracks currently playing…I’ve been listening to it all day’);

三、检索方式
     1、自然语言检索: IN NATURAL LANGUAGE MODE

想看到每个记录的评分如何,只需要返回MATCH()方法作为结果集的一部分,如下所示:

二、语法
      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索引)。

+—-+

         SELECT * FROM articles WHERE MATCH (title,content) AGAINST
(‘+apple ~banana’ IN BOOLEAN MODE);
         ~
是我们熟悉的异或运算符。返回记录必须包含apple,若也包含了banana会降低权重。
         但是它没有 +apple -banana
严格,因为后者如果包含banana压根就不返回。

+—-+

  

   ▶ 实验2:过滤条件:必须包含”red”关键词

   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

   图片 4

   总结:使用了过滤符号 +
,表示查询结果中,任一条数据都必须包含”red”这个词,不包含”red”这个词的行均被忽略。

3、运行全文搜索

  

   ▶
实验3:过滤条件:必须包含”red”关键词,如果匹配到的行中还含有”blue”关键词,则会对此条数据增加权重:

   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

   或下面写法:
   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

   图片 5

  
总结:与实验2比较,当包含了red的行中,若也包含blue关键词,权重确实增加了(如:id=4这条)。

| id | data                                       |

  

   ▶ 实验9:在实验1基础上,将blue的权重值忽视不要(注意与实验1比较)

   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

   图片 6

  
总结:在实验1的基础上,此时去除select字段条件里的blue关键词,但在where里去仍保留blue关键词。
            我的本意是想正常匹配“optimizing ok red
blue”这几个关键词,但不想得到blue的权重值(忽视blue的权重值)。
            查询的结果是含有blue关键词的数据的权重值会略降低了。
           
通过“降重”——忽略某些关键词权重值的方式可使部分数据权重值减小,进而影响排序。

VALUES (3, ‘Have you heard the new band Hotter Than Hell?

四、MySQL全文检索的条件限制
    
1、在MySQL5.6以下,只有MyISAM表支持全文检索。在MySQL5.6以上Innodb引擎表也提供支持全文检索。
     2、相应字段建立FULLTEXT索引

|  3 |

         SELECT * FROM articles WHERE MATCH (title,content) AGAINST
(‘+apple +(>banana <orange)’ IN BOOLEAN MODE);
         返回必须同时包含“apple banana”或者必须同时包含“apple
orange”的记录。
         若同时包含“apple banana”和“apple orange”的记录,则“apple
banana”的权重高于“apple orange”的权重。

MySQL针对这一问题提供了一种基于内建的全文查找方式的解决方案。在此,开发者只需要简单地标记出需要全文查找的字段,然后使用特殊的MySQL方法在那些字段运行搜索,这不仅仅提高了性能和效率(因为MySQL对这些字段做了索引来优化搜索),而且实现了更高质量的搜索,因为MySQL使用自然语言来智能地对结果评级,以去掉不相关的项目。

    3、查询扩展检索: WITH QUERY EXPANSION

| id | MATCH (data) AGAINST (‘rock’) |

  

    ▶ 实验15:中文全文检索
     
MySQL不支持中文全文检索,因为中文一句话是连着写的,不像英文单词间有空格分隔。解决办法就是中文分词(关于中文分词请参阅其它文章),
     
如果你的MySQL是安装在Windows平台上的,可以不用转码直接存储中文就可以使用全文索引,如本例。但是如果你的MySQL是安装在Linux上的则需要进行转编码(urlencode
/ base64_encode / json_encode / 区位 /
拼音)等方案,具体方案参看其它博文。

      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

图片 7

 

来源:

当您拥有了数据和索引,就可以使用MySQL的全文搜索了,最简单的全文搜索方式是带有MATCH…AGAINST语句的SELECT查询,以下是一个简单的例子,可以来查找含有单词“single”的记录:

  

   ▶ 实验4:过滤条件:必须包含”red”关键词,并且不能包含”blue”关键词

   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

   图片 8

   总结:可见 + –
这两个符号是表示“并且(and)”的意思,即:必须包含red关键词 and
不能包含blue关键词。

很多互联网应用程序都提供了全文搜索功能,用户可以使用一个词或者词语片断作为查询项目来定位匹配的记录。在后台,这些程序使用在一个SELECT查询中的LIKE语句来执行这种查询,尽管这种方法可行,但对于全文查找而言,这是一种效率极端低下的方法,尤其在处理大量数据的时候。

  

   ▶ 实验1:只对 title_fc 索引字段做全文检索,并显示每条数据的权重值

   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

   图片 9

   总结:1.当没有加 + –
这样的过滤符号时,这些关键词是“或(or)”的关系,即:要么匹配optimizing,要么匹配ok,要么匹配red,要么匹配blue。
           2.通过上面实验,发现当某条数据有多个关键词匹配时(如:red
blue),此条数据的权重值会略高:
              此条数据权重值 ≈ optimizing权重值 + ok权重值 + red权重值 +
blue权重值
             
理论上来说,当一条数据能匹配上的关键词越多,则此条数据的权重值越高,排名越靠前。

+—-+

         SELECT * FROM articles WHERE MATCH (title,content) AGAINST
(‘apple banana’ IN BOOLEAN MODE);
        
apple和banana之间是空格,空格表示OR。即:返回记录至少包含apple、banana中的一个。

+—-+

◆ 步骤4 插入测试数据
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’),
(‘中华’,’中华人民共和国 ‘),
(‘中华情 和谐’,’上海 和谐’),
(‘污染之都’,’你好 我是 北京 人’),
(‘北京精神’,’创新 爱国 包容 厚颜’)

| id |

  

   ▶ 实验13:另一个角度看排序
     看一个SQL语句原型,如果性别字段值为“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值高却排到了前面这不一定科学吧?)

   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

图片 10

   总结:如果 title_fc 和 content_fc
都匹配上了,做为优先排序理所当然,但也应考虑局部权重值过高的问题哦。

验证数据的正确录入:

         SELECT * FROM articles WHERE MATCH (title,content) AGAINST
(‘+apple banana’ IN BOOLEAN MODE);
        
返回记录必须包含apple,同时banana可包含也可不包含,若包含的话会获得更高的权重。

这篇文章将向您讲述在MySQL中如何进行全文搜索。

  

    ▶ 实验14:优化实验13,可支持更复杂的条件排序
       看一个SQL语句原型,CASE WHEN THEN END 结构:

          CASE <单值表达式>
              WHEN <表达式值> THEN <SQL语句或者返回值>
              WHEN <表达式值> THEN <SQL语句或者返回值>
               …
              WHEN <表达式值> THEN <SQL语句或者返回值>
              ELSE <SQL语句或者返回值>
          END

      
有一表查询:大于或等于80表示显示为“优秀”,大于或等于60显示为“及格”,小于60分显示为“不及格”。
       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

       # 实例测试一下
       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()
貌似不好实现,看下面语句:

       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

图片 11

      
总结:本实验的排序未必合乎科学,但引出一个更复杂规则的排序方式、角度,多种排序结合使用才能做出更合理的排序,才能使你的搜索引擎更加智能。抛砖引玉,或许你有更好的排序,请也分享给我。

接下来,定义您要作为全文搜索索引的字段

  

   ▶ 实验6:过滤条件:匹配包含单词“red”和“Linux” 的行,或包含“red”
和“blue”的行(无先后顺序)
                然而包含 “apple Linux”的行较包含“apple
blue”的行有更高的权重值。

   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

   图片 12

  
—————————————————————————————————————————— 

   ▶ 实验7:过滤条件:匹配关键词以 re 开头,或以 bl 开头的数据行

   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

   图片 13

   总结:注意 * 是写在后面哦,此时相当于 like 模糊匹配,没有权重值了

  
—————————————————————————————————————————— 

   ▶ 实验8:过滤条件:匹配查找字符串“To Use MySQL”关键词

   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

   图片 14

   总结:此时是把双引号内的的字符串看成一个关键词,若不用双引号则是将 To
Use MySQL 三个关键词去分别匹配,两者有区别;

1 row in set (0.00 sec)

# 修改全文检索的最小许可字符为2个字符或汉字
ft_min_word_len = 2

|  2 |

         SELECT * FROM articles WHERE MATCH (title,content) AGAINST
(‘+apple -banana’ IN BOOLEAN MODE);
         + 表示AND,即必须包含。-
表示NOT,即必须不包含。即:返回记录必需包含 apple,且不能包含 banner。

发表评论

电子邮件地址不会被公开。 必填项已用*标注