Mysql自带的全文检索

sql自带的模糊查询对于检索数据来说，是肯定满足不了要求的，但是项目紧张，又没有那么多时间去研究一些全文检索框架，例如Lucene、Solr、ElasticSearch，咋办呢，Mysql有办法啊，其实，在Mysql数据库中，很早之前就已经支持了全文检索数据，只不过有个重点，外国佬怎么会支持中文尼，哈哈。

其实根本原因是因为英文检索是用空格来对分词进行分隔，而中文肯定不能用空格来分隔，只能通过语义进行分词，用空格的话是肯定检索不出某些词汇的。

良心的是，在Mysql 5.7版本时，MySQL内置了ngram全文检索插件，用来支持中文分词，但是仅对MyISAM和InnoDB引擎有效。

在使用中文全文索引之前，需要对索引最小字符长度进行设置，长度一般默认为4个字符，也就是说，查询的时候只对条件字符串aaaa起作用，对字符串aa不起作用，操作步骤就是在my.ini中的[mysqld]后面添加 ft_min_word_len = 1，将字符长度设置为1即可，此时需要重新启动mysql服务。
注意：如果已经在这之前有全文索引的话，需要重新建立索引，重建索引的语法为：

repair table t_tree_info quick;

创建全文索引

1.创建表的时候创建全文索引
语法一般为：

CREATE TABLE article ( 
       id INT AUTO_INCREMENT NOT NULL PRIMARY KEY, 
       title VARCHAR(200), 
       body TEXT, 
       FULLTEXT(title, body) 
 ) TYPE=MYISAM;

建立索引的时候，如果只有单独一个字段的索引，可能无法实现全文索引，需要和另外一个字段建立一个联合索引；另外表的类型尽量设置成MYISAM类型。
2.alter table 的方式添加表的索引
语法为：

ALTER TABLE 表名称 ADD FULLTEXT INDEX 索引名称  (字段名1，字段名2)

3.直接创建索引create index方式
语法为：

CREATE FULLTEXT INDEX 索引名称 ON 表名称(字段名1(20),字段名2（20）)

使用全文索引

使用全文索引的语法为：

 SELECT * FROM 表名称 WHERE MATCH(字段名) AGAINST('条件字符串1*')

在使用过程中如果需要满足多个检索字符，可以采用and连接多个MATCH进行查询。

注意：
使用过程中的50%的门坎限制，当查询结果很多，几乎所有记录都有，或者极少的数据，都有可能会返回非所期望的结果，但是可用IN BOOLEAN MODE即可以避开50%的限制，至于要使用的时候在后面加上即可，例如：

SELECT * FROM 表名称 WHERE MATCH(字段名1) AGAINST(‘条件字符串1’ IN BOOLEAN MODE)

实例：
如果我要查询物资表中的带有 塑料、20 两个字符的检索结果，我需要这样写,需要注意50%限制的问题，查询出来的数据更准确：

select t.* from material t where MATCH('material_name') AGAINST('塑料*' IN BOOLEAN MODE) and MATCH('material_name') AGAINST('20*' IN BOOLEAN MODE)

删除全文索引

语法为：

DROP INDEX 索引名称 ON 表名称 ;

全文索引变量值查询

全文索引有几个需要提前设置的变量值，可以用sql语句直接进行查询：

SHOW VARIABLES LIKE 'ft%'; 

执行后会出现几个变量值得具体数据，例如：

ft_boolean_syntax   + -><()~*:""&|  #改变IN BOOLEAN MODE的查询字符，不用重新启动MySQL也不用重建索引
ft_min_word_len    4                #最短的索引字符串，默认值为4，(通常改为1)修改后必须重建索引文件
ft_max_word_len    84               #最长的索引字符串，默认值为84，修改后必须重建索引文件
ft_query_expansion_limit   20       #查询括展时取最相关的几个值用作二次查询
ft_stopword_file    (built-in)      #全文索引的过滤词文件，具体可以参考：MySQL全文检索中不进行全文索引默认过滤词         

关于查询字符ft_boolean_syntax，一般常用的包含下面几个：
1.” ” :整体匹配，用双引号将一段字符串包裹，表示查询出来的字段要完全相符，不能分隔，例如”李明”，就是查询出带有 “李明” 这个字符串的相关字段结果，而不是返回包含”李小明”、”李大明”等的结果。

2.* :通配符，这个只能接在字符串后面。
MATCH (name) AGAINST (‘+小明‘) #错误，不能放前面
MATCH (name) AGAINST (‘+小明*’) #正确

3.~ :表示拥有该字会降低相关性，只是排在较后面。
+apple ~macintosh 先匹配apple，但如果同时包含macintosh，就排名会靠后面。

4.( )：通过括号来使用字条件
找到包含111和222和333，111和222，或者111和333,语法为：
+111 +(>222 <333)
5.> <：提高与降低相关性

> 提高相关性  <降低相关性
决定查询的结果排得比较靠前还是比较靠后的位置

6.+ : 放在词的前面，表示一定要包含该词，并且必须在字符的开始位置
+Apple 匹配：Apple123, “tommy, Apple”

7.- : 不包含该词，所以不能只用「-yoursql」查不到任何row的，必须搭配其他语法使用。
MATCH (name) AGAINST (‘-林11 +张筱雨’)
匹配到： 所有不包含林11，但包含张筱雨的记录

8.空格，表示可选的，包含该词的顺序较高
apple banana 找至少包含上面词中的一个的记录行
+apple +juice 两个词均在被包含
+apple macintosh 包含词 “apple”，但是如果同时包含 “macintosh”，它的排列更高一些
+apple -macintosh 包含 “apple” 但不包含 “macintosh”