舟山专业做网站,帮企业做网站赚钱,广州建设网站服务,大连网站网站搭建制作一、概述MySQL全文检索是利用查询关键字和查询列内容之间的相关度进行检索#xff0c;可以利用全文索引来提高匹配的速度。二、语法MATCH (col1,col2,...) AGAINST (expr [search_modifier])search_modifier: { IN BOOLEAN MODE | WITH QUERY EXPANSION }例如#xff1a;SELE…一、概述MySQL全文检索是利用查询关键字和查询列内容之间的相关度进行检索可以利用全文索引来提高匹配的速度。二、语法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 MODE2、布尔检索 IN BOOLEAN MODE剔除一半匹配行以上都有的词譬如说每个行都有this这个字的话那用this去查时会找不到任何结果这在记录条数特别多时很有用原因是数据库认为把所有行都找出来是没有意义的这时this几乎被当作是stopword(中断词)但是若只有两行记录时是啥鬼也查不出来的因为每个字都出现50%(或以上)要避免这种状况请用IN BOOLEAN MODE。● IN BOOLEAN MODE的特色·不剔除50%以上符合的row。·不自动以相关性反向排序。·可以对没有FULLTEXT index的字段进行搜寻但会非常慢。·限制最长与最短的字符串。·套用Stopwords。● 搜索语法规则 一定要有(不含有该关键词的数据条均被忽略)。- 不可以有(排除指定关键词含有该关键词的均被忽略)。 提高该条匹配数据的权重值。~ 将其相关性由正转负表示拥有该字会降低相关性(但不像 - 将之排除)只是排在较后面权重值降低。* 万用字不像其他语法放在前面这个要接在字符串后面。 用双引号将一段句子包起来表示要完全相符不可拆字。SELECT * FROM articles WHERE MATCH (title,content) AGAINST (apple -banana IN BOOLEAN MODE); 表示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 返回必须同时包含“apple banana”或者必须同时包含“apple orange”的记录。若同时包含“apple banana”和“apple orange”的记录则“apple banana”的权重高于“apple orange”的权重。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 创建数据库(视情况可跳过此步)CREATE DATABASE search DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;◆ 步骤3 创建数据表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)) ENGINEMyISAM DEFAULT CHARSETutf8# 如果后期需要添全文加索引可以用如下语句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 插入测试数据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 索引字段做全文检索并显示每条数据的权重值SELECT *,MATCH (title_fc) AGAINST (optimizing ok red blue) as title_scoreFROM zzx_articlesWHERE 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关键词SELECT *,MATCH (title_fc) AGAINST (optimizing ok red) as title_scoreFROM zzx_articlesWHERE MATCH (title_fc) AGAINST (optimizing ok red IN BOOLEAN MODE) order by title_score DESC总结使用了过滤符号 表示查询结果中任一条数据都必须包含red这个词不包含red这个词的行均被忽略。------------------------------------------------------------------------------------------------------------------------------▶ 实验3过滤条件必须包含red关键词如果匹配到的行中还含有blue关键词则会对此条数据增加权重SELECT *,MATCH (title_fc) AGAINST (optimizing ok red blue) as title_scoreFROM zzx_articlesWHERE 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_scoreFROM zzx_articlesWHERE MATCH (title_fc) AGAINST (optimizing ok red blue IN BOOLEAN MODE) order by title_score DESC总结与实验2比较当包含了red的行中若也包含blue关键词权重确实增加了(如id4这条)。------------------------------------------------------------------------------------------------------------------------------▶ 实验4过滤条件必须包含red关键词并且不能包含blue关键词SELECT *,MATCH (title_fc) AGAINST (optimizing ok red blue) as title_scoreFROM zzx_articlesWHERE MATCH (title_fc) AGAINST (optimizing ok red -blue IN BOOLEAN MODE) order by title_score DESC总结可见 - 这两个符号是表示“并且(and)”的意思即必须包含red关键词 and 不能包含blue关键词。------------------------------------------------------------------------------------------------------------------------------▶ 实验5过滤条件必须包含red关键词如果匹配到的行中还包含blue关键词则降低此条数据权重SELECT *,MATCH (title_fc) AGAINST (optimizing ok red ~blue) as title_scoreFROM zzx_articlesWHERE 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”的行有更高的权重值。SELECT *,MATCH (title_fc) AGAINST (red (Linux FROM zzx_articlesWHERE MATCH (title_fc) AGAINST (red (Linux ------------------------------------------------------------------------------------------------------------------------------▶ 实验7过滤条件匹配关键词以 re 开头或以 bl 开头的数据行SELECT *,MATCH (title_fc) AGAINST (re* bl*) as title_scoreFROM zzx_articlesWHERE MATCH (title_fc) AGAINST (re* bl* IN BOOLEAN MODE) order by title_score DESC总结注意 * 是写在后面哦此时相当于 like 模糊匹配没有权重值了------------------------------------------------------------------------------------------------------------------------------▶ 实验8过滤条件匹配查找字符串“To Use MySQL”关键词SELECT *,MATCH (title_fc) AGAINST (To Use MySQL) as title_scoreFROM zzx_articlesWHERE 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_scoreFROM zzx_articlesWHERE 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_scoreFROM zzx_articlesWHERE 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_scoreFROM zzx_articlesWHERE 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”字段select 字段1,字段2,字段1 字段2 as 字段3from 表名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 score1FROM zzx_articlesWHERE 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”显示“男”否则显示“女”select *,IF(sex1,男,女) 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 score1FROM zzx_articlesWHERE 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 结构CASE WHEN THEN WHEN THEN ...WHEN THEN ELSE 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 id8 THEN 5 WHEN id8 THEN 4 ELSE 0 END) as newfieldfrom zzx_articleswhere id5我的新排序思路如果 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 score1FROM zzx_articlesWHERE 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 / 区位 / 拼音)等方案具体方案参看其它博文。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 score1FROM zzx_articlesWHERE MATCH (title_fc,content_fc) AGAINST (中华 北京 和谐 security IN BOOLEAN MODE) order by score1 DESC,title_score DESC,content_score DESC来源http://www.cnblogs.com/martinzhang/p/3220345.html
