MySQL-中文全文检索

总结：注意 * 是写在后面哦，此时相当于 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

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

? 实验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

　　总结：在实验1的基础上，此时去除select字段条件里的blue关键词，但在where里去仍保留blue关键词。我的本意是想正常匹配“optimizing ok red blue”这几个关键词，但不想得到blue的权重值(忽视blue的权重值)。

查询的结果是含有blue关键词的数据的权重值会略降低了。

通过“降重”——忽略某些关键词权重值的方式可使部分数据权重值减小，进而影响排序。

? 实验10：在实验9的基础上，在select字段条件里增加几个red关键词，where里的关键词保持不变(注意与实验1 实验9比较)。

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 两字段做全文检索

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

总结：通过实验发现，又成功的取到了 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”字段：

select 字段1,字段2,字段1 + 字段2 as 字段3
       from 表名
       where .....

　　下面将 title_fc 和 content_fc 两权重值求和，放入新字段 score1 中，并按 score1 首要排序，title_score 次之，content_score再次之：

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_scort

　　总结：相对而言，如果 title_fc 和 content_fc 都匹配上了，应给予靠前的排名吧。所以首要按其 title_fc 和 content_fc 两权重值之和排名，次要再考虑 title_fc、content_fc 排序。

? 实验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

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

? 实验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

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

? 实验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

　所有文章都只做学习记录,不做任何商业用途已经技术分享.

MySQL-中文全文检索

标签：